Microsoft Access Split Databases: Automatically Re-Link

[@Bernard-lu6we]

Thanks so much for this clear and thought through approach! Just to add my voice to the plea to make (again?) the code available on the indicated (broken?) link. Many thanks for the effort of producing this video!

[@jaironaranjo3654]

Sir what an amazing videos you are definitely the best!

[@wsitech]

Very very interesting video. I have this problem with clients ALL THE TIME when we do repairs or maintenance on the front end, and it goes back and we have to manually reconnect. We do have a procedure that goes through in AutoExec and automatically relink the tables, but this looks like it can be more generic in nature, less hard-coding needed, i.e. the way I have it now hardcodes one of several strings, commenting out the ones we aren't using. This looks more flexible, albeit far more complex I'll probably have to watch it 2-3 times and step through the code.

[@JonCompass]

I was about to write a similar code, having not found any a while back, so thanks very for this. One question, if the FE links to two BE databases, have you looked at modifying the code to get around that?

[@janezklun]

Just great approach, excelent, thank you

[@vicsplace]

Great job, Thank you for your clear descriptions of the process of the code. There are many great sources out there but none that i have found explain and teach me the way your videos do. again great job and keep it up. one question though, can this code pull the text file information from within the front end say from a table so that i don't have a text file that end user could possible see and alter?

[@Accessjitsu]

+vic k Yes, you can store a path in a front-end table. You just have to remember to change the file name there when you deploy. When the front-end is on your development machine, I assume you would have the file name of your back-end. When you copy that front-end to your customer's machine, you'll need to change the file name and path to theirs.

[@azashukri5322]

What a fantastic video.

[@hassankadhum4127]

Thank u sir for nice video i will test this thanks

[@JWinAus]

This is awesome, thank you for this video. May I ask, in a split DB scenario, is there an automated way to make changes to the back-end database? it does not happen often, but sometimes it is necessary to eg. add a column to an existing table, or even add a table to the backend DB. Is there a way to code this and roll out during an upgrade distribution?

[@HatemEsmat]

Great video, thanks 👍 Can you please send me the code The link is broken, have nothing in the Page Thank you

[@lozzamoore]

The web link above to accessjitsu to get the code here is not found. That's a shame as the video is very good, and I am trying to achieve exactly this. Has the code been deliberately removed or is this a bug please?

[@akokohjoel9854]

Pls can you show us Link to the VB code to relink backend

[@lizimorley7703]

Thanks for this helpful video! One question: Is it a problem that my backend doesn't have a password? I have been debugging and am getting errors in the "RefreshLinks" function. Furthermore, the file dialog box doesn't automatically pop up when the path is not found, it only pops up when I step into that line of code in the debugger. Thanks!

[@anupamdebnath6821]

GetFileFolder("file", "Choose the Access database holding your tables (the backend)") not work.

[@johnwatkins39]

im kinda a noob to access databases, i have built a couple previously, but now i wanna split the databases. i understand some vba code but would like it broke down a little further pls thx for the great video

[@Accessjitsu]

Here's a video on how to split: kzbin.info/www/bejne/lZywlJR3eqmoatE

[@sharathAlive]

Sir, i honestly am very happy to see this code. i have got it to work i guess upto 50% i have a error kindly guide me. i have a "modrefreshlinks/checkbackend error:0 = " Dont know were its going wrong :( im a newbie so please help?

[@waqarshah9651]

If I have two separate split databases with different tables however now I need to link to one table on the second database as it is a large table and needs to be up to date all the time is this possible?

[@wadestarks610]

How long does this auto-relink process take to complete?

[@annarocha6161]

Thank you so much, I figured that out. I added it to my login form. I can't seem to make this work for any other machine. I've tried on different computers and it will only work on mine. Is there away we can talk. I'd be more than happy to compensate you for your time.

[@Accessjitsu]

Are you clicking the "enable" button when it asks you if you want to run macros, and are you trusting the database on the other machines?

[@annarocha6161]

Hello,Thank you for this very useful video. I need to open a login form after the splash screen runs but can't seem to make that happen. Would you mind pointing me in the right direction?

[@Accessjitsu]

right before closing your splash screen use the docmd.OpenForm method.

[@RossAWaddell]

I'm using Access 2016 and after I link a table (whether creating a new link or refreshing the connection info on an existing linked table) it shows up as read-only when I open up the table. Programmatically, I appear to be able to update/insert but not through the standard table view. Intermittently, a linked table will open normally and show the (new) record at the bottom of the table, but not frequently. Any ideas?

[@Accessjitsu]

I've never had this happen to me. It's the intermittent that gets me. If it were all the time, I'd suggest checking folder permissions. Any chance somebody else, or another instance of Access has the backend opened exclusively when this happens? Check the default record locking in the Access Options - maybe "All Records" is checked and somebody else is editing the table at the time?

[@RossAWaddell]

Thank you for replying so quickly. I did some more tests last night and I think it boils down to using Dropbox for my development environment. After installing my app to a local C: drive, all links behave as normal. The intermittent problem with the read-only linked tables when saved on Dropbox might also tie in to the other issue I had when Repairing and Compacting the db; occasionally, it can't rename the newly compacted db ("database.mdb") back to the original file name, so it might be due to the syncing mechanism back to the cloud.

[@thanetenjoy]

I have the same with OneDrive folders. Any solution?

[@RossAWaddell]

@@thanetenjoy When I need to relink tables or do repair/compact, I copy the client file to a non-cloud drive folder (i.e. c:\temp) and do it there, then copy it back. I think this is just the nature of cloud drives and their syncing.

[@thanetenjoy]

@@RossAWaddell ok let me try it today and I will update the results soon. Thank for your support

[@LuisCruz-os2dy]

question away from the topic how can i reset the autonumber from my tables. i know there is a a vba code or any other way please help

[@Accessjitsu]

The only way I know to reset an autonumber is to empty the table and compact and repair. If you renumber while there are already records in the table, you risk creating a collision later, so I don't think Access will let you do it with data in the table.

[@LuisCruz-os2dy]

thank you

[@sappraku]

Thanks very much for this tutorial. Sad news for me, after i tried i get the message "Relink successful "when both front and back-ends are in the same folder But I get the error "modRefreshlinks/Checkbackend error : o =" Please what could be the problem ?

[@Accessjitsu]

That error code of zero isn't telling us much, or rather anything. Are you able to step through the code in the debugger to determine which statement it is failing on?

[@sappraku]

Thanks a million for your response. Sorry but the pop-up box appears with the error modRefreshlinks/Checkbackend error : o = the only option i get is ok, then after clicking ok nothing happens. I think the dialog box is failing to open for me to search or locate the backend. I copied everything from accessjitsu.com/2016/01/10/microsoft-access-split-databases-automatically-re-link-broken-table-links/ Is it also possible to replace the startup form with my login form since i want the login form to open first. Thank very much

[@Accessjitsu]

You can make your login form open first, you can put my startup code in your login form or have the login form open the startup form. Whichever you like. You need to know which line of code is failing. you need to set a breakpoint at the top of the code and then run the code. Step through the code one line at a time using F8, until you find the error. When you are debugging like this, when you hover your mouse over a variable, it will tell you the value, so you can get a better idea of what is happening.

[@vfxmastersthailand9330]

great video but could i ask for a txt of the codes please

[@vfxmastersthailand9330]

thank you so much, but now i have another problem I've made a appointment form that automatically update the outlook calendar everything work but now for some reason it stopped working its saying it has done it but when you open outlook there noting there, i really want it to go on my SharePoint calendar so all my phones get updated

[@vfxmastersthailand9330]

i got it to work oooooohhhhhhh lol