Operation is not allowed when the object is closed

[pabs1111]

Hi All

I'm trying to insert records using VBA in Access into a mysql database and I'm getting the above error. The code I'm using I have found on the web, and in all frankness don't understand it as I'm only a beginner. However the code does work if I'm using a select statement to retrieve records, but it does not work when I'm trying to do an insert statement.

Any pointers/adivce would be really appreciated! The error is thrown on statement. rs.Close

Thanks Paul

Code is

Private Sub InsertTrainers_Click()
On Error GoTo Err_InsertTrainers_Click
    Dim strDatabaseName As String
    Dim strDBCursorType As String
    Dim strDBLockType As String
    Dim strDBOptions As String

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim b As Long
    Dim strSQL As String
    Dim strMsg, Response As String
    
    strMsg = "Do you want to add this event to all the trainers schedule?"
    
    'Get confirmation of the insert into the trainers schedule.
    Response = MsgBox(strMsg, vbOKCancel)
     
    If Response = vbOK Then
        strDBCursorType = adOpenDynamic  'CursorType
        strDBLockType = adLockOptimistic   'LockType
        strDBOptions = adCmdText         'Options

        Set cn = New ADODB.Connection
        cn.Open ConnectString()
        With cn
            .CommandTimeout = 0
            .CursorLocation = adUseClient
        End With

        Set rs = New ADODB.Recordset       'Creates record set
        strSQL = "Insert Into TrainerSchedule (TrainerID,EventID,Roleid,Statusid,TrainerSOWid,TrainerTravelid, TrainerAccomid) " _
                & "Select TrainerID, EventID, 71, 21,76,80,84 From FindTrainerforEvent Where Eventid = " & [EventID]
    
        rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
        
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
                
    End If
 
Exit_InsertTrainers_Click:
    Exit Sub

Err_InsertTrainers_Click:
    MsgBox Err.Description
    Resume Exit_InsertTrainers_Click
    
End Sub

Private Function ConnectString() As String
Dim strServerName As String
Dim strDatabaseName As String
Dim strUserName As String
Dim strPassword As String

    'Change to IP Address if not on local machine
    'Make sure that you give permission to log into the
    'server from this address
    'See Adding New User Accounts to MySQL
    'Make sure that you d/l and install the MySQL Connector/ODBC 3.51 Driver

strServerName = "localhost"
strDatabaseName = "EventPlanner"
strUserName = "XXXXXX"
strPassword = "XXXXXXX"

ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                "SERVER=" & strServerName & _
                ";DATABASE=" & strDatabaseName & ";" & _
                "USER=" & strUserName & _
                ";PASSWORD=" & strPassword & _
                ";OPTION=3;"

End Function

[Krandor]

If it works when you do a select and not when you do an insert, then it may be a permissions issue.

Check the permissions for the login you are using and make sure that it has insert rights to the table.

[pabs1111]

If it works when you do a select and not when you do an insert, then it may be a permissions issue.

Check the permissions for the login you are using and make sure that it has insert rights to the table.

Unfortunately this is not the case - I have Select, Insert and Update privileges granted

[FishVal]

Try to use ADODB.Connectio n.Execute method instead of ADODB.Recordset .Open.

Regards,
Fish

[pabs1111]

Try to use ADODB.Connectio n.Execute method instead of ADODB.Recordset .Open.

Regards,
Fish

Works Perfectly! Thanks Fish

Paul

[ADezii]

If it works when you do a select and not when you do an insert, then it may be a permissions issue.

Check the permissions for the login you are using and make sure that it has insert rights to the table.

1. To expand on FishVal's excellent point:
   

   It is not a good idea to use the Source argument of the Open method to perform an action query that doesn’t return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. Call the Execute method of a Command object or the Execute method of a Connection object instead to perform a query that, such as a SQL INSERT statement, that doesnt return records.

2. To be perfectly honest, I feel as though a code revision is in order, since many Variable Declarations are incorrect (Cursor Type, Lock type, and the Options Parameter are not Strings), and the Return Value from the Msgbox Function is also not a String. Code syntax can also be improved on, as well as the overall application logic. I posted just a couple of suggestions for starters:
   

   Dim rs As ADODB.Recordset
   Dim strSQL As String
   
   Dim intResponse As Integer
   
   strMsg = "Do you want to add this event to all the trainers schedule?"
   
   'Get confirmation of the insert into the trainers schedule.
   intResponse = MsgBox(strMsg, vbOKCancel)
   
   strSQL = "Blah, Blah, Blah..."
   
   Set rs = New ADODB.Recordset
   
   With rs
     .Source = strSQL
     .ActiveConnection = cn    'previously defined
     .CursorType = adOpenDynamic
     .LockType = adLockOptimistic
       .Open
   End With

[NeoPa]

Paul,
Please remember when posting code that you are expected to use the [ CODE ] tags provided.

This makes the code much easier to read and saves others the effort of adding them for you later.

NeoPa (Admin).

PS. I do understand you're new to the site.

[pabs1111]

1. To expand on FishVal's excellent point:
   

   It is not a good idea to use the Source argument of the Open method to perform an action query that doesn’t return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. Call the Execute method of a Command object or the Execute method of a Connection object instead to perform a query that, such as a SQL INSERT statement, that doesnt return records.

2. To be perfectly honest, I feel as though a code revision is in order, since many Variable Declarations are incorrect (Cursor Type, Lock type, and the Options Parameter are not Strings), and the Return Value from the Msgbox Function is also not a String. Code syntax can also be improved on, as well as the overall application logic. I posted just a couple of suggestions for starters:
   

   Dim rs As ADODB.Recordset
   Dim strSQL As String
   
   Dim intResponse As Integer
   
   strMsg = "Do you want to add this event to all the trainers schedule?"
   
   'Get confirmation of the insert into the trainers schedule.
   intResponse = MsgBox(strMsg, vbOKCancel)
   
   strSQL = "Blah, Blah, Blah..."
   
   Set rs = New ADODB.Recordset
   
   With rs
     .Source = strSQL
     .ActiveConnection = cn    'previously defined
     .CursorType = adOpenDynamic
     .LockType = adLockOptimistic
       .Open
   End With

Thanks for the advice ADezii - will update my code.

Cheers

Paul

[pabs1111]

Paul,
Please remember when posting code that you are expected to use the [ CODE ] tags provided.

This makes the code much easier to read and saves others the effort of adding them for you later.

NeoPa (Admin).

PS. I do understand you're new to the site.

Doh! Sorry stupid Newbie mistake.

Paul

[NeoPa]

No biggie :)

You don't come across as a newbie regardless of your current post-count. I doubt we'll be seeing that again from you Paul.