Migrating Access 2000 To SQL Server database

[GrimmReaper]

Hello, I've found 2 options in migrating to a SQL Server database.
1) Use the Upsizing wizard in Access
2) DTS - Import and Export Data in SQL Server

I want to know which one is the best option to take ?
Isn't there a way I could do it in VB itself ??

[mendhak]

DTS. Best way. Tried and tested.

Yes, there is a way to do it in VB, but is there any specific reason you want to take this approach?

[GrimmReaper]

Thanx, I thought so.

The reason I want to do this with VB is that, I have a dualboot computer - Win98 and Win XP.
On my Win 98 part - I have the whole MS Office on XP, I don't

I was just wondering actually whether it is possible, so incase I might need it one day - I know it

[mendhak]

Just so you know, yes it is possible and the procedure is actually quite simple. On your XP machine, you only need the MDB, but you don't need MS Access. What helps you access the MDB file is the ODBC namespace in ADO.NET. You can simply open up the tables, read from them and run your INSERT statements for SQL Server. Of course, the table structure must be known.


OK, understood that?

Good. You can take things a step further by using ADOX, so you dynamically read the table structure, create the table in sql server, and then transfer the data.

Hope that confused you

[GrimmReaper]

Thanx once again, MendHak!

Nope, it didn't confuse me much - I just need to do a bit of research on ADOX, and how precisely to use the ODBC namespace in .Net

[mendhak]

Then you're halfway there. I don't know whether you'll be using this approach or not but if you do feel free to post here, I'm sure someone intelligent browses the forums.