Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot create a system odbc connector to mysql db in access 2010

Posted on 2011-03-18
14
Medium Priority
?
853 Views
Last Modified: 2012-05-11
Hi

When i try and create a system mysql odbc in access 2010 it wont allow me as it says i dont have administrative priviledges.

I am logged on as the domain admin using access 2010 32bit and the latest mysql odbc 32bit version.

What do i have to do to create a system odbc.

I have tried running access as an administrator and still doesnt make any difference.
0
Comment
Question by:timb551
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35171188
Hi,

why do you try to do it with Access? If you want to create a system ODBC connection, go to the Windows Control Panel where you can simply add and test it.

But I would also recommend to generally don't setup an ODBC connection. You always need to do it on any target computer which want to use your database. Simply use a DSNless connection, you can do that with VBA and only need to use the right connection string (of course you need to install the MySQL ODBC driver on the target computer).

Here's a link which explains how to use DSNless connections in Access:
How to create a DSN-less connection to SQL Server for linked tables in Access

Although it is described for SQL Server the same works with any other external database.

You also need to recreate links in Access any time you change a view or table on the database server so with this it is easy to create a simple sub which removes all links and create them again with a list of table/view names and their corresponding names in Access.

Cheers,

Christian
0
 

Author Comment

by:timb551
ID: 35188664
You can add it in the control panel but then its not available to select within access to link the tables.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35188985
Hi,

did you try the suggestion above to use a DSNless connection?

Your problem with the system DSN could also have something to do with your operating system, if it is 64Bit.
Here are some infos about how to use the 32Bit ODBC drivers in this case:
No ODBC Drivers available for Excel or Access in Windows 7

Cheers,

Christian
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 58
ID: 35189564
<<You can add it in the control panel but then its not available to select within access to link the tables. >>

<<Your problem with the system DSN could also have something to do with your operating system, if it is 64Bit.>>

  You did use the 32 bit ODBC manager correct?  With a 64 bit OS, there are two ODBC managers; one for 64 bit and one for 32 bit.

  Access won't see anything created under the 64 bit ODBC manager.

  NO POINTS HERE PLEASE as Christian has this covered.

JimD.
0
 

Author Comment

by:timb551
ID: 35189668
Hi

I have tried to add the odbc link to the 32bit version of the odbc as well but it says im not logged in with administrative priliveges.


Tim
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 35190008

 Find the command prompt in the menu, right click on it and select "Run As Administrator".

 Then type in:

c:\windows\sysWOW64\odbcad32.exe

at the prompt.

JimD.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35190558
Hi Jim,

I personally have no access to any 64Bit version of Windows or Office until now so it was only a guess..:-)

I didn't knew that Windows 7 now has two ODBC driver settings - good to know. I have no problem with sharing points...:-)

Cheers,

Christian
0
 

Author Comment

by:timb551
ID: 35190668
Ok, tried running the 32bit odbc as an administrator and makes no difference.  still cannot add a system odbc.
0
 
LVL 58
ID: 35192298

 It still says you don't have administrator Privs? or Access doesn't see the DSN?

JimD.
0
 

Author Comment

by:timb551
ID: 35192423
The former
0
 
LVL 58
ID: 35194567

 I really detest UAC at times.  Try it this way:

1. Open explorer.
2. Navigate to Windows\SystemWOW64
3. Locate ODBCad32.exe, right click on it and select "Run As Administrator"

  Does it work and if not, exactly at what point do you get a permissions error?

JimD.
0
 

Author Comment

by:timb551
ID: 35375830
None of these solutions have worked im afriad.  Now paying for microsoft support to see if they can sort.
0
 
LVL 58
ID: 35394790

 OK.  Let me know...

JimD.
0
 

Author Comment

by:timb551
ID: 35744810
Went back and retried the odbcad32.exe and for some reason its now working.

thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

721 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