Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-17
6
Medium Priority
?
795 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 2000 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
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.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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