Forum Moderators: open
First time poster, I just stumbled on your site from google. I've got a client who's got a rather large Access 2000 database (actually 50 of them, but that's not important yet).
He had me develop a project for him that adds features to this program, but here's the tricky part. How do I maintain the databases between a SQL 2000 DB and an Access 2000 DB.
A good friend of mine suggested using DTS to dump all the data into a 'temporary' database then execute a stored procedure to import the data into my new db. (I also have to figure out a way to send data back). My new tables all have datetime's that are updated when a record is created and updated and I'm having the old database admin do the same on his end (so we can track which records need to be updated / imported).
I also figured I should store the primary key from the Access database in my SQL DB so I can track where it came from when an update is needed.
I realize this is not a great solution, but it is a temporary solution while I design the rest of the application to replace the Access 2000 application (could take a year or two to finish that, thus the need for the immediate functionality).
If you think Stored Proc is the way to go, how do I import that data then?
I've never used Stored proc's to import data from another table, only to access data from a programming environment. How does the syntax go?
Thanks for your help,
Skylar Dodds
Make sense? In other words, I'm only importing "some" of the data and adding my own new data to it.
Maybe your suggestion is still the best one? I don't know, please let me know what you think.
This way you can add additional information to SQL Server as you develop the new application - and there would be no need to keep the data in sync as it is stored in only one place.
