sql update / insert using ado

[jesse]

Hi,

I've used the following code I found in a book to run sql select queries. This works fine. However, I'd aslo like to do update and insert statements, which this code won't do. I've tried adjusting it in a number of ways, but, so far, unsuccesfully. If anyone could tell me how to adjust these two functions so I could do update and insert queries I would be very much obliged.

tnx!

(I'm using access 2010)

Sub OpenConnection()

If Not connected Then
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open CurrentProject.Connection
cnn.Close
Set cnn = Nothing
connected = True
End If

End Sub


Function ExecCommand(dbSQLstring As String) As ADODB.Recordset

Call OpenConnection

Dim rec_set As ADODB.Recordset
Set rec_set = New ADODB.Recordset
Dim cmnd As ADODB.Command
Set cmnd = New ADODB.Command
cmnd.ActiveConnection = CurrentProject.Connection
cmnd.CommandText = dbSQLstring
Set rec_set = cmnd.Execute

Set ExecCommand = rec_set

End Function

 

[pbaldy]

I'm not a strong ADO user, but that should execute whatever SQL is fed to it, even insert/update SQL. How are you calling it?

 

[SimonB1978]

Hi,

I THINK the problem is that an update/insert query doesn't return any dataset, so you cannot assign this to your recordset.

You could replace this:
Set rec_set = cmnd.Execute

Set ExecCommand = rec_set

With something like this:
If Left(dbSQLstring, 6) = "INSERT" Or Left(dbSQLstring, 6) = "UPDATE" Then
cmnd.Execute
ExecCommand = Null
Else
Set rec_set = cmnd.Execute
Set ExecCommand = rec_set
End If


Simon B.

 

[boblarson]

This may have no bearing on the question at hand, but this part makes no sense whatsoever:

If Not connected Then
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open CurrentProject.Connection
cnn.Close
Set cnn = Nothing
connected = True
End If


You are connecting and then disconnecting before doing anything. What is the purpose of that? And then, in your other procedure, you go and open another connection anyway.

 

[pbaldy]

Good point Bob, I didn't even notice that. I use two public functions, one to open the connection and the other to close it after processing is complete.

 

[LPurvis]

>> makes no sense whatsoever
I'd imagine that it's a form of connection verification.
Testing whether the current connection is capable of opening a new connection. If not an error will be raised which, presumably, prevents the ExecCommand from completing.
Opening a connection to test the validity of a connection isn't a particularly comforting thought. Not least of which is that the exposed ConnectionString of an established object isn't necessarily that which you would supply to an object with which to successfully connect.

>> so you cannot assign this to your recordset
ADO does let you get away with DML statements in a command opening a recordset.
It's not something I like (at all) but it won't throw an error as DAO recordsets would, unless you then attempt to use the opened recordset.

Cheers.

 

[jesse]

Hi,

I'm sorry. i asked the wrong question. The code I use, allthough not very elegant even to me as an inexperienced acces user, works fine for updates and inserts.

What went wrong is that I had assumed the queries I use in MySql would also work in access, which apparently isn't the case. Sorry, my bad. Thanks for all your advices.