By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,604 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,604 IT Pros & Developers. It's quick & easy.

Operation is not allowed when the object is closed

P: 13
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub InsertTrainers_Click()
  2. On Error GoTo Err_InsertTrainers_Click
  3.     Dim strDatabaseName As String
  4.     Dim strDBCursorType As String
  5.     Dim strDBLockType As String
  6.     Dim strDBOptions As String
  7.  
  8.     Dim rs As ADODB.Recordset
  9.     Dim cn As ADODB.Connection
  10.     Dim b As Long
  11.     Dim strSQL As String
  12.     Dim strMsg, Response As String
  13.  
  14.     strMsg = "Do you want to add this event to all the trainers schedule?"
  15.  
  16.     'Get confirmation of the insert into the trainers schedule.
  17.     Response = MsgBox(strMsg, vbOKCancel)
  18.  
  19.     If Response = vbOK Then
  20.         strDBCursorType = adOpenDynamic  'CursorType
  21.         strDBLockType = adLockOptimistic   'LockType
  22.         strDBOptions = adCmdText         'Options
  23.  
  24.         Set cn = New ADODB.Connection
  25.         cn.Open ConnectString()
  26.         With cn
  27.             .CommandTimeout = 0
  28.             .CursorLocation = adUseClient
  29.         End With
  30.  
  31.         Set rs = New ADODB.Recordset       'Creates record set
  32.         strSQL = "Insert Into TrainerSchedule (TrainerID,EventID,Roleid,Statusid,TrainerSOWid,TrainerTravelid, TrainerAccomid) " _
  33.                 & "Select TrainerID, EventID, 71, 21,76,80,84 From FindTrainerforEvent Where Eventid = " & [EventID]
  34.  
  35.         rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
  36.  
  37.         rs.Close
  38.         Set rs = Nothing
  39.         cn.Close
  40.         Set cn = Nothing
  41.  
  42.     End If
  43.  
  44. Exit_InsertTrainers_Click:
  45.     Exit Sub
  46.  
  47. Err_InsertTrainers_Click:
  48.     MsgBox Err.Description
  49.     Resume Exit_InsertTrainers_Click
  50.  
  51. End Sub
  52.  
  53. Private Function ConnectString() As String
  54. Dim strServerName As String
  55. Dim strDatabaseName As String
  56. Dim strUserName As String
  57. Dim strPassword As String
  58.  
  59.     'Change to IP Address if not on local machine
  60.     'Make sure that you give permission to log into the
  61.     'server from this address
  62.     'See Adding New User Accounts to MySQL
  63.     'Make sure that you d/l and install the MySQL Connector/ODBC 3.51 Driver
  64.  
  65. strServerName = "localhost"
  66. strDatabaseName = "EventPlanner"
  67. strUserName = "XXXXXX"
  68. strPassword = "XXXXXXX"
  69.  
  70. ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
  71.                 "SERVER=" & strServerName & _
  72.                 ";DATABASE=" & strDatabaseName & ";" & _
  73.                 "USER=" & strUserName & _
  74.                 ";PASSWORD=" & strPassword & _
  75.                 ";OPTION=3;"
  76.  
  77. End Function
Sep 11 '08 #1
Share this Question
Share on Google+
9 Replies


P: 50
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.
Sep 11 '08 #2

P: 13
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
Sep 11 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Try to use ADODB.Connection.Execute method instead of ADODB.Recordset.Open.

Regards,
Fish
Sep 11 '08 #4

P: 13
Try to use ADODB.Connection.Execute method instead of ADODB.Recordset.Open.

Regards,
Fish
Works Perfectly! Thanks Fish

Paul
Sep 11 '08 #5

ADezii
Expert 5K+
P: 8,627
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:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. Dim rs As ADODB.Recordset
    2. Dim strSQL As String
    3.  
    4. Dim intResponse As Integer
    5.  
    6. strMsg = "Do you want to add this event to all the trainers schedule?"
    7.  
    8. 'Get confirmation of the insert into the trainers schedule.
    9. intResponse = MsgBox(strMsg, vbOKCancel)
    10.  
    11. strSQL = "Blah, Blah, Blah..."
    12.  
    13. Set rs = New ADODB.Recordset
    14.  
    15. With rs
    16.   .Source = strSQL
    17.   .ActiveConnection = cn    'previously defined
    18.   .CursorType = adOpenDynamic
    19.   .LockType = adLockOptimistic
    20.     .Open
    21. End With
Sep 11 '08 #6

NeoPa
Expert Mod 15k+
P: 31,419
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.
Sep 11 '08 #7

P: 13
  1. To expand on FishVal's excellent point:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. Dim rs As ADODB.Recordset
    2. Dim strSQL As String
    3.  
    4. Dim intResponse As Integer
    5.  
    6. strMsg = "Do you want to add this event to all the trainers schedule?"
    7.  
    8. 'Get confirmation of the insert into the trainers schedule.
    9. intResponse = MsgBox(strMsg, vbOKCancel)
    10.  
    11. strSQL = "Blah, Blah, Blah..."
    12.  
    13. Set rs = New ADODB.Recordset
    14.  
    15. With rs
    16.   .Source = strSQL
    17.   .ActiveConnection = cn    'previously defined
    18.   .CursorType = adOpenDynamic
    19.   .LockType = adLockOptimistic
    20.     .Open
    21. End With

Thanks for the advice ADezii - will update my code.

Cheers

Paul
Sep 12 '08 #8

P: 13
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
Sep 12 '08 #9

NeoPa
Expert Mod 15k+
P: 31,419
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.
Sep 12 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.