Solved

MS-Access 2007 Runtime; error connecting to MySQL with ODBC Data Adapter

Posted on 2009-05-17
6
782 Views
Last Modified: 2013-11-28
EE Experts:

This is the 2nd question on this subject that I have had to ask in the last 2 months. The last time I found my own solution and posted it here on Experts-Exchange for all to benefit. Here is the link:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24250236.html

It looks like I have run out of steam on finding an answer to this one. By going to the link I posted in my last question I found these instructions on how to edit the Windows Registry to keep my query from being blocked by Microsoft's new security constraints. Here is the link:

http://office.microsoft.com/en-us/access/HA012301871033.aspx#4

The instructions I preformed at this link did not fix my problem. I still get the error message in the attached screen shot.

The difference between this time and last is that last time I was working in the MS-Access IDE; i.e., the MS-Access that comes with MS Office 2007 Professional edition. Now I am trying to accomplish the same thing except here I am using the free MS-Access 2007 Runtime that is downloadable from the Microsoft support website.

I found 2 questions answered here on EE that lead me to believe that there is a solution for my problem that works. Here are the links to both of them:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_23934683.html?sfQueryTermInfo=1+10+allownetworkloc

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23751632.html?sfQueryTermInfo=1+10+allownetworkloc

In both of these questions I don't believe an ODBC data adapter was being used. I need the ODBC data adapter because it is the only way that I know of to use the Reporting capabilities of MS-Access on a MySQL database. This ODBC data adapter is being used in a ODBC Data Source Name on another machine with the full MS-Access 2007 environment and works just fine. One of the reports that works just fine uses a Pass Through UNION Query which I need because MS-Access 2007 strips out my column aliases without it. On the machine that I am having a problem there is a previous version of my report package written in MS-Access 2002 that uses the same ODBC Data Source Name (DSN) without a problem. So the ODBC DSN should not be a problem. I have the ODBC DSN configured the same on all machines which will be using my report package except on the development machine the network connection is to localhost and on the test machine the network connection is the host name of my development machine so that the test machines will be looking at the same database as the development machine is looking at.

Attached is a screen shot of my Windows Registry editor with one of the changes recomended in one of the EE questions linked to above. Maybe I am editing my Windows Registry wrong?

Thank you for any help you can give me.

Ted Palmer
0
Comment
Question by:Ted Palmer
  • 4
  • 2
6 Comments
 

Author Comment

by:Ted Palmer
ID: 24406700
I am not seeing my uploaded screen shots when viewing my question. So I'll upload them again,
ODBC----Call-failed.bmp
0
 

Author Comment

by:Ted Palmer
ID: 24406705
Here is the screen shot for my Windows Registry Editor.
RegistryEditor-01.bmp
0
 
LVL 5

Accepted Solution

by:
noetymology earned 500 total points
ID: 24406761
Have you tried packaging your database using Access Developer Extension 2007 or are you simply renaming the file to .ACCDE and giving it out to your user? When we use the ADE2007 the database contents are not blocked by Trust Center.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Ted Palmer
ID: 24406995
noetymology:

Yes I created the executable and msi install file using Access Developer Extension 2007. In fact I didn't say this but I should have; the MS-Access 2007 database on the client is in a ACCDR file type. I assume that the 'R' on the end means that the file is supposed to be for use with the Runtime engine. My environment is Windows XP for both the develoment and client machines. I am testing the client side with a Windows XP machine, but I have been deploying my application on Windows Vista machines because that is what my beta tester has. The RDBMS runs on a Microsoft Small Business Server, for which the underlying OS is Windows Server 2003.

In your environment are you using an ODBC data adapter with Linked tables? Or perhaps you can use linked tables without an ODBC data adapter if you have a split MS-Access database.
0
 
LVL 5

Expert Comment

by:noetymology
ID: 24407074
ok..BTW In my previous post, i meant to ask are you simply renaming the file to .ACCDR and not ACCDE. Sorry for the typo error
0
 

Author Comment

by:Ted Palmer
ID: 24464062
All:

It is my professional opinion that, given the current state of the Microsoft Access 2007 Runtime engine, it is not possible to use the Microsoft Access 2007 Runtime engine with databases that have a pass through query that uses an ODBC data adapter. I had a link to a Microsoft website KB article that described how to accomplish this but I lost it. It cited another "Windows Registry Hack" which gave specific instructions on how to add a Windows Registry key that would allow an MS-Access 2007 ACCDR (Runtime) file with a pass through query to be trusted. This registry hack in my exerience did not work. Just like none of the other registry hacks that I have found and tried did not work. The fact that a MS-Access 2007 database has a pass through query that uses an ODBC data source name (DSN) causes the Runtime engine to flag the entire database as "Content Disabled"; i.e. none of the queries to linked tables will work.

If you find evidence to the contrary, please post it here or send me an e-mail at my e-mail address in my profile. If you can't access my profile because you are not an EE subscriber, sent the e-mail to tedpalmer (at) tedpalmer dot com.

Thank you;
Ted Palmer
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now