July 15, 2004 at 10:32 am
Hello SQLers,
What I want to do is "convert" an ACCESS 2000 database over to MSDE 2000. I know you can use the Upsizing Wizard in ACCESS but this will not be satisfactory because what we are tryng to do is upgrade a customer's software program and make it as transparent to them as possible. They don't even have Access installed on their PCs, just the JET Engine and all they will have after is MSDE 2000. What I have thought of doing was using a "brute force" method where I will read the ACCESS Database and create the MSDE Database line-by-line. This is not very elegant and I was wondering if someone has a better, practical, and more elegant way of doing this programattically.
Thank you for your help and suggestions in advance.
JRT
July 16, 2004 at 8:47 am
Do you want to move the tables to MSDE and leave everything else on the Access so that the data remains on the MSDE and Access will be front end?
If you are going to do this, just export all your Access tables to SQL and then fix the primary keys and data types and stuff in SQL. You may need to modify some of the queries and forms as well.
July 16, 2004 at 9:02 am
Actually, what I want to do is remove Access alltogether. Once I the tables are "transfered" to MSDE, the system will not use the Access database anymore. The most likely scenario would be that the file will be deleted from the system.
That is wht I want a programmable way of converting the databases over to MSDE so that it would be as transparent as possible to the end user.
July 19, 2004 at 2:45 pm
It sounds like your Access MDB has tables only in it. If so, you can use the Upsize Wizard to convert the data tables from ACCESS 2000 to MSDE 2000. Then, just discard the upsized ACCESS MDB, which will just be a shell of Linked table entries pointing to the MSDE 2000 tables.
Then... the customer's software program will have to be changed to reference the tables in MSDE instead of ACCESS -- but it sounds like you've planned for that step already.
Bob Monahon
July 20, 2004 at 9:16 pm
Be careful when using the upsize tool. It will create the tables and come 90 - 99% of guessing the properly data-types and sizes (usually closer to 99%)
UNfortunately, ANY PKs, Indexes, FKs, etc... are not created in SQL from this tool (unless this has changed). They will have to be re-created manually OR programatically. This is so all the surrounding SQL internal tables are updated.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply