Change sql connect on pass through qry

Hi Expert (using access 2003)

I cannot work out how to change the DSN on the pass through qry or the connect so it's point at the new sql server as opposed to the old one which it keep on looking for...
route217JuniorAsked:
Who is Participating?
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.

omgangIT ManagerCommented:
Open the pass-through query in design view - most likely be a SQL window.  Right-click on the window header (where the name of the query is displayed).  You should see an option for Properties in the pop-up context menu.  Select Properties to see/modify the ODBC Connect Str setting.

OM Gang
0
route217JuniorAuthor Commented:
OM gang - thanks for the feedback

I haqve already tried that method and saved the access file and still no luck...
0
route217JuniorAuthor Commented:
the error message i am getting is

ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Invaild object name 'apples_extact_tbl_oranges'. (#208)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

omgangIT ManagerCommented:
Are you using a DSN?  What does the connect string look like?  Please post it.
OM Gang
0
route217JuniorAuthor Commented:
Hi yes using a dsn.....

i cannot post the actual connect string,,,,,

the connect string is fine trust me on that one - please..
0
route217JuniorAuthor Commented:
some one suggested...

Open  the Visual Basic editor and create a new sub

Loop through the Querydefs object
                If querydef connect property starts with odbc then amend this to be the new connection


not sure how to do this....
0
omgangIT ManagerCommented:
Hold on a bit.  I have a code routine to do that.  But it won't be any different than changing it manually.
OM Gang
0
route217JuniorAuthor Commented:
Hi OM Gang

the table it cannot find in the sql has data and opens prefectly...when i run the pass through it doesn't like it....
0
omgangIT ManagerCommented:
It really sounds more like a SQL Server security issue to me.  Have you granted access to that table for the user credentials being used by the pass-through query?
OM Gang
0
omgangIT ManagerCommented:
Here's a function to get the connect strings from queries that have them.

As it is below it will print the connect string to the Immediate Window.  I have a few lines commented.  Uncomment those and make appropriate changes to the strConn assignment and it will replace the connect string on the queries that have one.
OM Gang





Public Function GetQueryLinks()
On Error GoTo Err_GetQueryLinks

    Dim qdf As DAO.QueryDef
    Dim strConn As String
   
    strConn = "'ODBC;Driver={Microsoft ODBC for Oracle};UID=MyUID;PWD=MyPass;SERVER=TheServer;"
   
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Connect <> "" Then
            'qdf.Connect = strConn
            Debug.Print qdf.Connect
        End If
    Next
   
        'refresh querydefs collection
    'CurrentDb.QueryDefs.Refresh
   
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
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
route217JuniorAuthor Commented:
om gang


Have you granted access to that table for the user credentials being used by the pass-through query? how do u do this??
0
omgangIT ManagerCommented:
In SQL Server Management Studio, select the db and then right-click on the table in question.  Choose Properties.  Select the Permissions page.  If you're setting up the pass-through query from Access to use a service account you need to make sure that account has been granted appropriate (Select, Insert, etc.) permissions.

Test - if you can access the table in SQL Server Management Studio while connected with your user credentials then test by setting the connect string for the Access pass-through query using your user credentials.

OM Gang
0
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.