2

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

4
  • 1
    What line is generating the error?
    – Excellll
    Oct 19, 2015 at 16:40
  • I don't know how to see which line gives error...
    – Dragonborn
    Oct 20, 2015 at 1:43
  • In the VBA Editor window, place the cursor somewhere in the code for this sub and press F8. This will step into your code one line at a time. Just press F8 until you get an error. Note the line that you were on when the error message popped up.
    – Excellll
    Oct 20, 2015 at 13:04
  • I did as you said @Excellll. In VBA Editor I kept pressing F8. The Line Connection.Open "DSN=sgdv" got highlighted as yellow. When I pressed the F8 at this line after being highlighted the error code shows up.
    – Dragonborn
    Oct 22, 2015 at 2:52

2 Answers 2

1

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

-2

The answer depends on what you mean with mainworkbook

  • if it is another workbook you need to give complete path like c:......
  • if it is current workbook there is no need to select the datatype. The current workbook itself is the active workbook
2
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
    – Ramhound
    Oct 19, 2015 at 17:10
  • As you can see from the other comments, your answer is not easy to understand. I'll edit it to include some punctuation and make it clearer what I think you want to say.
    – teylyn
    Oct 19, 2015 at 22:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .