You are so welcome!

Glad it helped!

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.

Wow, thanks!

Glad it helped!

Thanks

Glad you enjoyed it!

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

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.

Glad it was helpful!

@@599CD WELCOME I LEARN SOMETHINGS FROM YOUR TUTORIAL

Sure: 599cd.com/AO

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

I appreciate it

Thanks

Watch for tomorrow's video!

Compile: 599cd.com/Compile

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

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.

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.

Glad it was helpful!

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:.

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.

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

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

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 😀

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

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

599cd.com/TFIX

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

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

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.

599cd.com/QQ

@@599CD Excellent

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

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

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

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

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

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.

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

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

What he said ^^^

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.

Yep

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

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 KZfaq. 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

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!!

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

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

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)

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

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.

Welcome! I'm from Florida. :)

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

Share what?

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

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.

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

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.

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!

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

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.

Most welcome

:)