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:
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