Copying Large Access Database

[bkkmick]

Hi

Most days I take a backup copy of our MS Access database from our serviced office to my house via LogMeIn.

Quite often the transfer will not be successful as the connection will be dropped (the serviced office ADSL connection is crap).

The database is quite big (almost 1gb) and I run the MS compact database option before I transfer the file.

Apart from breaking the database into a number of smaller databases which will take quite a bit of re-writing of code (and I don't have the time to do that at the moment), does anyone know of a tool used to transfer large files remotely from one PC to another?

Thanks

Mick

[cclub75]

The database is quite big (almost 1gb) and I run the MS compact database option before I transfer the file.

I guess the MS compact function is not as efficient as a real file compression tool.

Have you tried to zip (or rar) the file ?

[bkkmick]

Thanks CClub. After compacting the database there's not much effect after zipping it. Pain in the arse really.

[chingching]

you need a download program that will allow "resume" so if the connection is broken it will pick up where it left off. Most have them.

A 1 GB db, maybe u should rewrite the code anyway !

[bkkmick]

Yea, chingching, re-write the code or archive old clients data. Just don't have the time at the moment. No problems caused in the office - just when I try and transfer the file. Do you happen to know of a download program that works remotely between two PC's?

I've tried installing Anyplace but it doesn't seem to want to work. Don't know if it's the fact that we don't have static IP addresses or that the firewall's keeping us apart. Urghhh...

[Guest Reimar]

Why not use WinRAR or StuffIT? Compress and split the file to, let say 50 MB, and copy the splitted archives!

And I haven't good experiences with LogMeIn, I use RAdmin instead and that works fine, FOR ME! You can also use a Server Program like EasyFileSharingWebServer and install that on your Office comp and download from your home what you need! The prog is cheap and easy to configure and you can use SSL as well plus Resume for broken downloads!

[bkkmick]

Thanks Reimar, I'll certainly take a look at those options.

[Kyosuken]

1GB access database ? OMG ! ihope you guys are storing only text inside (and no photos/docs or whatever) access databases are not meant to be that big, i think you should try the inner functions of access (the tool for performance comes to mind) and check if the databases is reliable, because i am pretty sure that at your company working with access must be a pain too. Would be good to try some optimisation scripts (on google search for optimise access db scripts) and run them at least one every two days at nights.

[steffi]

If it's only one gig why not just dump it a flash drive or cheap external usb hard drive.

quote name='Kyosuken' date='2007-05-24 13:46:18' post='1321400']

1GB access database ? OMG ! ihope you guys are storing only text inside (and no photos/docs or whatever) access databases are not meant to be that big, i think you should try the inner functions of access (the tool for performance comes to mind) and check if the databases is reliable, because i am pretty sure that at your company working with access must be a pain too. Would be good to try some optimisation scripts (on google search for optimise access db scripts) and run them at least one every two days at nights.

[Guest Reimar]

Depends on the kind of database in Access, it can be a pain in the a*s to "convert" to an other prog. And if it's a company database it's may much more difficult! A lot user will be able just to work with THIS database and it can be nearly impossible to change to a different system!

If I'm on place of bkkmick, i would setup an "server" on my office comp with Internet accessible remote connection! That will be the best and most efficient way to deal with the data. I use this kind of connection to run the importand backups by some of our costumers on my own office comps in my office via remote connection. I also can startup, control or shut down the computers in the Network of my costumers! All via remote! Transfer some data isn't any problem, up and down!

And one more thing, I save a lot of time if some of my costumers has problem to just sit down an "look" via remote what they doing and instruct them how to handle or solve the problems!

But that is my main business: Service for LAN and WAN include Hosting!

Edited May 24, 2007 by Reimar

[britmaveric]

I'd agree 1gb for access dBase is quite big - need switch to SQL. Access tends to corrupt bigger the dBase and performance degrades.

[bkkmick]

All comments taken to heart, thanks guys. Yes, the database is all text and no pictures.

I think that I'll have to make the time and put the effot into splitting the database. Currently it has all customer + static data tables + customer tracking info all in the one database. I have various processes running in the office to extract data from the web (customer amendments, new customers etc.) another to send emails from the BIG database to customers and the central back office system all written in VisualBasic 6. It will be a pain but a necessary evil.

All the best

Mick

[Simmo]

I've done migrations of similar homegrown access db's to SQL Server in the past. It can be a real headache depending on how the access database was setup (data normalisation etc, indexes etc). There's a migration assistant tool and plenty of docs on best procedures from the Microsoft download centre.

SQL Server will give you a ton more flexibilty on backups / load balancing etc / table locking etc.