Convert Access Queries to MSSQL

[Altair Brito]

Hello everyone.

I need help converting my access queries to SQL. I've converted all the
tables and it worked fine until I tried to run a query I had made in the
access db that did not convert to the SQL.

Is there a utility or a tool i can buy to this without having to do it
manually..? Or is there some other easier way of doing this..?

Thank you!

 

[John Spencer MVP]

I know of NO automated way to do this. Some queries will be fine while others
will have to be rebuilt if you are running them on SQL server.

All the VBA functions will not be available.

Here are a few of things you will run into if you are trying to run native SQL
on SQL server.
Access uses IIF in its queries. You will probably need to use a Case
Statement anywhere you have used an IIF.

Wild cards are different - only % and underscore for any number of characters
and any one character.

Joins are a bit different.
Left Join --> LEFT OUTER JOIN
Right Join --> RIGHT OUTER JOIN
Inner Join --> INNER JOIN

Delimiters are different -
Use ' not " to delimit strings
Use ' to delimit dates instead of #


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Altair Brito]

Ok. Thanks for you prompt help...

Another question:

How would I go about connecting to an Access DB that's on another network?
For instance, I have the DB on my web server that you can add files to, what
would be the connection string for VB to get data from that file..?

Locally I used:

CN.open "Provider=Microsft.Jet.OLEDB.4.0;Data Source=" & DBPath & "; Persist
Security Info=False;Jet OLEDBatabase Password:1234"

Thank you for you help!