LArge Table Split

[villaw]

Hi all

I have an ms access table which is the backbone of a an office invoicer/ management database and has grown quite a bit ie 50mb (the table alone). Is there any chance to split this table in two smaller ones or shall I just export it into an sql database and be done with it.

My mdb is already split in front and backend.

thanks

 

[gemma-the-husky]

50mb is tiny - dont worry

try compact and rerpair though, anyway - that will probably shrink it

 

[villaw]

thank you very much for your prompt reply.

Yes 50mb is not a lot (remember I am referring to the table in question not the mdb).
I do regular compact/repair exercises but I have noticed that the bigger the table gets, the more frequent the corruptions (geometrically), hence my request.

Obviously i do not want to upsize to sql, primarily due to lack of portability but I would certainly feel a lot more comfortable in the long run if I knew I could split it in two and let it grow over the years.

 

[DCrake]

I know you said you split it front end back end, but have you deployed the front end to uses? as opposed to all users opening the same front end on a shared drive?

 

[villaw]

each user has his own front end installed on him desktop which in turn links to one backend. A true client server environment but I do sense limitations on the backend due to size. I have considered slipping the db into two but one table is the real backbone of it all and covers 2/3 of the whole db size.

 

[FoFa]

We have had similar issues (although I would NOT consider 50MB an issue).
What we have done is created an archive process that archives data to a SQL table (or in our case tables) and have an archive solution (separate access application called from the first one) that handles lookup and reporting against the SQL tables. We have determined there is a certain amount of data to keep in the active system, but after some time frame we can then archive it. In our case they run an archive process once a month.

 

[vbaInet]

each user has his own front end installed on him desktop which in turn links to one backend.

Could you please explain this bit further. How exactly did you perform this function? Do you run the DB on a WAN or LAN? Do you ever use a Wireless connection?

 

[villaw]

thank you for your time
this is a LAN connection. Each desktop user has the front end in his drive which in turn accesses the back end db via linked table. ie one front end per user locally and one back end for everyone on the file server.

Probably I am overly cautious but this is an accounting application and corruptions are definitely not a choice anyone can handle.

Thank FoFa for the insight.

 

[vbaInet]

Just checking again. Are you using UNC paths to link to your BE? What is the speed of your connection?

 

[SOS]

also, check to make sure your network interface cards (NICs) are all working properly. A bad one (even on the server) can corrupt a database even if all else is well with the database itself.

 

[villaw]

yes I am using UNC paths and the connection is a hardware 100mb even though I have never checked the real bandwidth.

I see where this is going and I will pay a lot more attention to the network. Never realised that this is so critical in this scenario.