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
Connection.Open "DSN=sgdv"
got highlighted as yellow. When I pressed the F8 at this line after being highlighted the error code shows up.