Access Front End with MySQL Backend? (2 Viewers)

kmarusek

New member
Local time
Today, 06:35
Joined
Apr 20, 2022
Messages
1
Hi Access World,

I'm new to this forum and pretty new to access and mysql. I'm working a small project for a friends company, they have an old split ms access VBA program developed back in 2002. They have a host computer that houses the dB backend file, while they have copies of the front end to all other machines and employees (about 8).

They have had some issues with load times and I thought I'd help them by migrating and linking their backend database tables to a mysql server instance.

I took a copy of their access program to test on my own personal network where I've set my desktop up as the backend mysql host and my laptop as a frontend user. I've migrated the access tables to the mysql instance on my desktop and linked tables, however when I copy the front end file to my laptop it has trouble finding and connecting to the dB. Have I missed a step? The front end file runs fine on the desktop and the desktop is fully accessible to the laptop. I'm having trouble finding documentation or youtube videos. Hope you all can help, thank you.
 

GPGeorge

Grover Park George
Local time
Today, 03:35
Joined
Nov 25, 2004
Messages
1,775
There is a Linked Table Manager in Access. It is used to manage the linking of tables, such as the situation you are in.
You will find it here :

1650493296229.png


Use it to link the FE on your laptop to the tables.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:35
Joined
Jan 23, 2006
Messages
15,361
Haven't done that for several years, but I'd suggest this MySQL link as a starting point.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:35
Joined
May 7, 2009
Messages
19,169
before migrating to mysql, you should try first creating a Persistent Connection to the Backend
and see and test if there is any changes in speed.

linking to mysql/mmsql will not guarantee that it will make the db faster.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:35
Joined
Feb 19, 2002
Messages
42,970
Just converting the BE to MySQL or any other RDBMS is not going to solve any sluggishness. You need to modify the application to use better client/server techniques and to stop binding forms and reports to tables or queries with no criteria and then using local filters. The point of a RDBMS as the BE is for IT to do the heavy lifting. Dragging all that data across the LAN will probably slow down the application rather than speed it up because Jet/ACE are tightly integrated with Access and optimize this "filter" process when the BE is Jet or ACE but NOT when the BE is ODBC.

Before doing the conversion, do some cleanup. Compact the BE. Verify that the relationships are in place and correct. Access automagically creates hidden indexes on Foreign Key fields. You need to make sure that there are not extra indexes since they slow down updates and inserts and make sure that you have indexes on fields that are commonly used for searching like CustomerName.
 

Users who are viewing this thread

Top Bottom