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.
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.
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 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
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
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?
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?
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?
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.
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.
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'.
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.
Thanks TH-cam 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 TH-cam just so I don't have to keep seeing something I didn't que repeat over and over again.... great job.
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 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!
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 😀
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?
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?
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
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.
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.
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.
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 ?
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
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...
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.
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
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
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.
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
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.
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?
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?
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.
@@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)
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.
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
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 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.
@@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.
@@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.
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
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.
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.
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.
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
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.
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?
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. :)
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?
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
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?
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.
@@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.
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?
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.
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:.
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
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 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!!
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.
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.
very helpful. this is the kind of tutorial that i admire ever.thank
Glad it was helpful!
@@599CD WELCOME I LEARN SOMETHINGS FROM YOUR TUTORIAL
Love your Videos, Really helpful and easy to understand what you are saying in plain English!
Thanks
Excellent video, Richard! Very well organized with a clear path from A --> B.
Glad you enjoyed it!
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.
Good tip. I've never tried that before. Thanks for sharing.
@@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
Thank you for sharing 👍🏾
Respect ✊🏾 and God bless you ❤️🤗
I appreciate it
Great lesson. Amazing. Very helpful.
Thanks
Love it you solved my big problem of security ❤
Watch for tomorrow's video!
I like all your access videos.. great 👍
Excellent Video! Very easy to follow and replicate.
Glad it was helpful!
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
You are absolutely 100% correct. Thanks for letting people know in case I forgot to mention that in the video.
@@599CD just saw John Colby's comment. my confusion cleared.
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?
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?
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?
Create a new blank 32-bit database. Import the 64-bit objects. Unless you have 64-bit specific VBA code, it should work fine.
Amazing. Very helpful. I was trying to improve performance and this just fixed my issue. You nailed it!
Glad it helped!
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?
Each user gets their OWN local copy of the front-end ACCDE file.
Is there a way to connect to the back end if your at another location via internet?
Sure: 599cd.com/AO
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?
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.com/QQ
Dear sir, you recommend in video to avoid aggregate queries and dlookup functions in splitted database, then what are the alternatives of these.
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.
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'.
Compile: 599cd.com/Compile
Thanks. Are users able to play with my backend in the shared folder?
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.
Thanks TH-cam 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 TH-cam just so I don't have to keep seeing something I didn't que repeat over and over again.... great job.
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?
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.
@@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!
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 😀
Can anyone help if i want to place the shared backend tables file online? like on GDrive or Dropbox etc. with thanks
NEVER do this! See Access Online: 599cd.com/AO
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?
Yep. That will at least show you where the problem is.
Hi, Sir I am from India.
Welcome! I'm from Florida. :)
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?
You can relink them with the Linked Table Manager. I cover that in the Extended Cut (Members Video).
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
If you're more concerned with user-level security, you need this: 599cd.com/Security
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.com/QQ
@@599CD Excellent
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.
I wouldn't use Access over WiFi. Wired LAN only.
Thanks Richard.
Just if you can help to resolve the error: 3045 of the split databae, "file already in use".
599cd.com/TFIX
Excellent presentation ...Quality learning material ..thank you
Is it best to Edit the Relationships (one-to-many, etc.) in the data database, or the front end database?
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.
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 ?
You've likely got an error in your VBA code somewhere. 599cd.com/Compile your database. Run down the 599cd.com/Troubleshooter
Thanks for your video!. Is it possible to host the be and the fe on ms teams or OneDrive for instance?
Is it possible to join the be and fe in the future if necessary ?
Yep
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
Each Access database file can only be 2 GB in size. There's no limitation that I'm aware of when EXPORTING data.
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...
Don't import images into Access. See 599cd.com/Images
Hi Richard, how do you combine the front end and backend into one database again?
Just import all of the objects from one DB into the other. 599cd.com/Import
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.
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
@@599CD thanks so much for your prompt reply
Thank you for a great explanation, but it did not explain how to distribute the powers
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
@@599CD Does this method work with OneDrive?
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.
I tried this, but get 'subscript out of range' as an error. compact and repair didn't solve it. What could be the problem?
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
Oh, and post questions like this in the Forum on my web site... I check that much more frequently than TH-cam. You're a member... get on there! :)
Haha, i posted the same question two years ago and couldn’t solve it then, hopefully may ‘work-around’ is fine too
Thanks a lot
Most welcome
If you have 10 Users Can we make 2 BE database, First BE 5 Users and the Second BE 5 Users?
Why would you do that? You're splitting your DATA up.
@@599CD Because I noticed if the number of users is many. The performance of the database is heavy
Hi! thanks for the good video, please can you show how to install and connect acces db in local server
Please clarify.
@@599CD I mean to install window sever and connect the access db to control different computers with access forms
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.
how did you do it can you share with me , thank you
Share what?
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?
Sure. You can place the files anywhere you like. As long as you can link to them, you can use them.
Thanks for the reply! Best wishes
@@599CD HI great video. Is there any way to “hide” the backend file ,so the users can not delete it
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?
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.
@@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)
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.
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
@@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!
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).
Create an archive table in a separate ACCDB file. I cover that in the extended cut for Don't Delete Data: 599cd.com/DontDelete
@@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.
@@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.
@@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.
@@599CD Thanks for your feedback.
👍💐
:)
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 ☹️
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
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
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.
Your videos are incredibly helpful and have saved me COUNTLESS hours on the job. Thank you so much!
You are so welcome!
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.
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.
Thankyou!!! I have searching for this solution for soooooo long i coundnt remember!!
You sure get my problem solved! Thank you!
Glad it helped!
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
As I mention in the video, you can't rely on global relationships in a split database.
Awesome. This is great.
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.
Users must have read/write authorization to the backend file.
What he said ^^^
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?
Do you have the databases (front and back ends) set up in TRUSTED locations? You need to set that up on each workstation.
Thank you Richard for sharing your knowledge. You're the GREATEST INSTRUCTOR on youtube!
Wow, thanks!
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?
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. :)
@@599CD thanks for the helpful and rapid reply. Your Tech Help videos have opened up my impression of Access’s capability.
after split front-back end, i insert new table in back end but front end don't link. How to link ??
External Data > New Data Source > From Database > Access. You have to manually link to any new tables you add.
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?
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
@@599CD Okay thank you very much! I'll give that a try.
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?
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.
@@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.
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?
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.
64-Bit Access: 599cd.com/64bit
Thanks, Great explained
We want to explain how to create a server like the one you use
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:.
Is it possible for us to store the Back-End into a cloud server if we don't have a shared folder?
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
See this: 599cd.com/AConWEB
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
Compact / Compile
599cd.com/Compact
@@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!!
Hi.we Can't see your screen. All is black
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.
@@599CD yes. You are right. The problem from my side. Thanks