CODEWITHSUNDEEP
mysqlexcelodbcadodbvba
KMLN
KMLN

Reputation: 79

Connect, update and close connection to MySQL from VBA

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!

Upvotes: 0

Views: 1373

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

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

Upvotes: 1

Related Questions