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
9 13433
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.
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
Try to use ADODB.Connection.Execute method instead of ADODB.Recordset.Open.
Regards,
Fish
Try to use ADODB.Connection.Execute method instead of ADODB.Recordset.Open.
Regards,
Fish
Works Perfectly! Thanks Fish
Paul
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.
- 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.
- 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 32,556
Expert Mod 16PB
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.
- 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.
- 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
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 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Matthew Louden |
last post by:
The following ASP code yields the following error, but actually the new
record is stored
in database. The same error happens when the application deletes a record,
such as
sqlStmt ="delete from...
|
by: cliverama |
last post by:
help! fried brains....
asp calling a sqlserver7 stored proc which dynamically builds a
sqlstatement & passes it to sp_executesql
asp page gives the operation not allowed when object is closed...
|
by: |
last post by:
So, I am getting this really annoying error.. the SQL Statement executes
perfectly in Query Analyzer.. all I am doing is basically duplicating a row
(insert into select from) from one table back...
|
by: Arpan |
last post by:
An ASP application updates a SQL Server database table after a Form is
submitted after which the user is directed to another page that should
display the updated records. If the code is
...
|
by: amitbadgi |
last post by:
Hello guys I am getting this error while converting an asp applicayion
to asp.net
Exception Details: System.Runtime.InteropServices.COMException:
Operation is not allowed when the object is...
|
by: Matt |
last post by:
Got this problem that killing me...what am i doing wrong with this old
dll. When i call the function from asp i get "Operation is not allowed
when object is closed"
any clue will be...
|
by: jigu |
last post by:
<html>
<head><title>testing connection asp to mdb</title></head>
<body>
<%
dim sql_insert,sSQL
src = "C:\websites1\yourdominname\mydatabase.mdb"
sConnStr =...
|
by: Sonasang |
last post by:
Hi ,
I am creating a web page with ASP and Javascript.We have shared the foldres containg the code and all our team members are accessing the code.
There is no problem for me when i run the...
|
by: govind161986 |
last post by:
Dear All,
I have a stored procedure in which I have used a temporary table which selects the value from two tables and display the result. When I run the stored procedure from Sql's query analyzer...
|
by: sailoosha |
last post by:
Hi,
I am getting an error "operation not allowed when object is closed" when executing the following code. I am getting the runtime error at
While Recordset.EOF <> True
I am not sure where the...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |