route217
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...
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...
ASKER
OM gang - thanks for the feedback
I haqve already tried that method and saved the access file and still no luck...
I haqve already tried that method and saved the access file and still no luck...
ASKER
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)
ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Invaild object name 'apples_extact_tbl_oranges
Are you using a DSN? What does the connect string look like? Please post it.
OM Gang
OM Gang
ASKER
Hi yes using a dsn.....
i cannot post the actual connect string,,,,,
the connect string is fine trust me on that one - please..
i cannot post the actual connect string,,,,,
the connect string is fine trust me on that one - please..
ASKER
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....
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
OM Gang
ASKER
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....
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
OM Gang
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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??
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
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
OM Gang