Split accdb - how many backends?

[Les Isaacs]

Hi All

We have a split A2010 accdb, and the backend is now 1.8Gb so I need to do something

If I put each of the 4 main tables in 4 separate backend accdbs, will the fact that the frontend then needs to link to 4 accdbs in some way impact on performance? Another possibility (given that 90% of our work only needs the recent data) is that I split the 4 main tables into 'archive' and 'current' (= recent) tables, with all the 'archive' tables in one accdb and all the 'current' tables in another accdb: but that would make it more difficult to run reports etc over the entire data

Putting the 4 main tables in 4 separate backend accdbs seems like the answer to me - but are there any other considerations to this

Thanks for any help.
Les

 

[pr2-eugin]

Your design of having only 4 table intrigues me ! How many writes and deletes happen in the table on a daily basis? Make sure you perform Compact & Repair everyday if you create and delete records ! It will free up unclaimed garbage memory.

You can have four tables in 4 different databases, and connect them to the front end. Performance might be a bit slower, to overcome this, you need to set up persistent connection with these four databases. There should be a lot of tutorials on how to set this up.

 

[namliam]

Have you tried compacting the database? Is it simply bloated? or is there actual 2 gig of data?

if 90% of your work is on current data I would definatly split the databases to current and archive, if only to speed up the current work.

You can "easily" use a UNION query to merge the two souces back together again.

not sure about considerations when splitting the DB, I have hardly noticed any difference when working in 1 db, FE / BE or FE / 3 BE

 

[Les Isaacs]

Hi Both

Although there are only 4 'main' tables, there are many others too

We do lots of writes/deletes every day, so I do regularly compact/repair the backend - otherwise we'd have been at 2Gb ages ago

Question: is a 400Mb backend likely to be noticeably quicker to use than a 1.8Gb backend, or is this a bit of an academic issue for the purists? I'm guessing I'll get some "try it and see" responses to this

I will look into persistent connection - hadn't heard of that

Thanks for your help
Les

 

[pr2-eugin]

I will look into persistent connection - hadn't heard of that

Here is a good explanation and solution to this topic - http://www.granite.ab.ca/access/performanceldblocking.htm

Good Luck !

 

[RainLover]

I think it is time to more on. You are going to have to upsize one day so I suggest that you do it now.

I recommend MySQL. It is available on 80 - 90% of servers around the world. It is free and of high quality.

 

[Les Isaacs]

Paul:
Thanks for that link: there's a bit for me to digest there
Rain:
Yep - I know we're heading that way - have been putting it off for ages, but perhaps as you say now's the time Our frontend is big and complex, so it's not going to be easy. Do you know of any particulaly good resources for help with accdb -> MySQL conversions?
Thanks
Les

 

[RainLover]

Les

The last time I did this was about 5 years ago, so I have forgotten more than I know know.

I think PHP myAdmin is the tool that you use.

I do remember that some fields do not work. once converted. Namely any field that is a Lookup. AutoNumber needs to be reset I think but not 100% sure.

At a guess you should be able to learn what to do and then implement it less than a day.

MySQL is a great program and is used for Web Development. I did this with MySQL, PHP and HTTP. It was a joint exercise of which I managed the Database. http://www.vudara.com

 

[The_Doc_Man]

The question when you first split FE/BE is the same as you will find when you split 1 BE to a mutli-BE situation - JOINs.

Remember that with Access, ALL database operations in a pure Access environment are done in your workstation (i.e. when you have no MySQL or other active DBMS on the BE). So everything comes through the network to your system first. The box holding the BE files is nothing more than a glorified file server, passive with respect to database actions. All the code is local. All its data components are remote.

Splitting a single BE into multiple BE when JOINS might exist between them can become a nightmare because you now are hitting your network extremely hard, even if all of your BE files happen to be in the same folder on the same server. I have found that each JOIN operation added to a multi-table JOIN query only makes it worse as you go along. I.e. it magnifies the inherent slowness of the network drastically. I won't say "geometrically" because that has a precise meaning that I don't thing applies here - but I believe "drastic" is a good word.

I'll add that if the FE/BE split has any number of remote network hops between FE and BE, adding more splits means that many more network connections you had better not lose, because if even ONE of them dies, they all go down the tubes. And if this split puts the multiple BEs on more than one file server - I shudder to think of the exposure to network hiccups.

I'm with the group that suggests it might be time for MySQL or some such similar beastie. Otherwise you'll have your network administrators breathing down your neck as you turn your network into a "notwork".

 

[Les Isaacs]

Rain / 'Doc_Man'
Thanks for your further replies: I get a sense of MySQL coming at us like a train, and it's a bit daunting
If I understand the comments about multiple backends correctly, the upshot seems to be that we should definitely NOT DO THIS: is that correct? Our setup is that we curretly have a single accdb BE on a fileserver and (at our busiest) 9 FEs on 9 PCs in use concurrently. Generally it all works OK, but:

1. sometimes it's a bit slow writing data
2. now we're approaching the 2Gb limit on the BE (the initial issue for this thread )
3. occasionally we get "the data has been changed by another user ..." issues

so it probably is time to move on
Thank again for the tips.
Les

 

[RainLover]

Les

It is time to move on, if your Back End is that large. This must be one hell of a database if 9 users can cause it to grow so large.

Things to look at.
Have you done a compact and repair.
Do you import tables/records and then delete them.

Although I have recommended MySQL, or if you prefer SQL Server. There are still others. I am now thinking that you are causing your database to bloat through incorrect activities. I have databases that are 10 Years old and still working fine. I work on one particular database that was written in Access 95. It would have 50 - 80 users, although not at the same time.

So my suggestion is to first look for a cause for database bloat. If nothing can be found that may be causing a problem then it time to upsize.

Sometimes it's a bit slow writing data
Now we're approaching the 2Gb limit on the BE (the initial issue for this thread

)
occasionally we get "the data has been changed by another user ..." issues

Q 1 This is most likely a size problem.
Q 2 Needs attention.
Q 3 This has nothing to do with Database size as far as I can see.

I hope all this helps. BTW a good book to have is MySQL for dummies. I started with one of those then got a couple more books from Amazon. These more advanced books teach you things that you did not realise was a problem.

If you can master them you would be looking good. But start with the dummies or idiots first.

 

[RainLover]

After yet some more reading. I am now concerned about the design of your Tables.

I would really like to look at BE with just a few rows of data. I would like the DB converted back to Access 2003. If not then I cannot read It. But don't let that stop you because there are others here to help.

One other thing you can do is to post a copy of a pic of the Relationships and Tables. Just try to show as much as possible.

 

[Galaxiom]

Are there large files or images in the data? If so you could manage them externally and just link to them from the records.

I have had Access tables with fifteen fields, many of them indexed and nearly ten million records before reaching the limit. It certainly isn't somewhere that is reached by users typing in forms.

 

[The_Doc_Man]

As to whether having multiple back-ends around is correct or incorrect is impossible to say without a more precise problem definition. However, it is fraught with peril because of the aforementioned issues of

(a) losing one connection kills the app; adding multiple connections to multiple files multiplies the odds of a connection failing.

(b) having multiple joins between multiple files will lead to a lot of overhead in table JOIN implementation. It's bad enough when all joining tables are in the same file. Synchronizing across file boundaries just makes it that much harder and that much less efficient.

From your comments, I am at a loss to believe that you have that much data that has to stay current. I know from a situation that involves some kind of regulatory rule, it is possible to have to keep lots of data for a long time, but I wonder if you really need to run reports covering that amount of data over the entire time span of your data gathering history. If so, it is time to find a solution that can hold that amount of data in a usable format that won't eat your system resources down to nil when you run your queries.

The first key to designing a system is to size it. Your size descriptions don't say "Access." They say "SQL-based" - though I neither suggest for nor against using MySQL specifically. If MySQL is available, use it. If something else would be available and consistent with company policy, use that instead. Your problem here is sheer data magnitude. When that is your problem, Access is perhaps not your answer. Something more SQL-ish will do the job.

Now there is some chance that your problem is merely a bad design that isn't well-normalized - but I won't accuse you of that. I bring it up because if your design IS normalized, then you are DEFINITELY reaching the point at which an up-convert is becoming more are more of a necessity, less and less of a mere possibility. Normalization can get back a lot of space, but if you are even close to a good, normalized structure then you are pretty much out of blood for that particular turnip. You can stop squeezing it now.

 

[RainLover]

Les
We have two other members agreeing that we should look further into the problem.

The Table design does appear to be the first place of call.

Let's see what data you can post for us to look at.