Migrating Access to SQL

[r000t]

Hello,

I've been working on a project that pulls data from a Access database, however upon thinking about it I think the advantages of using an SQL database instead far exceed that of an Access database.

Would it be easy to change all the database code from Access syntax to SQL?

P.S Has anyone got any good info on using an SQL database in VB?

Thanks.

[techgnome]

It's fairly easy. Instead of using the OLEDBClient, you would use SQLClient. Of course the connection string will change. As for the SQL Queries themselves.... depending on your queries, you'll have some changes that will need to be done, but it shouldn't be a big deal.

As for using VB with SQL Server... try checking out the DB FAQ/Tutorial section.

-tg
fyi - there's no such thing as "SQL database".

[jmcilhinney]

First up, SQL is not a database. SQL is a language used to write queries and the like against databases. Access itself uses SQL for its data definition and data manipulation commands. If you actually mean SQL Server, which is a database, then you should use the correct name to avoid confusion.

You would basically just need to change all your OleDb types to SqlClient, e.g. OleDbConnection to SqlConnection, which is basically just a Find & Replace operation. You'd also need to change your connection string, which is about 10 seconds work. You might have to change some of your SQL code but, if you're only using simple queries and the like then that should be exactly same.

If you know how to use ADO.NET already, which you presumably do if you're using an Access database, then you pretty much already know how to use SQL Server. The only thing that really changes from database to database is the ADO.NET provider and the connection string, which aren't really anything specifically to do with VB anyway. ADO.NET works exactly the same way regardless of the database, so any ADO.NET information you find is relevant.

[r000t]

Thanks for your reply's Much appreciated