Angel02
asked on
select query in MS Access
I need to run a simple select query in MS Access Database
Select * from <tablename>
The above query fails throwing "Connection does not exist.. Call failed" error.
The problem is that I think there is something wrong with my permissions to access that table. I now want to run the same query acting as a different user who has the permissions.
Can I do that in MS Access? Something like,,
EXECUTE AS USER = 'domain\someuser';
SELECT HAS_PERMS_BY_NAME('X','TAB LE','SELEC T');
REVERT;
Select * from <tablename>
The above query fails throwing "Connection does not exist.. Call failed" error.
The problem is that I think there is something wrong with my permissions to access that table. I now want to run the same query acting as a different user who has the permissions.
Can I do that in MS Access? Something like,,
EXECUTE AS USER = 'domain\someuser';
SELECT HAS_PERMS_BY_NAME('X','TAB
REVERT;
ASKER
Thank you for the response.
When I open an MS Access database on my machine, am I logged in as a default user? How can I know as which user I am accessing the Access Database. Is it the default windows authentication?
How can I login to an Access database as a particular user?
When I open an MS Access database on my machine, am I logged in as a default user? How can I know as which user I am accessing the Access Database. Is it the default windows authentication?
How can I login to an Access database as a particular user?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My Access application is linked of an Oracle database. It is MS Access 2003 version and the extension is .mdb.
I am trying to open one of the linked tables and it is throwing "ODBC call failed - Conenction does not exist" error. I am using the same ODBC connection to open other linked tables of the same Oracle database. All other are opening fine except one.
Also, this one table that is not opening is successfully accessed by a macro which is scheduled to runs every morning. And when I go to ISQL and type in the command:
CONNECT username*password@database
Select * from <linkedtablename>;
It successfully returns the table results. I am just not able to access the table from MS Access. I don't know if something is wrong with the table or the access.
I am trying to open one of the linked tables and it is throwing "ODBC call failed - Conenction does not exist" error. I am using the same ODBC connection to open other linked tables of the same Oracle database. All other are opening fine except one.
Also, this one table that is not opening is successfully accessed by a macro which is scheduled to runs every morning. And when I go to ISQL and type in the command:
CONNECT username*password@database
Select * from <linkedtablename>;
It successfully returns the table results. I am just not able to access the table from MS Access. I don't know if something is wrong with the table or the access.
Well to me the thing to try would be to delete the linked table and re-link it.
(Perhaps take a backup of the Access file first)
(Perhaps take a backup of the Access file first)
ASKER
I did that already. I in fact created a new Access database and added it as a linked table. But it would still give the same error.
<<I am trying to open one of the linked tables and it is throwing "ODBC call failed - Conenction does not exist" error. I am using the same ODBC connection to open other linked tables of the same Oracle database. All other are opening fine except one.>>
Please do the following:
1. Open the database.
2. Do Ctrl/F11 (brings up the VBA editor)
3. Do Ctrl/G (brings up the debug window)
Do:
? CurrentDB().tabledefs("<na me of table that works>").Connect
and hit enter.
then do:
? CurrentDB().tabledefs("<na me of table that does NOT works>").Connect
and hit enter
Make sure you replace everything in the < > (and including them) with the name of the tables nvolved.
Copy and Paste the results of both commands above to here.
If they contain user name/passwords, make sure you X out part of them as this is a publically viewable thread.
Jim.
Please do the following:
1. Open the database.
2. Do Ctrl/F11 (brings up the VBA editor)
3. Do Ctrl/G (brings up the debug window)
Do:
? CurrentDB().tabledefs("<na
and hit enter.
then do:
? CurrentDB().tabledefs("<na
and hit enter
Make sure you replace everything in the < > (and including them) with the name of the tables nvolved.
Copy and Paste the results of both commands above to here.
If they contain user name/passwords, make sure you X out part of them as this is a publically viewable thread.
Jim.
ASKER
Both the commands returned absolutely same results
? CurrentDB().tabledefs("<na me of table that works>").Connect
and hit enter.
This returned:
ODBC;DSN=<ServerABC>;UID=< User1>;PWD =<Pwd1>;
then do:
? CurrentDB().tabledefs("<na me of table that does NOT works>").Connect
This also returned
ODBC;DSN=<ServerABC>;UID=< User1>;PWD =<Pwd1>;
? CurrentDB().tabledefs("<na
and hit enter.
This returned:
ODBC;DSN=<ServerABC>;UID=<
then do:
? CurrentDB().tabledefs("<na
This also returned
ODBC;DSN=<ServerABC>;UID=<
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just saw this comment! I'll give this a try.
You would have to login to your app as the other user.