?
Solved

select query in MS Access

Posted on 2012-09-18
10
Medium Priority
?
523 Views
Last Modified: 2012-11-28
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
Comment
Question by:Angel02
  • 5
  • 3
  • 2
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38411864
No there is nothing like that in Access/Jet sql.
You would have to login to your app as the other user.
0
 

Author Comment

by:Angel02
ID: 38414294
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1000 total points
ID: 38414410
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Angel02
ID: 38414488
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38416618
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
 

Author Comment

by:Angel02
ID: 38467927
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
 
LVL 58
ID: 38469857
<<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
 

Author Comment

by:Angel02
ID: 38483909
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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 38485678
<<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
 

Author Comment

by:Angel02
ID: 38553448
Just saw this comment! I'll give this a try.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question