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 13455
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
FishVal 2,653
Recognized Expert Specialist
Try to use ADODB.Connectio n.Execute method instead of ADODB.Recordset .Open.
Regards,
Fish
Try to use ADODB.Connectio n.Execute method instead of ADODB.Recordset .Open.
Regards,
Fish
Works Perfectly! Thanks Fish
Paul
ADezii 8,834
Recognized Expert Expert
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,573
Recognized Expert Moderator MVP
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,573
Recognized Expert Moderator MVP
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 test where username='2323'" Any ideas? Thanks!
<%
Dim objRS, sqlStmt
Set objRS = Server.CreateObject ("ADODB.Recordset")
sqlStmt = "insert into test VALUES ('2341', '2341');"
|
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 error
this is the asp code:
Set connInc= server.CreateObject("ADODB.Connection")
connInc.Open "DSN=db_database;User ID=userid;Password=xxxxxx"
Set rsInc= server.CreateObject("ADODB.Recordset")
|
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 into itself. This is so that
people can duplicate a report definition and then edit.. Basically i store
the XMLDATA property of the OWC in a SQL table and have a couple of pages
for finding report defs.. not too complex..
Error Type:...
|
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
----------------------------------------
'records get updated here
Response.Redirect("Records.asp")
objRS.Close
Set objRS=Nothing
|
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 closed.
Source Error:
Line 168: rs =
Server.CreateObject("ADODB.recordset")Line 169:
| |
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 appriciated....
test .asp
<%
dim oObj
dim a
set oObj= server.CreateObject("MyDll.MyClass")
|
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 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
' Uncomment the next two lines and comment the above line to switch between Access and SQL.
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
|
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 code and working properly. But when some of my team members when they run the web page they are getting the ERROR in function() Operation is not allowed when the object is closed.
In the login page there in no problem, After we login in the page...
|
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 window the stored procedure runs fine and also retrieves the record but when I execute the same procedure from aspx page it retrives an error message "Operation is not allowed, when the object is closed" it doesn't retrives the records.
Thanks.
...
|
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 problem is.
Plz let me know if you have idea on it.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |