• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

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;
0
Angel02
Asked:
Angel02
  • 5
  • 3
  • 2
2 Solutions
 
peter57rCommented:
No there is nothing like that in Access/Jet sql.
You would have to login to your app as the other user.
0
 
Angel02Author Commented:
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?
0
 
peter57rCommented:
If the application doesn't ask you to login then it is fairly certain it doesn't know nor care who you are.  (It's not 100% certain - there are exceptions).

So yes - you are could be logged in to Access as the default user (named Admin see below).  

Access does not use Windows authentication.  If it's an mdb file, then Access has its own method of user identification which can be invoked by the developer (default Admin as above) .  If it's an accdb file from Access 2007 or later it has no user identification of its own and if any constraints are in force they must have been imposed by the developer.  

However this might be totally irrelevant to the problem you are having.

Are you dealing with a native Access table or is the Access application linked to tables in a a server database such as SQL_Server or Oracle?

Which version of Access are you using?  WHat is the extension of the Access database you are opening?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Angel02Author Commented:
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.
0
 
peter57rCommented:
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)
0
 
Angel02Author Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
 
Angel02Author Commented:
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>;
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Both the commands returned absolutely same results>>

 That means then that it is something on the Oracle side.  What you can do as a test is to replace the UID and PWD in the connect string with the user that you wanted to try originally in your question and see if it works.

Do that by printing the string as you did above.  Adjust it in the debug window as needed, then copy and type:

CurrentDB().TableDefs("<name of table that does NOT works>").Connect = "

 and paste the string followed by another quote and then a return.  Verify the change again with:

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

if good, do:

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

and hit return.  You should not get an error.  Now try opening the table.  If it works, then most certainly it is a permissions problem.

You have a few choices:

1. Leave the existing username/pwd on the table
2. Grant permissions on the table for the original user
3. Use SQL Pass-through queries - these are sent directly to the server and not touched by JET.  With that, you'd be able to give syntax like you asked about in your question.  But you won't be able to use any JET expressions or link to local tables.  All you'll be able to do is pull data directly from Oracle.

Jim.
0
 
Angel02Author Commented:
Just saw this comment! I'll give this a try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now