Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CCMSQL1
Starting Member
2 Posts |
 Posted - 2007-12-07 : 11:51:10
|
| OK here is my situation: I work in a laboratory with a 10 year old instrument that stores results in an Access 97 DB. The DB is beautifully designed and implemented. The problem is that since the software is so old, once the DB gets above a few MB in size the application starts to fail. So their solution is to Backup (make a copy) and Cleanup (delete data older than X days) once a week. So what I end up with is a bunch of copies of the DB, which fragments the data that I need. In order to get the data results I have been using an Access 2003 DB linked to this one (because I can't create Views in that database). The lab users then use the linked DB directly or I have written some automation code in Excel VBA (ADO) to simplify it for the other users. This allows us to query data immediately as it is produced.My hope is that I can somehow get all of the data into a SQL Server DB. Now I have imported one of the backup copies of the DB in and the table structure and data imports fine into SQL Server, but I end up with duplicate rows because the different DB backup files have overlapping data, and for some reason there aren't PKeys. Also, I'd prefer to have some way of automating the input. Something like a trigger on the Access table so that any new data is imported into the SQL DB automatically. I doubt this is possible, but that is generally what I am trying to do and I am out of ideas. The ultimate goal is that the SQL DB will always be "updated" and I can use that as my query source and take advantage of the power of SQL Server.Hopefully that all made sense... if anyone has any ideas I would love to hear them. Thanks for your time. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
CCMSQL1
Starting Member
2 Posts |
Posted - 2007-12-08 : 12:42:36
|
| The problem of a few MB lies not with Access, but rather with the application that uses it. I don't really understand why this is, but the company says if you let it get too large it starts lagging and having communication issues. So their solution is to delete data older than X days after backing up.As for linking a SQL DB, I had thought of that. But when I backup and cleanup, the older data will be lost. I am hoping to copy the actual data into the SQL database. After a year I want to have all data in one SQL DB, not in 52 weekly backup files. So I am wondering how I can append any new data inserted into the Access DB into the SQL DB.Thanks |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-08 : 17:33:10
|
| You can copy data in sql server with ssis package or via linked server. |
|
|
|
|
|
|
|
|
Linking to SQL Server to Access 97