Link to home
Start Free TrialLog in
Avatar of Angel02
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','TABLE','SELECT');
REVERT;
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

No there is nothing like that in Access/Jet sql.
You would have to login to your app as the other user.
Avatar of Angel02
Angel02

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?
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland 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
Avatar of Angel02

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.
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)
Avatar of Angel02

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("<name of table that works>").Connect

and hit enter.

then do:

?  CurrentDB().tabledefs("<name 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.
Avatar of Angel02

ASKER

Both the commands returned absolutely same results

?  CurrentDB().tabledefs("<name of table that works>").Connect

and hit enter.
This returned:

ODBC;DSN=<ServerABC>;UID=<User1>;PWD=<Pwd1>;


then do:

?  CurrentDB().tabledefs("<name of table that does NOT works>").Connect

This also returned
ODBC;DSN=<ServerABC>;UID=<User1>;PWD=<Pwd1>;
ASKER CERTIFIED SOLUTION
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
Avatar of Angel02

ASKER

Just saw this comment! I'll give this a try.