Convert an Access Database to MySQL

[pjw001]

Hi,

I'm not sure whether this is the appropriate forum, but I had to start somewhere.

I have been asked by a local charity how to convert an Access database to MySQL, but I have a few questions before it is worth dealing with that question.

They have an Access database that they access via Access Forms. This works fine for people in the office (and therefor on a fast network), but when they try and use it via VPN it is either very slow or just fails.

The first question is, will converting the data to MySQL potentially solve this problem?

If it will work, can the existing forms still be used?

If this is just not the way to solve the problem, does anyone have any suggestions?

Many thanks.

 

[spamjim]

Configuring MySQL as a data source for each user or their system could allow existing Access forms to connect. You would just be accessing tables stored on the MySQL server instead of in Access. All this action would do is relieve the need for VPN but you would still need to make sure secure connections are being made to the MySQL tables.

 

[vacunita]

The first question is, will converting the data to MySQL potentially solve this problem?

That depends on what is causing the slowness.

If its fast in the office, my guess is the VPN connection does not have sufficient bandwidth, and changing to MYSQL may not really see an improvement.
i.e Changing the speed of the database, will not affect the transfer rate over the VPN.

If it will work, can the existing forms still be used?

I believe Access Forms can be set up to connect to a MYSQL database. you'll need to find the forms config settings, and alter those to point to the new mysql db.

If this is just not the way to solve the problem, does anyone have any suggestions?

I would start with the VPN. Does anything else suffer slowdowns when done through the VPN? How is the VPN setup? etc... You may need a network consultant to figure out where the bottleneck is.

----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

 

[pjw001]

Hi,

Thank you both for your replies.

spamjim - I don't understand why VPN would no longer be needed if MySQL is used. Can you explain.

vacunita - I can't answer your question about other problems when using VPN. (This question is the result of a very brief conversation.)

I have very little knowledge of Access or MySQL and am really just trying to get a bit of a handle on what may or may not be possible or worth trying.

From the answers from both of you it is looking like the change to MySQL will probably not achieve anything.

Thanks.

 

[vacunita]

My questions were more rhetorical for you to research rather than answer to me. i.e Figure out if the slowness is because of Access or the VPN.

As to mysql without the VPN, as long as mysql has a direct connection to the internet you can use, you could potentially connect directly to it without the VPN.

Generally routers can be set to forward requests to specific IP addresses. So as long as you have the public IP you can connect to MYSQL directly by having the router forward any requests to mysql to the mysql server whichever it may be.

----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

 

[spamjim]

The irony of the Microsoft Access name is that it is not really suited for access by multiple users unless you connect it to a web service (ie. SharePoint, IIS, MySQL).

You have the option to attempt to maintain the current structure (one user paradigm [or many users posing as one user]) or move to a multi-user system. At the basic level, MySQL can be used for data storage and Access can be used for the forms/front end. However, you should want to expand the design of this database to handle multiple users, and understand/record the changes that each user makes to the data. MySQL can be used in that multi-user area as well but you'll likely abandon the Microsoft Access forms (which assume a single user on a desktop database).

Microsoft has some options of its own to allow access to MS Access away from the local system:

https://support.office.com/en-us/ar...database-03822632-da43-4d8f-ba2a-68da245a0446