Excel – VBA Automation error when querying database through ODBC

microsoft excelodbcvba

I am trying to run Excel macro to query some data from my ODBC database.
My database details are as below:

DSN: sgdv UID:l8qc1 Password:l8qc1 server:sgdv

I am trying to query the data from the database but the VBA shows the error as below for the line Connection.Open "DSN=sgdv".

Run-time error -2147217843 (80040e4d) Automation error

I can't identify the error. Please help. Below is my code

Sub ReadDB()
Dim mainWorkBook As Workbook
Dim intRowCounter
Set mainWorkBook = ActiveWorkbook
intRowCounter = 2
mainWorkBook.Sheets("Sheet2").Range("A2:Z100").Clear
Set Connection = CreateObject("ADODB.Connection")
Connection.Open "DSN=sgdv"
strQuery = "SELECT * FROM DEDICT01 where DEDICT01.SER_SN='Z1E80R4C'"
Set resultSet = Connection.Execute(strQuery)
Do While Not resultSet.EOF
    mainWorkBook.Sheets("Sheet2").Range("A" & intRowCounter).Value = resultSet.Fields("DEDICT01.CUST_PARTS_NO").Value
  resultSet.movenext
Loop
resultSet.Close
End Sub

I have modified the program and my new coding is

Sub ReadDB()
Dim mainWorkBook As Workbook
Dim intRowCounter
Set mainWorkBook = ActiveWorkbook
intRowCounter = 2
mainWorkBook.Sheets("Sheet2").Range("A2:Z100").Clear
Set Connection = CreateObject("ADODB.Connection")
Connection.ConnectionString = "DSN=sgdv;UID=l8qc1;PWD=l8qc1;"
Connection.Open
strQuery = "SELECT * FROM DEDICT01 where DEDICT01.SER_SN='Z1E80R4C'"
Set resultSet = Connection.Execute(strQuery)
Do While Not resultSet.EOF
    mainWorkBook.Sheets("Sheet2").Range("A" & intRowCounter).Value = resultSet.Fields("DEDICT01.CUST_PARTS_NO").Value
  resultSet.movenext
Loop
resultSet.Close
End Sub

My Previous error is gone but now I am getting new error at the line

mainWorkBook.Sheets("Sheet2").Range("A" & intRowCounter).Value = resultSet.Fields("DEDICT01.CUST_PARTS_NO").Value

The error is

Run-Time error '3265':

Application-defined or Object-defined error

Best Answer

The problem is your connection string. Note that you mentioned UID, password, etc. information in your question, but this info appears nowhere in your code. To connect to the database, you must supply this information in the connection string.

Try the following:

Connection.ConnectionString = "DSN=sgdv;UID=l8qc1;PWD=l8qc1;"
Connection.Open 

I can't guarantee this will work, but it should at least get you on the right track. If it doesn't work, you may need to tweak the connection string. For more information about ADODB connections and connection strings, see this MSDN page:

https://msdn.microsoft.com/en-us/library/ms807027.aspx

Related Question