Connect, update and close connection to MySQL from VBA

Question

I am trying to connect to MySQL using VBA, in order to write the results produced in Excel. I need to write thousands of rows each containing around 100 columns. So I have created separate subroutines to connect to MySQL before I start writing the results, then for each row, I need to run an 'insert' query and in the end, I need to close the connection.

I have created the following subs which I am calling from my main program:

sub main
    dbconnect
    for i = 1 to 50000
         dbupdate
    next i
    dbclose

sub dbconnect()
    Dim cn as ADODB.connection
    Set cn = New ADODB.Connection
    cn.open "Driver .............." (usual stuff)

sub dbupdate()
    dim sqlstr as string
    sqlstr = "insert into table " .... (some query to write the results in a table)
    cn.execute sqlstr

sub dbclose()
    cn.close
    cn = nothing

However, my macro doesn't seem to understand cn when it appears in the dbupdate or dbclose subroutines. It seems that the connection I had set up in dbconnect is not accessible to other subroutines. The macro stops at cn.execute sqlstr in dbupdate with the following error: runtime error 91: object variable or with block variable note set.

Is there a way I can make this work?

Many thanks!

Rik Sportel · Accepted Answer · 2017-07-04 14:03:12Z

1

You're declaring your connection object within the dbconnect() sub. That means that connection will cease to exist after that sub ends executing. To fix this, make it a publicly declared variable outside of any sub:

Public cn as ADODB.connection 'Declare it here!!!

Sub main()
    dbconnect
    for i = 1 to 50000
         dbupdate
    next i
    dbclose
    '....
End Sub
Sub dbconnect()
    Set cn = New ADODB.Connection
    cn.open "Driver .............." (usual stuff)
    '....
End Sub
Sub dbupdate()
    dim sqlstr as string
    sqlstr = "insert into table " .... (some query to write the results in a table)
    cn.execute sqlstr
End Sub
Sub dbclose()
    cn.close
    Set cn = nothing 'Use set keyword!
End Sub

answered Jul 4, 2017 at 14:03

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KMLN Over a year ago

Thanks. I declared the connection as public variable before dbconnect as you suggested, but still getting the same error. The macro stops at cn.execute sqlstr in dbupdate with the following error: runtime error 91: object variable or with block variable note set.

Rik Sportel Over a year ago

Test if your cn.open is successful first, if not exit with an error. It's likely the cn.open is the actual problem.

Collectives™ on Stack Overflow

Connect, update and close connection to MySQL from VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related