MS Access - Not prompting for Credentials to ODBC Data Source

Question

We are attempting to connect Access to an ODBC data source. We expect, and do see with Excel and Crystal Reports, that when connecting the very first time, a prompt to "Grant Access" to the data source. Then on the first and subsequent connections, that there is an application login (MS Access window expected) and then a product (ie the datasource program) login.

Access seems to use the ODBC connection to see the tables and add them (ie configure), but never spawns the "Grant Access" dialog window, and while I sometimes do\sometimes do not get a login, it does not retrieve data, which is not unexpected under the circumstances.

As the ODBC connection works with the 2 other applications we are testing, I am inclined to think that this is related to Access. I have configured the data source's data path as "Trusted", but not finding anything else related to security.

FWIW, the application is Intuit Lacerte Tax. The SDK Manual is here and authentication is discussed beginning on page 20.

Answer 1

Ok, I would try a few things. The "steps" and how Access remembers these connections can be rather complex (it does have a defined process to what may well seem like "random".

The first thing I would try is when you launch the ODBC connection (ALWAYS do this from Access BTW). Then you have several choices, but "when" possbile, and "if" this choice works, then I suggest using that choice. That "first best" choice (which will not always work) is to choose this opiton (a FILE dsn).

And trying even hard to make above work? When you launch Access, try right click and "run as administrator" (you only need to do this during linking of the table(s).

So, I tap windows key, type in access, and then I get/see this:

so, in above, (I have access 2010, and then later newer version of access installed). So, choose run as administrator, since this "increases by large" margins the ability for setting up connections to work.

So, next up is to ALWAYS launch/use/let/enjoy ms-access to pop up the ODBC connection manager (don't run it from the control panel).

So, now, we choose external data, odbc.

Or, if using previous, then this:

Now, as noted, as a FIRST choice, try using a FILE dsn, this choice:

A so called FILE dsn might not work, but DO FIRST try to make this choice work. If it does not work, then you have to choose "machine data source".

The reasons for the FILE dsn choice are "many", but several are that this will actually cause Access to "remember" the settings (at least much better then the other options).

And now we can create the connection etc. Note VERY close that on FIRST TIME ONLY of the linking, you will set this check box and dialog (and you miss this step on a table re-link or table re-fresh you DO NOT SEE NOR GET this option again!!! - you have to delete the table link and start over!!!).

So, you should eventually get to this dialog to choose the table(s), and note this option:

Note the check box to "save" the password. You don't want to skip this step.

(and of course answer YES to the dialog that pops up warning you about saving the password.

Also, depending on the type of tables, you may well then get a prompt to choose/pick/use a row PK value. That is this prompt:

So, if all above goes well?

Then you NOT EVEN get a odbc prompt when you now try to use or open a linked table.

The "big" issue of course is that you often find that you can not use FILE dsn, but if at all possible DO TRY to make this "first best" choice work.

If not, then try the machine DSN. The above steps and advice still applies, but a FILE dsn is far better, since you can even share, or place the Access applicaiton on a different computer (assuming the drivers are installed), then no DSN or setup is required on those additional computers.

If the linked table works? Exit access, re-launch the application, and now try opening the linked table - it should not only work, but also work without any ODBC and logon prompts.

Of course some of this depends on the ODBC driver. But, still do try the above, since even if some ODBC prompt does occur, entering the infomration will from that point onwards (as long as you running that "session" of access should result in all linking tables now working, and working without any "logon prompts".

Answer 2

Hi,

This issue could be due to the way Access handles connections to external data sources and has its own security measures.

Here are some methods that you can try-

1. Ensure that your database and ODBC data source are stored in a folder or network location that is identified as a trusted location.
2. Choose "External Content" from the left-hand option in the Trust Centre window. Verify that "Enable All Data Connections (not recommended)" is chosen. As a result, Access can connect to external data sources without asking for authorization.
3. Select "Macro Settings" from the left-hand menu in the Trust Centre window. Make sure that either "Enable all macros" or "Disable all macros with notification" is chosen. This permits all essential macros and code in your database to operate without limitations.
4. Try building a brand-new, straightforward Access database and then establishing the ODBC connection from that point. This can assist in figuring out whether the problem is limited to the database or if more general settings or configurations are affecting the connection.

If these methods do not work for you, try following this blog.

Best Regards.

Answer 3

Hi,

This issue could be due to the way Access handles connections to external data sources and has its own security measures.

Here are some methods that you can try-

1. Ensure that your database and ODBC data source are stored in a folder or network location that is identified as a trusted location.
2. Choose "External Content" from the left-hand option in the Trust Centre window. Verify that "Enable All Data Connections (not recommended)" is chosen. As a result, Access can connect to external data sources without asking for authorization.
3. Select "Macro Settings" from the left-hand menu in the Trust Centre window. Make sure that either "Enable all macros" or "Disable all macros with notification" is chosen. This permits all essential macros and code in your database to operate without limitations.
4. Try building a brand-new, straightforward Access database and then establishing the ODBC connection from that point. This can assist in figuring out whether the problem is limited to the database or if more general settings or configurations are affecting the connection.

If these methods do not work for you, try following this blog.

Best Regards.

Answer 4

Thanks for your replies!

First let me apologize for not responding sooner. While I am subscribed to the topic, I did not get a notification for these replies.

@Albert Kallal

I have followed your suggestion, but I get an error when trying to save the File Data Source I get the error "Unable to perform authorization check. The LacerteTax application must be setup and have resolved the proper realm id."

This is NOT an MS Access error, but has to do with the business application, but it breaks it.

I am at the same place I was with the Machine DSN, but this is helpful as it gives me something to discuss with support (RealmID).

@Tanay Prasad

1 - Done, I added the MS Access DB as trusted along with the actual source data

2 - I do not see this option in our version of Office 365 and unable to find Enable All Data Connections in any of the Trust screens. I do have Trusted Locations => Allow Trusted Locations on my network (not recommended) enabled.

3 - Done

4 - I am keeping it simple. ATM, I am creating a DB with a blank table and trying to link to a single source table only. Still no joy