Split database

[LizJohnson]

I have a database that is becoming extremely large and I want to break out each year into a separate database. I am doing this to help productivity in that it takes quite a long time to open the database as it is right now.

I was going to copy the database and rename it a specific year and then eliminate the data that doesn't match that year. I would be copying the same main database for each year.

The problem that I have is that this is a split database. I did copy the main database into a separate folder but it looks like the database is still split and attached to the main database. I'm worried that if I start deleting lines, that it will delete it out of the main database which is not what I want to do.

How can I copy and adjust this database so that while I'm doing this I don't loose any of the information in the main database until I can get all of the main database separated? Can I "un" split the database, make the changes and then re-split the database?

 

[Minty]

Out of interest how big is big ?

If your database is split then your task is straight forwards, simply take a copy of your backend database, leave everything in it as is. This is now your archive / old data.

In your current "live" backend database cull the records you no longer want.

A word of warning - Unless you have severe problems with size, this isn't really deemed best practice - it's more normal to set an archive flag or archive date into your main records and simply not bring them into your day to day forms. That way when you want to compare this years data vs. three years ago you aren't leaping through hoops.

 

[CJ_London]

I did copy the main database into a separate folder but it looks like the database is still split and attached to the main database. I'm worried that if I start deleting lines, that it will delete it out of the main database which is not what I want to do.

this doesn't make sense - if you want to make the main db smaller, then you need to delete records from it.

Have you tried compacting the backend before going down this route?

Also are your tables properly indexed?

 

[LizJohnson]

Thank you both for your response.

I can't delete the information as it is tax information that we need to keep.

As to comparing years, we don't do that. We only run reports for the current year.

My problem was that I copied the front-end database, not the back end.

This also then brings up another question.

If I do just copy the back-end, is there a way to copy all the forms and queries into a new database or would I have to re-produce them entirely?

 

[plog]

I have a database that is becoming extremely large

Let's put a number on that before we are certain that's the issue. File size in MB?

 

[CJ_London]

If I do just copy the back-end, is there a way to copy all the forms and queries into a new database or would I have to re-produce them entirely?

just copy the front end and use the linked table manager in the copy to link to your copy backend instead of your main backend.

I agree with Minty - you are leaving yourself open to potential problems if you do not take care in what you are doing

 

[LizJohnson]

The back end is over 12 MB and the front end is over 5.5 MB.

It was mentioned about compacting the back end. I've never done this. What does it involve and do you loose any of your data?

Thanks again for all the help.

 

[CJ_London]

12mb is nothing - you have room for 166 of those within the normal limits of Access.

Compacting - on the ribbon, click on database tools, then select compact and repair - will take a couple of seconds. Do this in the backend, it will compact the db you are in.

No risk to loss of data, but if you are not confident, take copy of the backend first

 

[Minty]

Do you have separate copies of the front ends on each user's machine or are they sharing a common front end?

If you are sharing a networked front end that could be causing you some issues, each user should have their own local front end.

 

[LizJohnson]

They each have a copy of the front end. The problem is that it takes a long time (3-5 minutes) to upload the database and files that they need. I'm trying to help the cut this time down.

 

[Minty]

Why are they uploading it? Unless you have made changes to the front end their copy should be static?

If you mean 3-5 minutes to open then I would think you have some network issues or are trying to bring every record in to the opening form?

 

[The_Doc_Man]

There is something here that hasn't been revealed yet.

How fast is the network? A 3-minute database OPEN time is abysmal and says there must be something going on behind the scenes that is your REAL time-eater.

In a true split FE/BE database, the ONLY THING Access ever does with the BE on opening the database is to verify that all of the linked tables are still linked and still have the expected structure. It doesn't (normally) try to do anything else.

Therefore, the first thing I would do is try to trace down EVERY ACTION taken by your Default Opening Form in its Form_Open, Form_Load, and Form_Current event routines. Branch out from there as required. Those three VBA code segments (if you have them) will run every time you open the database. There is where your action occurs.

The only other possible solution involves that your users aren't running the private copy of the FE file but are sharing the public copy, which if so would open you up to lock contention. If this is happening, put a stop to it NOW. But I actually believe this to not be the case because you would see more errors than you would know what to do with from all of the "File Locked" issues that would crop up.

 

[LizJohnson]

By uploading it, I do mean opening the Access database and subsequent tables/queries.

I work for KY Department of revenue so we have to use specific drives for our files.

 

[The_Doc_Man]

Then clarify, please, and also understand that I am asking questions based on what I think you said but I have no confidence that I know what you meant. There some of the difficulty is in communicating the configuration and location of the database files.

Do users have a private copy of the FE file on their workstations? Or are you talking about having private folders on the same server and loading everything remotely?

My comment stands about what a database does with a shared BE file. Access itself does not do anything other than verify that BE linked table schemas correspond to the links it has copied into the FE. If anything else is being done, it is being done by a default Opening Form.

HOWEVER, it would also help to know what kind of network we are talking about. By that I mean, Ethernet? Token Ring? Fire-Wire? USB-net? and speeds in the MHz or GHz range? Things like that.

The last time I had a problem with something that slow, it was because we were stuck with a shared file structure five states away and though our backbone was 100 Mbit, we were sharing with literally 1000's of users, so we considered ourselves blessed to get 10 Kbit service during afternoon shift. (We solved the problem by moving the BE to a local server where we had true 100 Mbit and only 100's of users. At least 10 times better, right?)

 

[HiTechCoach]

By uploading it, I do mean opening the Access database and subsequent tables/queries.

I work for KY Department of revenue so we have to use specific drives for our files.

If KY is like OK and other states that are moving to the "Cloud" then your files are on a remote server.

If that is correct then I have some bad news. It will never work fast until you get a fast WAN/internet connection. AN Access database, not split or split with an access back end, just can't be run reliable over WAN.

See: Using a wan with ms-access? How fast, how far?

 

[The_Doc_Man]

Interesting thought, coach. When I was still working, I never thought of what we did as the cloud because for the most part, we WERE the cloud. But the slow system I discussed was, indeed, pretty much "in the cloud."

LizJohnson - if this IS a cloud-based system, then you will not improve matters without improving network speed/stability. In this specific case, the idea you need to take to your boss is that if s/he wants faster response, get your data out of the clouds and put it closer to you. Muhammad doesn't always go to the mountain. Sometimes, you must bring the mountain to Muhammad.

 

[LizJohnson]

Thank you all for your help. Unfortunately, I do not know what type of server we use as I am not in that department. I am only a lowly accountant (). I do believe it is a remote connection, though.

 

[gemma-the-husky]

Can you "see" the back end - Is it access, or something else?
If you can see it, then how big is it?

In general terms there should not be any need to subdivide a database into multiple databases for different years.

 

[LizJohnson]

Yes, I actually created this database. As I stated in an earlier post, the back end is around 12 MB so far.

 

[HiTechCoach]

Yes, I actually created this database. As I stated in an earlier post, the back end is around 12 MB so far.

If the database file is on a remote server then a 12 meg file can easily take a few minutes to download and open.

If you open File Explorer. Go to Computer/This PC. Is the drive letter where the Access database is located under Network Locations?

If yes, do you have a local server?