Sunday, March 25, 2012

(newbie ques) Access 2000 to SQL Server migrate sync

I am a db newbie and am looking for advice on how to migrate a (fairly large - 900mb) Access b/e to SQL server 2000. The f/e is Access. I've read most of the upsizing info, but need help in understanding how to merge in new records/data which occur while the upsizing is done. (i estimate it will take several days to sort out the upsizing mess). This is a very active b/e, and not a lot of downtime is avail.

thanks for any expert advice

crunchy f.alter the access tables to add an extra column, let's call it Converted, with Required=yes, Allow zero length=No, Default='n'

run the extract to begin the upsizing process, then set Converted='y' for all records before putting the access database back online

all your existing apps can then continue as before

when the first stage of upsizing is done, do another extract, pulling only records where Converted='n' and set these to 'y' before putting the database back online

lather, rinse, repeat

rudy
http://rudy.ca/|||I am also interested in this. But just for my curiosity, can you let me know the significance of this new column etc.,
By the way, my knowledge of access and SQL and Vb is limited as I have learnt through forums like this
Thanks for your time and patience|||the significance of this new column? it marks which rows have been upsized

when you first add the column to the table, all rows have N

then you upsize all rows, and mark them Y (converted)

then the access application adds some more rows into the table, and they all get N by default

now you want to upsize only the recent ones, so you simply select them with converted='N' so that you don't pick up any rows that have already been upsized

rudy

No comments:

Post a Comment