Link to home
Start Free TrialLog in
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...
Avatar of omgang
omgang
Flag of United States of America image

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
Avatar of route217

ASKER

OM gang - thanks for the feedback

I haqve already tried that method and saved the access file and still no luck...
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)
Are you using a DSN?  What does the connect string look like?  Please post it.
OM Gang
Hi yes using a dsn.....

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

the connect string is fine trust me on that one - please..
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....
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
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....
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
ASKER CERTIFIED SOLUTION
Avatar of omgang
omgang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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??
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