How and Why to Split Your Microsoft Access Database - Linked Table, Front End, Back End, Benefits

[@TikkuDiaries]

Mr. Rost...Thanks for sharing so many videos and very minutely on each kind of complications in handling database, I really appreciate your efforts. Very helpful. I am sure, I have seen many videos in details but you are the best.

[@yargamesh]

Your videos are incredibly helpful and have saved me COUNTLESS hours on the job. Thank you so much!

[@599CD]

You are so welcome!

[@emilythechef]

Great video, not only do you teach, but you explain where we might have questions about what you are teaching. Something is wrong with my database, possibly because it's pretty complex (for me!), and I get an error message, but I'm brushing up on split databases to give me the confidence to manually split it. Your video was quite helpful.

[@Jojosmith342]

Thank you Richard for sharing your knowledge. You're the GREATEST INSTRUCTOR on youtube!

[@599CD]

Wow, thanks!

[@angelicagalicia2395]

Amazing. Very helpful. I was trying to improve performance and this just fixed my issue. You nailed it!

[@599CD]

Glad it helped!

[@rollenlopez1154]

Thank you for this great content. My database has grown to some extent that makes me worry about future and this just extended it. I will join your members group. Again great work mate.

[@599CD]

Thanks. I appreciate the kind words And if you ever outgrow Access, you can always up-scale your tables to SQL Server and still keep your Access front-end. Videos on that are coming out soon.

[@vikkishowe-gaither4349]

Love your Videos, Really helpful and easy to understand what you are saying in plain English!

[@599CD]

Thanks

[@tracy8522]

Thankyou!!! I have searching for this solution for soooooo long i coundnt remember!! You sure get my problem solved! Thank you!

[@599CD]

Glad it helped!

[@samuelakuche990]

very helpful. this is the kind of tutorial that i admire ever.thank

[@599CD]

Glad it was helpful!

[@samuelakuche990]

@@599CD WELCOME I LEARN SOMETHINGS FROM YOUR TUTORIAL

[@ZacCoventry]

Excellent video, Richard! Very well organized with a clear path from A --> B.

[@599CD]

Glad you enjoyed it!

[@mengmakara9365]

Great lesson. Amazing. Very helpful.

[@599CD]

Thanks

[@shamechpet]

I like all your access videos.. great 👍

[@simonmaersk]

A good way to to host your back end is to attach some kind of USB storage device straight to your router's USB port. Go into the router settings menu and set up the storage device to be shared on the network (or open ports so it can be accessed from anywhere). Store your database back end straight on the attached USB storage and point your front end to it. That way, you don't have to rely on a host computer to be turned on when you wanna access the database, since your router will be on at all times anyways.

[@599CD]

Good tip. I've never tried that before. Thanks for sharing.

[@simonmaersk]

@@599CD You're welcome! I'd love to hear your opinion if you test it. I've done it and it works fine, but I haven't tested it with many users. I only got it to work on my home network and tested it with a couple of users

[@DrProfDoctor]

Excellent Video! Very easy to follow and replicate.

[@599CD]

Glad it was helpful!

[@chancludo1]

Amazing info, Thank you very much! So, both failes, the BE and ACCDE are created from the file on your computer and just saved in the server's folder?

[@599CD]

Each user gets their OWN local copy of the front-end ACCDE file.

[@Khizarsokhta]

Love it you solved my big problem of security ❤

[@599CD]

Watch for tomorrow's video!

[@MoreHace]

Thanks for your video!. Is it possible to host the be and the fe on ms teams or OneDrive for instance?

[@melakumengeshagenet3031]

Thank you for sharing 👍🏾 Respect ✊🏾 and God bless you ❤️🤗

[@599CD]

I appreciate it

[@fdnyfish]

Is there a way to connect to the back end if your at another location via internet?

[@599CD]

Sure: 599cd.com/AO

[@WastedElephant]

Awesome. This is great.

[@jwcolby54]

Referential integrity absolutely does work in linked tables. It does not work between tables in separate BEs. Use different FEs for security rather than split (different) BEs

[@599CD]

You are absolutely 100% correct. Thanks for letting people know in case I forgot to mention that in the video.

[@jossurbab]

@@599CD just saw John Colby's comment. my confusion cleared.

[@GregRoberts-r6l]

Thanks for the video. After splitting the DB, I've opened the front-end file from another machine on the shared network, everything looks linked properly, the forms and tables are intact, but after completing a form, the on click event property produces an error and the record doesn't get saved. Other event properties seem to have similar issue. When I run this same form from the original machine and file, it works perfectly. Any troubleshooting ideas?

[@mauricemosseri4959]

Hi, this is great video, worked beautifully for me. I have a question, If I move the database to a different server, can I change the link tables to the new server? Or do I need to use the backup and split the database again on the new server?

[@599CD]

You can relink them with the Linked Table Manager. I cover that in the Extended Cut (Members Video).

[@stanTrX]

Thanks. Are users able to play with my backend in the shared folder?

[@599CD]

Yes, that's one of the security holes - accessing and editing records in the database requires full access to the backend database file. There are tricks you can use, such as hiding the file, but there is no foolproof way to prevent others from obtaining it. If security is a concern, you should consider using SQL Server instead.

[@jim27105]

Thanks for the great videos. I managed to split my database and now some users can't open the front end bcuz I created it on a 32 bit machine and they have 64 bit machines. Any chance you discuss this in one of your tutorials?

[@599CD]

The bit-ness of the BACK END file shouldn't matter, however you're going to need to make separate front-ends for 32-bit and 64-bit users OR, preferably, get everyone on the same version.

[@599CD]

64-Bit Access: 599cd.com/64bit

[@kheyapar8199]

Thanks

[@glennandkellyp]

Great video, but now I can't open my database on 32-bit version computers, because it was created in 64-bit. Is there a way to fix this?

[@599CD]

Create a new blank 32-bit database. Import the 64-bit objects. Unless you have 64-bit specific VBA code, it should work fine.

[@medhamdani38]

Thanks, Great explained We want to explain how to create a server like the one you use

[@599CD]

Any shared folder will do. In my office I don't actually run a "server." I just have a two Windows PCs with peer-to-peer networking. The important thing is that they both have a Z: drive which points to the same location. Z:\ on one PC is C:\Database on the other, but they both call it Z:.

[@lindarichardson976]

Thank you, Richard. I managed to create the back end file, but received this message when I tried to save the file as .accde: 'Microsoft Access was unable to create the .accde, .mde, or .ade file'.

[@599CD]

Compile: 599cd.com/Compile

[@HRazaJ]

Dear sir, you recommend in video to avoid aggregate queries and dlookup functions in splitted database, then what are the alternatives of these.

[@599CD]

It's not just any split database but anytime you are trying look up data from another table if you have a lot of records such as in a continuous form then you really want to try to build a query to join those together instead of using Dlookup. Aggregate queries aren't that bad If they're used correctly.

[@karagidesa]

Hi, thank you for the video. I’d very much appreciate if you could inform me of how can I split the database in 3 databases, so as each of the 3 salespersons would see and use only the respective customer account records. Thanks again

[@599CD]

If you're more concerned with user-level security, you need this: 599cd.com/Security

[@restrepojorgea]

Is it possible to join the be and fe in the future if necessary ?

[@599CD]

Yep

[@davidhudson2732]

At around 6.25, you talk about getting past the 2GB file size limit by stringing together tables that have a 2GB max file size. How do you do that? I would like to put our agency into an Access database (EHR) for all of our clients. However, we have a ton of old information that needs to be scanned and stored in the database. I am sure that we going to easily go over 2GB and would like to find a way to store all their past records in our new EHR system.

[@599CD]

599cd.com/QQ

[@davidhudson2732]

@@599CD Excellent

[@petervansoestdevrolijkeboe3320]

I kept getting ‘Subscript out of range’ when trying to split the database. Then I followed the tutorial of another video and that seems to work: 1) I made a copy of the database, 2) in 1 copy I removed everything but the tables (backend), 3)in the other copy I removed all the tables, saving the forms, queries, reports etc. (Front end), 5) I opend the front end table and linked it through “External data/from database/access…” with the backend table. It seems to work ok, but am I now at risk of any failures or errors in the future by ‘ surpassing’ the error message I got when trying to use the access splitting tool?

[@diggee172]

Great lesson. I have a tiny Access database which I am maintaining for a small church I want to split the database and put the backend some place but the problem is where to put it. The maintainers of the use their own copies of Access and we have no network or server. It's what we used to call "sneaker net". Is is possible to put the backend on Onedrive or a google drive? There is extremely low activity in the database daily, but we do have problems sharing with essentially multiple copies of the database now. I have seen some solutions online but we don't have any money for this- it's all volunteer work. Thanks.

[@599CD]

Don't use something like Google Drive to share the database AT THE SAME TIME. If you want to copy the ACCDB file up there when you're done with it and let someone else download it and work on it, that's OK. But if you split it and SHARE it that way, you will corrupt it. See: 599cd.com/AccessOnline

[@diggee172]

@@599CD thanks so much for your prompt reply

[@alexmarchand5446]

hi Rich, i just attempted to split; but now it seems like i have lost all access to my VBA code on the front end file; any thoughts? i have many buttons setup with VBA code and now all of these buttons do nothing; what should i do?

[@599CD]

Did you SPLIT or did you create an ACCDE file? That's the only way you can "lose" your VBA code. Find your original ACCDB file.

[@Magic_Mushr00m]

@@599CD Hi Rich, thanks for your helpful lessons! You taught me a lot ! But now I have the same issue, after splitting the database and made a accde file for users all my buttons stopped working. I did it in vba. Accdb file works fine. Do you have any ideas, why it happened? Thanks again for your videos!

[@LadyCrockettCrafts]

I have the same issue. My buttons on the front end do nothing now. Further looking into it, it is just my button that is on a first form and the button is supposed to open a 2nd form. The other buttons that open Reports work fine 🤔 Update- I had some empty VBA commands, I deleted them and then re-saved as accde and now it seems to be working. Maybe this will help someone else 😀

[@rshaddock]

Is it best to Edit the Relationships (one-to-many, etc.) in the data database, or the front end database?

[@599CD]

Good question. You can ONLY enforce referential integrity in the database file that the tables exist in. You can create relationships to linked tables, but they almost serve no purpose. That's one of the reasons I don't bother with global relationships in split databases.

[@lindarichardson976]

Sorry - I have just found your video: How and Why You Should Compile Your Microsoft Access Database, and if Necessary, Decompile it! Would this solve the problem?

[@599CD]

Yep. That will at least show you where the problem is.

[@JessikaNelson]

For some reason when we do this it gives us the error that it's not a valid path. When I open it on my computer there are no issues and the backend is exactly where the path says and everyone has access to that server but is unable to open the front end? I followed your instructions exactly. Any idea why?

[@599CD]

Without seeing what you've got... nope. The path has to be EXACT everywhere. So if it's Z:\Database on one computer, it has to be that on ALL of them. If it's //SERVER/Database on one, you get it.

[@599CD]

599cd.com/QQ

[@prochefpos]

Question : I split my access database and put it in my shared folder on a computer in my home network for use via wifi with other frontends on different machines. I have a vb6 application that uses this database. All apps sitting on my windows 11 computers have become terribly slow but on my windows 7 computer the speed hasn't dropped much. Is there a reason for this ? thanks.

[@599CD]

I wouldn't use Access over WiFi. Wired LAN only.

[@aburghul]

Thanks Richard. Just if you can help to resolve the error: 3045 of the split databae, "file already in use".

[@599CD]

599cd.com/TFIX

[@jossurbab]

As I found the speed of accessing the back end file in the server is slower from my PC, I plan to keep the original file as such in the server with all tables, forms etc., with a shortcut to my own use(Admin) and distribute front end copies to others after linking it with the said original file tables. What could go wrong in this situation?

[@599CD]

It should work faster with the front-end on your PC, but whatever works for you. There's no real problem with running your front-end from the server. It just usually works slower.

[@jossurbab]

@@599CD Thank you for your precious time. But sorry, after reading my question again, I found that its poor writing spoiled your answer. What I meant was, the original file(1) is unsplit in server, I am having it's short cut on my desktop. Same time, I made a front end from scratch, by copying forms etc., in a new empty access file and linking it to the unsplit file (1)in the server and distributing it to others for their use. I wanted to know the pros, if any, and cons of such an approach.

[@omararboleda3434]

Question: how do you deal with a Excel file that is linked to the table? The issue I am running is when another user open the file, it is getting a message that the file is opened. It is worth noting the file is on shared folder in OneDrive. Please help

[@599CD]

I don't recommend working off of shared Excel files. That's just corruption waiting to happen. Import the data, use it, and write it back out if you need to. Or better yet, use a shared Access database.

[@xSavageGamerx]

Hi Richard, how do you combine the front end and backend into one database again?

[@599CD]

Just import all of the objects from one DB into the other. 599cd.com/Import

[@jstan3076]

I am splitting it using the accde file to protect my VBA etc. Is that OK ? I then use the original accdb to add or modify table, form , then convert it to accde to replace the istimg accde file. Funny that there is no more + sign show on table, but seem OK. Can you help or do a video

[@599CD]

As I mention in the video, you can't rely on global relationships in a split database.

[@Jojosmith342]

hi Richard, I followed your instructions and splitted the DB. I emailed the ACCDE copy to users which I installed it on their desktop, the path points to the BE located in the shared folder on network (RO folder). When users open the ACCDE file from their desktop, the message says "Read-Only" with a button Save As. I tried to save as but users can't enter data in. I even tried another way by changing properties of the form to data entry = yes, Records lock = no locks. It does not help. Could you please kindly help? thanks a lot Richard.

[@gregoryjeffries8475]

Users must have read/write authorization to the backend file.

[@599CD]

What he said ^^^

[@amjithat]

Hello sir, Thanks for the great tutorial, Was trying to split my access data base but there is error popping up in last stage as " engine could not lock table becuase it is already in use by another person". i copied my database onto my computer and attempted this still it shows the same error. Could you help to fix this

[@599CD]

Compact / Compile

[@599CD]

599cd.com/Compact

[@cs_dna]

@@599CD Hello sir, I ran into the same problem. I split the database, keep the backend in our shared network folder, ask each user to copy the front end to their desktop. Still, it gives the "could not lock table becuase it is already in use by another person". All the users have full permission to that folder, the tables are linked correctly. I tried Compact like you said, but still no hope. Please help sir!!

[@karamahadika6081]

how did you do it can you share with me , thank you

[@599CD]

Share what?

[@SixGraphics]

Hi! thanks for the good video, please can you show how to install and connect acces db in local server

[@599CD]

Please clarify.

[@SixGraphics]

@@599CD I mean to install window sever and connect the access db to control different computers with access forms

[@599CD]

Well, I don't have lessons in Windows Server. But once you figure that out, you just set up a shared network drive and then split the database like I show you in this video.

[@arturoramirez712]

What is the 2 GB limit when exporting data? I stored an access db on the drive called One Drive. I tried to export a text file and got that 2 GB limit error message. What I did was just cut the export file in two. One file first half of the year, the other file the second half of the year. It worked. Each file is only around 35,000 kb, pretty small. What is the 2 GB limit related to exporting a file from access? Thank you

[@599CD]

Each Access database file can only be 2 GB in size. There's no limitation that I'm aware of when EXPORTING data.

[@stevenvasquez7726]

Can the back end be stored on a different network folder than the front end after it is split? Example: our data drive is the A Drive and our User drive is the B drive (permissions are on the A drive, not the B drive) can I store my Back End on the data drive A that the End Users won't have permissions on, but I, the admin, does have permissions? While the front end form is on the B drive?

[@599CD]

Sure. You can place the files anywhere you like. As long as you can link to them, you can use them.

[@syrkyls]

Thanks for the reply! Best wishes

[@markostzortz8064]

@@599CD HI great video. Is there any way to “hide” the backend file ,so the users can not delete it

[@NFAtoys]

What happens if your future changes include new tables? Can you split out the new tables, or does the entire back end have to be changed?

[@599CD]

That's up to you. You could add them to the back-end manually, or create a 2nd back-end and link the front-end to it. You COULD create the tables programmatically with VBA code if you know how. :)

[@NFAtoys]

@@599CD thanks for the helpful and rapid reply. Your Tech Help videos have opened up my impression of Access’s capability.

[@medhamdani38]

Thank you for a great explanation, but it did not explain how to distribute the powers

[@599CD]

What do you mean by "distribute the powers?" Do you mean distribute the new front-end database files? That's up to you. You can email it out. You can have people download it from a shared folder, or someplace like Google Drive. Or, you can use my Access Updater: 599cd.com/updater

[@medhamdani38]

@@599CD Does this method work with OneDrive?

[@599CD]

Nope. You could use a VPN, but I strongly recommend against it. Unless you're using a database SERVER, just sharing a drive letter over a network that's not a LAN tends to cause corruption in Access databases. SharePoint, SQL Server are the best options.

[@eslamessam2844]

Hello, when I try to save an ACCDE file an error message pop up "Microsoft access was unable to create the .accde file", noting that I am saving it and the Accdb files on my local computer not a server network, would you help me please ?

[@599CD]

You've likely got an error in your VBA code somewhere. 599cd.com/Compile your database. Run down the 599cd.com/Troubleshooter

[@petervansoestdevrolijkeboe3320]

I tried this, but get 'subscript out of range' as an error. compact and repair didn't solve it. What could be the problem?

[@599CD]

Probably an error in your VBA code. Compile the database. VBA Editor > Debug > Compile. That will show you where the problem is. Usually. If not, run down the checklist: 599cd.com/Trouble

[@599CD]

Oh, and post questions like this in the Forum on my web site... I check that much more frequently than KZbin. You're a member... get on there! :)

[@petervansoestdevrolijkeboe3320]

Haha, i posted the same question two years ago and couldn’t solve it then, hopefully may ‘work-around’ is fine too

[@ssdusd]

If you have 10 Users Can we make 2 BE database, First BE 5 Users and the Second BE 5 Users?

[@599CD]

Why would you do that? You're splitting your DATA up.

[@ssdusd]

@@599CD Because I noticed if the number of users is many. The performance of the database is heavy

[@charlesdawnbeltran9603]

Is it possible for us to store the Back-End into a cloud server if we don't have a shared folder?

[@599CD]

Not really. Unless your connection to your cloud server is LIGHTNING FAST and acts like a regular network share or drive letter, I don't recommend it. You can use SQL Server or SharePoint to host your data: 599cd.com/Sharepoint

[@599CD]

See this: 599cd.com/AConWEB

[@md.parvez8715]

When importing data with image from excel sheet to ms access but Don't show image or image link into ms access field. Please solve this problem kindly... I am from Bangladesh. I continue watching your tutorial video for ms access development..... Please help if possible...

[@599CD]

Don't import images into Access. See 599cd.com/Images

[@ghulamhaider3434]

Can anyone help if i want to place the shared backend tables file online? like on GDrive or Dropbox etc. with thanks

[@599CD]

NEVER do this! See Access Online: 599cd.com/AO

[@anupmukherjee8722]

Hi, Sir I am from India.

[@599CD]

Welcome! I'm from Florida. :)

[@dottiearnold4988]

I have tried 3 times to save as accde file but keep getting an error message. It says "This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc.). compiling Is there something I did wrong or is there another way to give a front end copy to users?

[@599CD]

Your database won't convert unless it can be successfully compiled. MDE is the old format. Are you using Access 2003 or earlier? ACCDE is the new format. I'd suggest running down my troubleshooter: 599cd.com/Trouble

[@dottiearnold4988]

@@599CD Okay thank you very much! I'll give that a try.

[@RasheedSyd]

Thank you for your efforts for helping us. I have got internship where I am supposed to migrate 1997 access database to latest version to use it on windows 10. Since they have not updated themselves from 1997 they are forced to use windox xp Operating system, My job is to update there access database to latest version by converting the database. Now the problem is I was able to convert the database from .mdb to .accdb but the database is combined with VBA coding and i am getting errors. Dont know what to do. If you can guide me through I will be grateful to you and I will also pledge to make donation to your channel. Thanks in tons.

[@599CD]

Importing the data shouldn't be much of a problem. As far as your VB code goes, that could be something simple or something crazy complex. Without looking at the database, it's impossible to tell. As far as guiding you thru the whole upgrade, that's not something that I do. If you have a specific error message you'd like to ask about, feel free to submit it via 599cd.com/TH

[@RasheedSyd]

@@599CD Thanks for the response, that was real quick. I will you use the link to post the specific errors that I am getting. Thank you!

[@NancyTroutman]

Now my users are getting warning messages "a potential security concern has been identified" whenever they open the database. Getting "stupid' warning messages teaches them to ignore actual ones. Is there any way to fix this?

[@599CD]

Do you have the databases (front and back ends) set up in TRUSTED locations? You need to set that up on each workstation.

[@qw13y6f]

after split front-back end, i insert new table in back end but front end don't link. How to link ??

[@599CD]

External Data > New Data Source > From Database > Access. You have to manually link to any new tables you add.

[@Trancer006]

i hope somebody would see this and help me out: I'll be building Access with lots of Excel files and the data in these spreadsheets will be updated frequently, so i want to link each spreadsheet to Access rather than import. So what's faster for running queries? Linking or importing spreadsheets?

[@599CD]

Good question. I actually have a video on this coming out soon. What I recommend is LINK to them to get the data, but IMPORT that data into local temporary tables. Then you can run whatever queries you need on that data. It will run much faster. So... 1. Link to the Excel sheet 2. Import the Excel data into a temp table in Access 3. Run queries/reports on temp table Operations on those linked Excel tables will run slower than on native tables.

[@Trancer006]

@@599CD Thank you for the tip! Is there an automated way to import the linked spreadsheets? Would that be VBA territory? (im a noob when it comes to VBA lol)

[@abdulhamidalhaddadi2255]

Thanks a lot

[@599CD]

Most welcome

[@zohaibakhtar1969]

Hi.we Can't see your screen. All is black

[@599CD]

It has to be on your end. Over 400 people have viewed this video so far (many all the way to the end) and yours is the only complaint.

[@zohaibakhtar1969]

@@599CD yes. You are right. The problem from my side. Thanks

[@elwyndude]

The database has become bloated with 5 years worth of data. Its already split. How would you make a copy that include previous years data and the other version has current year only (then each year make a new copy).

[@599CD]

Create an archive table in a separate ACCDB file. I cover that in the extended cut for Don't Delete Data: 599cd.com/DontDelete

[@elwyndude]

@@599CD Thanks - I have archived. The db (split) has 7 months of data in it, 20 tables, maybe 15K rows in 5 of the tables, compressed it and the db is approx 20mb in size. However, users are still experiencing slowness with about 1-2 mins between pulling up each record. The tables do have a primary key (autonumber) but other columns (such as date) that are used in searches do not have indexes (allowing duplicates). So I plan to add some indexes to these columns (allowing duplicates) in the hope to speed things up. The users connect to the backend db with excel... over the network. And I have a bad feeling this still will not help the situation as its the accessing over the network which is causing the slow response time. Any other ideas would be appreciated, but I am running out of options it seems.

[@599CD]

@@elwyndude you are correct. Linking to Excel is DREADFULLY slow. I'd suggest importing the Excel data whenever it's updated (daily routine?) and using that instead.

[@elwyndude]

@@599CD Yeah, thanks. They dont have Access on their machines hence the reason for the excel file.... Its not just one table but multiple joined tables that are used in a query... I suppose I could give them access to the query through Sharepoint.

[@elwyndude]

@@599CD Thanks for your feedback.

[@2009strar]

👍💐

[@599CD]

:)

[@cybersmoke8285]

Thanks KZbin I watched one or two vids from this guy now your broken algorithm pops it up after EVERY other video..... This poor guy got a thumbs down because of your algorithm KZbin just so I don't have to keep seeing something I didn't que repeat over and over again.... great job.

[@tjisnotthatstupid]

I tried this and it the “linked table” it set some dates as short text and wouldn’t let my change them…which caused tons of issues ☹️

[@tjisnotthatstupid]

Also, I made sure in the backend they were dates like they were supposed to be…they were. It was only in the linked tables in the front end