Solved Migrate Linked Back End Table Source From MS Access to MySQL Online (1 Viewer)

LGDGlen

Member
Local time
Today, 15:53
Joined
Jun 29, 2021
Messages
229
In the latest iteration of my database i need to repoint the linked tables to a new source location.

The steps i have been through are as follows:

Step 1 - Create an MS Access database with forms and tables
Step 2 - Test out functionality and sign off
Step 3 - Split database into Front End and Back End files
Step 4 - Store Back End on to shared folder and distribute Front End to limited number of in office users
Step 5 - In office users sign off functionality
Step 6 - Convert Back End tables to MySQL and uploaded to a database hosting site
Step 7 - Create an ODBC connection to the MySQL database

I now need to re-link the Front End to instead of pointing at the shared folder with the Back End file to the MySQL tables online and i'm a bit lost.

I tried:
  • External Data -> New Data Source -> Other Source -> ODBC Database
  • Selected Link to the data source
  • Select the Machine Data Source i created to connect to the MySQL Back End
  • Selected All tables in the list and clicked OK
This just added a duplicate set of tables with "1" on the end of the table name which seemed wrong to me. So i'm sure there is something i've missed so if anyone can give me the steps to do this i'd appreciate it

Kind regards

Glen
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:53
Joined
Oct 29, 2018
Messages
21,358
Hi Glen. One approach you could try is delete all linked tables from your front end and then use the External Data tab again to create new linked tables using ODBC.
 

LGDGlen

Member
Local time
Today, 15:53
Joined
Jun 29, 2021
Messages
229
seems in my cautiousness not to disrupt things i didn't think to do that and was just about to post that thats what i did and it seems to work
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:53
Joined
Oct 29, 2018
Messages
21,358
seems in my cautiousness not to disrupt things i didn't think to do that and was just about to post that thats what i did and it seems to work
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:53
Joined
Sep 12, 2006
Messages
15,613
It would be useful for you to explore a way of rebuilding the connections in code, rather than using the table manager. Users may not have access to the table manager., and you may prefer for them not to need such access.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:53
Joined
Feb 19, 2002
Messages
42,970
Keep in mind that as the tables grow, this could become too slow to use. Directly linking an Access FE to an ODBC BE in the cloud is what people have been asking the Access team to give us for years. In the past 20 years MS has made four failed attempts to give us non-access solutions. by changing the FE to something not Access. What we asked for was very simple. Leave Access alone. Clean up the ODBC connection sufficiently that it will work with reasonable speed over the internet. That's all.

To date that request hasn't been fulfilled so you may be in for some upcoming disappointment once you actually try to use the database. If you have used good client/server techniques and all your forms are bound to queries that select a single record, you might experience a reasonable speed. Just because you can link to remote tables doesn't mean the app will ever work satisfactorily
 

Isaac

Lifelong Learner
Local time
Today, 08:53
Joined
Mar 14, 2017
Messages
8,738
I tend to do just what you did at first - one at a time - it actually helps me stay organized as to which tables are 'done' and which need doing.
Remember, you can highlight and delete (delete key on keyboard) a table any time, and you can right-click and Rename any table at any time - including linked tables.

If MySql online gives you the option to download an ODBC driver that will help you create a DSN, that might make it easier as well.
Not sure, it may or may not help it be faster than whatever the connection string to connect directly to online might be.

For example, Salesforce has this capability, which makes it just like any other on-prem db....create a DSN and have at it.
 

Users who are viewing this thread

Top Bottom