General ODBC Error in VBA

Question

Hi am populating the data from MS Access By Using VBA i am using below mentioned code.if i am run the same code in MS 2007 then It run properly but if i am run the same code in MS 2003 it gives the "General ODBC Error" how to solve this problem Any help would be appreciated!!

Thanks in advance

Sub Button2_Click()
Dim varConnection As String
Dim varSQL As String
Dim cal, cal1, x

 varConnection = "ODBC; DSN=MS Access Database;DBQ=D:\Box\Generate.mdb;Driver={Driver do Microsoft Access (*.mdb)}"
'
       varSQL = "SELECT * FROM Empdata"

       With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=ActiveSheet.Range("C7"))

               .CommandText = varSQL

               .Name = "Query-39008"

               .Refresh BackgroundQuery = False
       End With

End Sub

Answer 1

It worked for me in 2003, but of course I had to change the connection string. I can't believe the connection string would care what version you're using, but here's something you can do. Create the query table through the UI (Data - Get External Data - New Database Quer), then go to the Immediate Window (Alt+F11, Control+G) and type

?ActiveCell.QueryTables.Connection

and see what you get. If it doesn't match the connection string you have in your code, that may be a clue as to why it's working in one and not the other.

Answer 2

Now My Connection String is this

 varConnection = "ODBC;DSN=MS Access Database;DBQ=D:\06june\tab.mdb;DefaultDir=D:\06june ;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

Still i got the error Application defined or object defined error if i run this code in 2007 its working properly but not working in 2003 format

Answer 3

Hi now my code is perfectly run in the 2003 format also:Just I want Share my idea with you, Create the New Data Source Connection By using the following Steps

1 ) Choose Start - >Control Panel ->Administrative Tools

2 ) Select Data Source (ODBC) icon

3 ) In The Data Source Administrator Dialog Box Click Add Button

4 ) Select the Driver Click Finish Button

5 ) Give the DNS Name (user Define) . Select the Database By Click the Select Button

I am recording the macro through (Tools -> Macros - Record macros) in my MS 2003 excel Sheet Before Import the data from the DB,

Steps to be followed to import the data from DB.

1 ) Select the Cell Where you Want to paste the data.

2 ) Choose from the Excel Menu : Data - > import External Data - > import Data

3 ) In the Select Data Source Dialog Box Select the New sources button

4 ) Select ODBC DNS ,click next

5 ) Select Data Source Table click next

6 ) Select the Table from the List Click Finish Button

7 ) In the Select Data Source Dialog Box Select the DNS Source Name then click ok Button

8 ) Now Stop The Macros , Go To the View Code , You get the full code for data import connection, alter the path depends upon your program