Difference Access - SQL Server

Hi,

i have a (probably very dumb) question:
I have an Access DB connected to an IIS via ODBC, and use ASP pages to get info to/from the DB.
My guys in IT now want to migrate the Access DB to SQL Server. Can anybody tell me what this implies in terms of
a) syntax of my ASP pages
b) syntax of my SQL statements
c) other problems I may not have thought of.

I am told it is really easy, however, I just cannot get is to work.
BTW, I have activated the "SQL Syntax" option in Access, in case that helps any.

Any comments are well appreciated!!!

Thank you.

There are no dumb questions ... I'm going through this myself with 9 databases. Some are easy. Used the Access 2002 Upsizing Wizard to convert the DB, changed the ASP Connection String to SQL Server and voila! (I have MSDE 2000 without Enterprise Manager, so data transfer is sometimes problematic.)

About half the databases have more difficult conversion issues. The Upsizing Wizard doesn't properly create the SQL Server tables, ASP code to insert dates needs to be changed ...

What exactly are you having trouble with? Is the SQL DB created? Trouble connecting? Problems inserting data? If I can help, I will.

Hi,

the problem I have is that my IT guys told me "no problem" when they said they want to transfer the DB.
The first problem arose when I tried to connect to the DB - the ODBC changed, so the Conn.Open wouldn´t work any more.
Also, the one time I got around that, it seemed to have a problem with my SQL strings.
The problem is not migrating the DB, that they managed rather well and I think the DB is ok. It is not big anyway.
The problems are - besides the one above:
1. Authentification: MY access DB did not have it (no need), but the SQL thingy does, and no one can tell me how to re-write the statements so that it includes User and PW.
2. Any other changes in how I handle the ODBC connection. I use the .Execute quite often, i.e. Always, actually.
3. Changes in the SQL statements.

Any help is appreciated!

If you would like code samples, let me know.

Thanks, guys, thanks.

Here's an excellent source for connection strings (you should consider using OLEDB instead of ODBC).

URL

I assume you're using SQL authentication?

ODBC with DSN:

oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

ODBC without DSN:

oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

If the SQL Server is on the same box as your web site, use "Server=(local);"

As far as SQL statements go, I'm still fixing my own code, so I can't offer too much advice. I do know that SQL Server handles dates differently than Access. If you're using # when inserting a date into an Access field, replace it with an apostrophe.

Access:

objCommand.CommandText = "UPDATE TableName SET DateField = #" & Request.Form("Date") & "# WHERE ID = '" & intRecordID & "'"
objCommand.Execute

SQL Server: change # to apostrophe symbol

I'm still tracking down other necessary changes in my own code...

Sorry, I was buried in other work the last days....

Thanks for all your help! I am going to attack the problem tomorrow with the SQL server guy.
So far the thing still runs in Access (and runs well, so I do not hope we are solving a problem we do not really have...)


Merry Christmas and a happy new year!