Relink Tables
By Richard Rost

5 years ago

Relink Tables when your Back-End has Moved

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this video, I will show you how to relink the tables to your back-end file in Microsoft Access, should the location of that table move. We'll see how to use the Linked Table Manager to relink the file.

Nicholas from Baltimore, Maryland (a Platinum Member) asks: I recently installed a new file server. This involved moving everything around, including my Access back-end database file. I split my database like you instructed in your video. Now, I'm getting a run-time error 3024 with Access saying it can't find the file. How do I fix this?

Members

Members will learn how to relink the back-end file automatically using a little VBA. This way, your users and you don't ever need to use the Linked Table Manager. We will prompt them and ask for the new location by having them browse to it.

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!

Links

Split Database: https://599cd.com/Split
Error Handling: https://599cd.com/ACD15
TableDefs: https://599cd.com/ACD26
Relink with VBA: https://599cd.com/ACD33

Learn More

FREE Access Beginner Level 1
FREE Access Quick Start in 30 Minutes
Access Level 2 for just $1

Free Templates

TechHelp Free Templates
Blank Template
Contact Management
Order Entry & Invoicing
More Access Templates

Resources

Diamond Sponsors - Information on our Sponsors
Mailing List - Get emails when new videos released
Consulting - Need help with your database
Tip Jar - Your tips are graciously accepted
Merch Store - Get your swag here!

Questions?

Please feel free to post your questions or comments below or post them in the Forums.

Keywords

microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, external data, linked table manager, data source name, data source information, runtime error 3024, runtime error 3044, relink, different names, mde file, tabledef, tabledefs, pickfile, relink tables vba, vba relink tables, manage linked tables, automatically relink microsoft access tables, how do you relink a table, modifying linked tables, update linked tables, link to table vba

Comments for Relink Tables

Age	Subject	From
2 years	Relink link a link	Jeffrey Kraft
2 years	Error Message	Christopher Godfrey
3 years	Link tables	Simon Levesque
5 years	Relink Tables	John Davy
5 years	Extra Access warning	Bert Harmsma

Subscribe to Relink Tables
Get notifications when this page is updated

Intro

In this video, I will show you how to relink your tables in a split Microsoft Access database after the backend file has been moved or renamed. We will walk through using the Linked Table Manager to update your table connections so your frontend can find the backend data file again, and I will explain common mistakes to avoid during the process. This is a practical solution for anyone encountering errors when Access cannot locate the backend database after moving files or changing server names.

Transcript

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to re-link your tables to your split Microsoft Access Database when your backend database file has been renamed or moved.

Today's question comes from Nicholas in Baltimore, Maryland, one of my platinum members. Nicholas says, I recently installed a new file server. This involved moving everything around, including my Access Back-End Database file. I split my database like you instructed in your video. Now I am getting a runtime error 3024 with Access. It can't find the file. How do I fix this?

Well, Nicholas, if you move your back-end file or you put it on a computer that is named differently, like you went from server to DB server or something, essentially you just have to re-link those linked tables and tell Access where to find your back-end database file. It's really easy to do. Let me show you how to do it.

For the rest of you, if you don't know what splitting your database is, go watch my Split Database video. It's free. It's on my website. It's on my YouTube channel. There's the link. I'll put a link down below. You can click on the description. Go watch that so you understand what splitting a database is all about.

Here I have a copy of my TechHelp free template. This is a free download off my website. You can go grab a copy if you want to, but this will work with any database file.

Now, the TechHelp free template has everything together in one database file because it's only a single user database. Let's go ahead and create a folder. New folder, and this is where my back-end files are going to go. This can be a server folder or whatever. If you watched the Split Database video, then you know what this means.

Let's go split that database real quick. Database Tools, Move Data, and then Access Database. We're going to split the database.

Where do you want to put it? Well, it's sitting on my desktop in that back-end folder. We're going to drop it in there. I'm going to call this my back-end ACCDB. Split. Database successfully split. Now all of these tables are linked to that new split file.

There's the back-end. Let me open that up. There it is right there. Back-end.accdb. If I open that file up, you'll see there's just my tables. If I go back to the front end file, which is sitting on my desktop right there, open that file up. Access takes forever sometimes to open up. There it goes.

You can see these tables are all linked. If you hover over these, you can see the location: CUsers AmmaCurr. It's true for AmmaCurr. AmmaCurr, desktop, back-end, back-end.accdb. That's where my back-end file got put.

Here's the problem that Nicholas is running into. He moved this folder. Let's say the folder changed. Let's rename it to new. Let me try that again here. Click, click, and then new back-end.

If I try to open this file up, let's try to find that back-end folder or basically his old file server. It looks like it opens just fine, but as soon as you try to access any tables, you get this error. Not a valid path. This is the runtime 3044 that he's getting. Actually, I'm getting a 3044. You might get a 3024. There are a couple of different ones. Bottom line is, Access can't find the back-end file.

So we're going to hit End. Then we're going to go to External Data, Linked Table Manager. Open that up. It shows you here that Access is looking for the data source in the back-end folder, but it's not there anymore. If you hit this little plus over there, you'll see all the tables that Access is expecting to find in that file.

Now you can link these individually to different files if you want to. That's a different class if you've got multiple back-end files. We're just going to link all of them, so click that.

Now don't hit Refresh. Refresh goes out to the same file and tries to find them again. We're going to click Relink. Relink that. Pick your folder and your file, so go back to my desktop. New back-end, wherever it's located, click on the file, and then click on OK.

Now this next prompt can be a little confusing. It says, by default, all selected tables will be relinked to tables with the same name in the new data source. That's what we want. Would you like to relink the selected tables to tables with different names? Most people hit yes here and then go through a whole bunch of steps. Basically, if you want to give the tables different names, say yes, but we don't want to. The default value should be no here. We want no.

So then go to that new file, new back-end, and you should see "succeeded" over here. It's going to find all the same names and relink them all the same. That's it. Hit Close, and now we can open up our database again, and it's pointing to the new location. That's all. It's that one prompt that's kind of confusing for people.

Want to learn more? In the extended cut, 15 minutes long, I will show you how to relink your tables using VB code without having to use the link table manager. Basically, your database will start up. The front end will look for the backend file and say, hey, I don't see your backend tables. Where did they go? It will pop up a little message saying it looks like your tables have moved. Please select where your backend data file went. It's a little prompt right here. You pick the file, and then it will relink them automatically for you. That's all covered in the extended cut, 15 minutes long.

Silver members and up get access to all of my extended cut videos. Gold members can download these databases.

How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use.

Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.

Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.

If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all.

Make sure you subscribe to my channel, which is completely free, and click the bell icon and select "All" to receive notifications when new videos are posted.

Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.

YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list.

If you have not yet tried my free Access Level One course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level One, Level Two is just one dollar. It is also free for all members of my YouTube channel at any level.

Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.

Click here to watch my free Access beginner Level One course, more of my TechHelp videos, or to subscribe to my channel.

Thanks for watching this video from AccessLearningZone.com.

Quiz

Q1. What problem did Nicholas encounter after moving his Access backend file?
A. He could not open any forms in the database
B. He experienced a runtime error because Access could not find the backend file
C. He lost all his tables after moving the file
D. He could no longer use macros in the database

Q2. What is the primary purpose of splitting your Access database?
A. To allow multiple users to edit forms simultaneously
B. To separate the tables (backend) from the user interface (frontend)
C. To enable automatic backups
D. To improve graphics rendering

Q3. Which Access tool allows you to update the path to your linked tables after moving the backend file?
A. Compact and Repair Database
B. Linked Table Manager
C. Database Documenter
D. Relationships Window

Q4. When using the Linked Table Manager to relink tables, why should you avoid clicking Refresh?
A. Refresh will delete all your tables
B. Refresh only checks the old file path again and does not prompt for a new path
C. Refresh renames your tables automatically
D. Refresh loads data from an external website

Q5. What should you select at the prompt that asks if you want to relink the selected tables to tables with different names?
A. Yes, to change the table names automatically
B. Yes, to duplicate the tables
C. No, to keep the table names the same
D. No, to remove all table links

Q6. What message does Access display if it cannot find the backend database file when you try to access a linked table?
A. Table not found, error 404
B. Not a valid path, error 3044 or 3024
C. Invalid form reference
D. Out of memory error

Q7. What additional functionality is demonstrated in the "extended cut" of the video?
A. How to use forms more efficiently
B. How to relink tables using VBA code
C. How to create reports
D. How to split the database

Q8. What benefit do Gold members of AccessLearningZone.com receive?
A. Access to extended cut videos only
B. Access to download sample databases and the Code Vault
C. Only access to free videos
D. Access to instructor live chat only

Q9. What is the suggested first action if you are not familiar with what splitting your database means?
A. Purchase the gold membership
B. Watch the free Split Database video
C. Call customer support
D. Use the Linked Table Manager immediately

Q10. What is the main reason Access cannot locate the backend file after it is moved or renamed?
A. The table data is deleted
B. The file path stored in the links no longer matches the actual file location
C. The Access version is outdated
D. The frontend file is corrupted

Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone addresses a common issue that arises after splitting your Microsoft Access database and moving or renaming your backend database file. As your instructor, I will guide you through the process of re-linking your tables so everything continues working smoothly after such changes.

I recently received a question about this topic. After moving an Access backend file to a new file server, the database started showing runtime errors and could not find the backend file. This is an expected problem whenever you move your backend file to a different location or change the server name. To fix this, you need to tell Access where the backend file is now located by re-linking your tables. Fortunately, the process is straightforward.

If you are not familiar with the concept of splitting your database, I recommend watching my free video on how to split a database. This will provide you with the background you need to understand why and how splitting a database works.

Let me give you a quick demonstration using my free TechHelp template, which you can also download from my website. This template is set up as a single user database initially, so all tables start out in one file. The first step in splitting a database is to create a new folder for your backend files. This might be on a server or anywhere you choose. After creating this folder, use the "Database Tools" and "Move Data" options in Access to split the database. You will be asked where to put the backend file. Specify the folder you just created and give your backend file a name. Once the split is complete, your frontend database will now have linked tables pointing to the backend file.

To verify, open the backend file and you will see it contains only the tables. The frontend file will contain everything else and the tables will have little linked icons next to them. If you hover over these tables in Access, you can see the current folder path to the backend file.

Now, let's consider what happens when you move, rename, or relocate your backend folder, as was the case in today's question. When you try to use your database after such a change, Access will still be looking for the backend file in its old location. The result is a runtime error message saying that the file cannot be found, with error codes like 3024 or 3044.

The solution is simple. In Access, go to the "External Data" tab and select the "Linked Table Manager." This tool shows you all of your linked tables and the current, incorrect path to the backend file. Select all the tables to re-link. Instead of clicking "Refresh," which only tries to reconnect using the existing path, choose "Relink." Then navigate to the new location of your backend file and select it.

Access will prompt you about whether you want to link to tables with different names or not. In almost all cases you should select "No," which tells Access to reconnect the tables as they are. Once finished, you'll see a "succeeded" message and your tables will now be linked to their new location. Your database is fully functional again.

For those who want to automate this process even further, in today's Extended Cut video, I show you how to use VBA code to relink your tables without needing to use the Linked Table Manager. With this setup, when the database starts and cannot find the backend, it will prompt you to select the new backend file, then automatically relink everything for you. I go through the entire process step by step in the extended cut.

Silver members and higher can watch all Extended Cut videos. Gold members have download access to all sample databases and functions, and Platinum membership gives you access to my full range of beginner and some expert courses on a variety of topics.

If you enjoyed this tutorial, please remember to like the video, leave a comment with your thoughts or questions, and subscribe to the channel for free. Turn on notifications so you do not miss future lessons. For more details, links to related content, and information on my courses and mailing list, be sure to check the video description.

If you have not yet taken my free Access Level One course, you can sign up for that on my website or YouTube channel. It covers all the basics you need to get started with Access, and Level Two is just one dollar or free for channel members.

If you would like your question answered in a future TechHelp video, visit my TechHelp page and submit your question.

For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.

Live long and prosper, my friends.

Topic List

Relinking tables after moving Access backend file
Using the Linked Table Manager in Access
Identifying missing linked tables due to file location changes
Navigating and selecting the new backend database file
Understanding Linked Table Manager prompts for table names
Verifying successful relinking of tables to the new backend location
Testing linked tables after relinking process