Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland asked on

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...
Microsoft Access

Avatar of undefined
Last Comment
omgang

8/22/2022 - Mon
omgang

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
ASKER
route217

OM gang - thanks for the feedback

I haqve already tried that method and saved the access file and still no luck...
ASKER
route217

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)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
omgang

Are you using a DSN?  What does the connect string look like?  Please post it.
OM Gang
ASKER
route217

Hi yes using a dsn.....

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

the connect string is fine trust me on that one - please..
ASKER
route217

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....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
omgang

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
ASKER
route217

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....
omgang

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
omgang

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
route217

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??
omgang

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