Switching from access to sql server express in vb2005

Question

Right now im using an access database wich i connect to with oledb in VB2005. Now if in my whole program i want to change the database to sql server, is enough to just use sql instead og oledb (for example sqlcommand instead of oledbcommand, and slqconnetion instead of oledbconnection)?

Or is there something else you need to be carefull with?

To my understand, the sql language is almost the same so that shouldnt be a problem..

Answer 1

Hi,

Some of the concepts of access and sql server do not have a one to one mapping. If you don't have migrated the database schema already to access to can use the upscaling wizard in the tool menu of Access or the newer Sql Server Migration Assistant for Access:
http://www.microsoft.com/sql/solutions/migration/access/default.mspx

Once you have your schema and assuming they are almost the same, you just have to change your data access objects from OdlDb to Sql.

Regards,
Charles

Answer 2

cverdon wrote:

(...) If you don't have migrated the database schema already to access to can use the upscaling wizard in the tool menu of Access or the newer Sql Server Migration Assistant for Access: http://www.microsoft.com/sql/solutions/migration/access/default.mspx

Done.

cverdon wrote:

Once you have your schema and assuming they are almost the same, you just have to change your data access objects from OdlDb to Sql.

How could I quickly do that? I've tried with no success...

Many thanks in advance.

Answer 3

Hi,

Can you provide more information on your problems?

Charles

Answer 4

Hi cverdon,

thank you very much for your kind answer.
My problem is the following:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1159212&SiteID=1

Thanks again!

Answer 5

Assuming that the database schema is (almost) the same in your SQL Server db than in Access, you just have to create a new dataset and configure it exactly like the Access dataset, but using the SQL Server tables and in a different vb namespace. 

 If all the object names are the same, it would then be a matter of going through your program to change the namespace imports to point to the new dataset.

Charles