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
route217JuniorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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
route217JuniorAuthor 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
Hamed NasrRetired IT ProfessionalCommented:
Back in 2 hours.
0
Hamed NasrRetired IT ProfessionalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.