Convert Access to SQL

[Mainer]

I am rather new to VB and SQL. I've searched this forum for solutions to my problem. While I have found others trying to convert, the answer is not available to me because of software/computer limitations.
I need to be able to convert an Acess database to SQL server thru code. I can't use the import or Upsize option in SQL because the computer this is on does not have the full version of SQL. To make matters worse, I can't get the Access off the machine to work on it elsewhere because it doesn't have a CD burner, Internet access. This is why I have to be able to do it thru code in VB.
I figure that I should open the Access db, store the record/fields to memory and then write them to the SQL. Seems like it is simple enough but I can't get it to work. I've written just a simple Do While program that doesn't accomplish anything.
Any help or suggestions is greatly appreciated.

 

[vb5prgrmr]

So you are trying to upgrade an Access database to MSDE from the sounds of it. What version of Access is this? With 2000 there is an upsizing wizard and with 97 there use to be an addin that you could download for upsizing. Have you tried these?

 

[zemp]

If you are trying to bring the Access to another machine and have no normal access (as you mentioned) try removing the hard drive and putting it in another computer as a slave. Then you have access to all your data and programs. When you are done return the hard drive to its original computer.

I had to do this once and it worked out well. Something to think about. Thanks and Good Luck!

zemp

 

[Mainer]

It is my understanding that the SQL program is not a full version. Would the Upsize wizard still work with a limited version of SQL? I do not know what version of Access the customer has. My boss just came back from them and told me of this problem and that I needed to write a short, simple program to convert the database.

 

[ericksoda]

You will need to create the database and then populate the tables.

There is an article in the Windows Developer magazine Jan 2003 that talks about doing SQL Server management from VB code. The article is "SQL DMO Automation". Their web page is

http://www.windevnet.com/wdm.

David