Migration to another database from Access 2007

[Dan Roberts]

I have to review large tables of medical claims histories. The total row count is 20,000,000 and the data are downloaded from a sql database. I tried to divide the database into 5 Access databases - one for each year.

I was able to import the text files but each database is very fragile and each is 1.5GB or larger. I have to refresh the data in each database every two or three weeks and the loads often fail. This is not a data warehouse precisely but there are few changes to the data except for the re-loads.

Performance is OK - loading is the biggest problem. Making the Access databases smaller would be cumbersome. The databases must be encrypted and I am using PGP but that does not seem to be he problem.

This is a single-user database. What would be handy is an Access front end to a ODBC single user database. I do not want to spend my time or my client’s as a database administrator.

Any thoughts?

 

[tokoloshi]

Why don't you look at SQL Server?

You get a free copy that can cope with all of your current needs.

Access will be able to upsize to SQL Server seamlessly.

During the upsize process choose the option to create a new Access Data Project and get used to using that method from the outset as opposed to using linked in tables and you will find that not much has changed for you.

SQL Server 2005 or 2008 will give you the encryption & performance that you are looking for. The learning curve is flat and it is free.

 

[Atomic Shrimp]

I think I would recommend, if possible, keeping the table all in one piece. Splitting it into pieces is something that can easily go wrong resulting in duplicate or missing records, especially if the records appear to be in (say) date sequence, but in fact contain small blocks of data that are out of order.

Also if possible, it would be best to import it direct to your working DB without an intermediate conversion to text - as that can cause all sorts of weirdness (commas and quotes within text fields being interpreted as delimeters, for example, or problems with nonstandard or non-alphanumeric characters).