Trying to extract data from MySQL to Excel using VBA

334
February 17, 2017, at 02:44 AM

I am getting an error and I don't understand what is the problem. Here is my code

Sub test()
Dim rs As ADODB.Recordset
Dim sqlstr As String ' SQL to perform various actions
Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
        "SERVER=ETS-DEV-01;" & _
        "DATABASE=reporting;" & _
        "USER=guest_user;" & _
        "PASSWORD=0X4ZT9kwsY%yGOp;" & _
        "Option=3"
    sqlstr = "select * from tveuptimes"
    rs.Open sqlstr, oConn
End Sub

I am getting the error: Run-time error '91' Object variable or with block variable not set.

I don't understand what i am doing wrong.

Answer 1

The line Set oConn = New ADODB.Connection isn't necessary. Instead, when you dimension rs and oConn add the word New before the type, like this:

Sub test()
Dim rs As New ADODB.Recordset
Dim sqlstr As String
Dim oConn As New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
        "SERVER=ETS-DEV-01;" & _
        "DATABASE=reporting;" & _
        "USER=guest_user;" & _
        "PASSWORD=0X4ZT9kwsY%yGOp;" & _
        "Option=3"
    sqlstr = "select * from tveuptimes"
    rs.Open sqlstr, oConn
End Sub

Here's a good tutorial: http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/

READ ALSO
Need MYSQL help for senior high school

Need MYSQL help for senior high school

Can you help me with this problem?

327
ubuntu mysql not starting

ubuntu mysql not starting

I am using LAMPP on ubuntu 1604

428
How to update an SQL table based on the previous data in the same table

How to update an SQL table based on the previous data in the same table

I have a table that measure the student performance student in my DB as below:

300
Displaying all the data in MYSQL

Displaying all the data in MYSQL

I have a query that should display all the data that matches the where clause but unluckily it only displays 1 dataHere is my query

302