CODEWITHSUNDEEP
mysqlexcelvbaadodb
M. R.
M. R.

Reputation: 11

VBA Connection Issue with MySQL - Connected at Top of Macro, but not bottom

I have a macro that opens an ADODB connection to a MySQL library. There are two queries that are generated in the macro. The first, which works, is a select statement to check for duplicates. The second, which is not working, is to insert records onto the same table the select statement referenced. I am receiving no errors from VBA, and when I copy/paste directly into MySQL the query works fine.

At the top of my macro I set up the connection as follows:

   TimesheetConn = "DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=*server number*;PORT=*port number*;database=my_db;UID=User;PWD=password;Option=2"

   'Connection Info
   Dim cnn As ADODB.Connection
   On Error GoTo AdoError
       Set cnn = New ADODB.Connection
      With cnn
    .ConnectionString = TimesheetConn
    .Open
    .CommandTimeout = 0
      End With
   Set FIT_Data = New ADODB.Recordset
   Set Task_Data = New ADODB.Recordset

I then develop the Select query (fitidquery) and run it as:

   FIT_Data.Open fitidquery, cnn, CursorType = 2

I do not close the connection, but then move on to generate my next query, an insert query (addtasks3), by looping through rows and assigning variables.

Then I try to call the connection again with:

   Task_Data.Open addtasks3, cnn, adOpenForwardOnly, adLockReadOnly
   cnn.Close

And it doesn't work, nor does it give me any errors either for VBA or SQL. As said before, copying the result of debug.print(addtasks3) into MySQL the query runs correctly and inserts the records.

I've tried opening a 2nd connection with the same parameters. That didn't work as well. I moved the On Error language down to in front of the second query call and it moves on to the AdoError message which seems to indicate that the connection is lost there.

I appreciate any help!

Upvotes: 0

Views: 293

Answers (2)

FunThomas
FunThomas

Reputation: 29286

If you issue an Insert-statement, there is no need to involve a recordset. You can simply execute 

 cnn.Execute addtasks3

Or, if you want to get the number of rows you inserted, use a ADODB.Command:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = addtasks3
Dim rowsInserted As Long
Call cmd.Execute(rowsInserted)
Debug.Print rowsInserted & " rows inserted."

Upvotes: 1

M. R.
M. R.

Reputation: 11

So I have to say thank you to all of you that mentioned On Errors. Your descriptions after rereading finally made it click with me what I did. I had thought that On Error Resume Next only worked for the line it was in front of in the code (I am still very much a VBA newbie learning as I go) and didn't realize that it would follow through the rest of the code. I had one before my first query because if its a new row it wouldn't return anything to check a duplicate against and cause an error. Hence why I wasn't getting error messages.

I added "On Error GoTo 0" after that section and now I'm getting error messages again. Turns out my user is denied use of the Insert Command for that table (even though I modeled it after a table where it does have use), so it looks like I just need to figure that out. Thank you everyone!

Upvotes: 0

Related Questions