unable to connect to sql via ms access

hi expert's (using access 2003)

I am trying to make my pass through qry pull data from my sql tables. ..I have been using vba to change the serve detail. ..

when I running qry I keep on getting call failed #208 error message.
I can view the data in th linked tables. .

not sure what to do tried all most every angle possible. ..



 Public Function GetQueryLinks() 
     On Error GoTo Err_GetQueryLinks 
    'Dim StrConn As ADODB.Connection 
    'Set StrConn = New ADODB.Connection 
    Dim qdf As DAO.QueryDef 
    Dim StrConn As String StrConn = "ODBC;Driver=(Apples_Oranges);SERVER=SQAAPB3SBCA65130.global.Hire.com;Database=Apples_Oranges);;Port=12351;" 
    For Each qdf In CurrentDb.QueryDefs 
         If qdf.Connect <> "" Then 
                qdf.Connect = StrConn 
                Debug.Print qdf.Connect 
         End If 
     Next 
Exit_GetQueryLinks: 
      Set qdf = Nothing 
      Exit Function 

Err_GetQueryLinks: 
     MsgBox Err.Number & " (" & Err.Description & ") in procedure GetQueryLinks of Module basTableLinks" 
     Resume Exit_GetQueryLinks 
End Function

Open in new window


EDIT:
    Code formatted for readability
    mbizup, Access Topic Advisor -- 2013-09-17
route217Asked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Let's reproduce the issue.
1. Create a new blank database.
2. Create one table named x.
3. Create a pass-through query named: ptq_test
4. Type sql: SELECT * FROM dbo.tbl;
    where dbo.tbl is a table in dbo schema.
Assume sqlserver instance is SQLEXPRESS.
5. Run query, select data source Dialog box appears with File Data Source Tab selected.
6. New - SQL Server Native client 11.0 - next - type name to save: Browse and save: sqlTest
   next-Finish - type description.
7. Select server - next -next 0 change default database: select your database. Next - Finish
8. Ok - Ok
9 You should see the result of the pass-through query.
Waiting for your feedback!
0
 
hnasrCommented:
Pass-through query uses DSN to a sql database, to retrieve data. It is not related to linked tables.
When you create a pass-through query, set the ODBC Connecting str to the DSN defining the required database.

Then the sql is like:
Select * from tabl;

If you get an error, check the ODBC connecting string.
0
 
route217Author Commented:
hi hnsar

apologies if the question was poorly worded...I have completed tge dsn section u mention above...and connection to the to the sql db was fine...8 have also set the string correctly...

but still keep on getting call failed error messages...
0
 
hnasrCommented:
Back in 2 hours.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.