Viewing Thread:
"Microsoft Access 2007 to Freeola MySQL?"

I've read a few threads on here where people have this setup in place.

Just wondering exactly how you are getting this to connect properly?

I get as far as creating the .dsn file, entering server and login details.. then I get the following error:
---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:

SQLState: '08001'

SQL Server Error: 3

[Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [3].

Connection failed:

SQLState: 'HYT00'

SQL Server Error: 0

[Microsoft][SQL Server Native Client 10.0]Login timeout expired

Any pointers would be greatly appreciated :-)

Pleased to hear you're almost there.

MS Access can be a temperamental beast. Just keep remembering to repair and compact regularly and it should be OK.

I also find it better to split the tables from the front end.

I'll keep an eye out on here if you have any further questions/issues.

cheers
JTD

Well JTD...

Things are looking up!

I installed the 32bit version of the sql driver and access was much happier. It's showed me all the tables in the sql database, and I've been able to link them no problem.

PK = Primary Key... I'm with you now. I'm not completely down with programming abbreviations but thanks to people like yourself I'm working through it.

Only problem I have now is that Access keeps crashing every now and then when I try to link.. but I'm sure I can work that out one way or another. Even if I have to just link one table at a time.

Thank you so much for your help on this.. and hopefully it will help many more people once it gets picked up by Google.

Thanks again.

Hi Bibowoka,

Good to hear you're making progress - this really isn't that complicated once you get the hang of it...

It's distinctly possible that creating a 32bit ODBC DSN would be a good move.


Re: 'My goal is to integrate an existing SQL table into my database (along with an already established access database).'

What you're trying do to is create a 'linked table'.

Sorry if this next bit is teaching you how to suck eggs at any point..

Step by step:
Right click the Tables 'tab' in Access control list.
Choose 'Link Tables' from the options.
From the pop-up 'file select window' choose ODBC from the drop down 'files of type' list.
Your DSN should be in the list of 'Machine Data Source'
Click your DSN - OK

You shold get a list populated with your MySQL tables. Choose one or more tables to link. - click OK.

If all is OK then your chosen tables should be created in the MSAccess table list as links. You can treat them as if they were local tables (unless your offline of course).

Now back to the PK bit.
Each table in your MySQL database MUST have a Primary Key (PK). That is to say each table MUST have a unique record identifier (such as clientID or RecID or Username, etc..) and it MUST be identified as the PK through MySQL Admin.

IF the PK is not set then the table link process will prompt for an 'identifying field' however UPDATE and DELETE actions will probably fail.


Try this - if any of the above fails then let me know where.

Good luck
JTD

Hi Jim the dog...

Thank you so much for your reply so far.. I've got much further along the route to success now.

My goal is to integrate an existing SQL table into my database (along with an already established access database).

So I've installed the driver, setup a System DNS, hit test and it connected. However in access the System DNS isn't visible so I entered it into User DNS instead. This then showed up in access ODBC list.

I hit ok, and it throws up the following message:
OBDC-Call failed.
[Microsoft][ODBC Driver Manager] The specified DNS contains an architecture mismatch between the driver and application (#0).

Could this be because the ODBC driver I downloaded is 64bit and access is 32bit?

I'll keep playing around but wondered if this was something simple.

Also, what is the individual 'PK' you talk about to enable alterations to data?

Thanks
Chris

Hi Bibowoka,

Are you wanting to link the MySQL table to the Access project or do you want to migrate existing tables to MySQL?

Best path for either need is to use an ODBC MySQL connection.

If you haven't already do so then download the latest MySQL ODBC driver from the MySQL dev site here (http://dev.mysql.com/downloads/connector/odbc/) and install it.

Then in WIndows ODBC connection manager (control panel, admin tools, ODBC) create a new System DSN with the MySQL Driver.

Once you've created the ODBC connection - with the freeola MySQL connection details and test it (and it's successful)..

... in MS Access use the link tables wizard to connect via the ODBC you created to the Freeola MySQL Database.

One thing to note is you MUST creat a PK for each of the tables otherwise insert/update/deletes won't work.

Ta Da!

Let me know how you get on.

JTD

Hi Bibowoka,

I would love to offer some advice but I'm not a database person I'm afraid. Hopefully some others will be along and offer some help.









[s]Hmmm...[/s]

I've read a few threads on here where people have this setup in place.

Just wondering exactly how you are getting this to connect properly?

I get as far as creating the .dsn file, entering server and login details.. then I get the following error:
---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:

SQLState: '08001'

SQL Server Error: 3

[Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [3].

Connection failed:

SQLState: 'HYT00'

SQL Server Error: 0

[Microsoft][SQL Server Native Client 10.0]Login timeout expired

Any pointers would be greatly appreciated :-)