Migration of Access dataBase to SQLServer, what should I change in the code?

Question

I have a web site that uses access database, I want to change connection to SQL Server DB, I migrate the Database with SQL Server, however I Have to change the connection code and sql requests.

here is a copy of what I have in the code (Im using WebMatrix):

pageConn.asp:

 <% Dim conn,connstr,Clkj_mdb
    Clkj_mdb="/DaTa/#Clkj_Cms#.mdb"
    connstr="DBQ="+server.mappath(""&Clkj_mdb&"")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
    on error resume next
    set conn=server.createobject("ADODB.CONNECTION")
    conn.open connstr
        if err then
            err.clear
            set conn=nothing
            response.write "Connect Error!"
            response.End         
        End IF
   %>

Should I change something in this part too??

Class.asp:

<% If request.querystring("Edit")="B_E" Then 
    Set Rs=server.createobject("ADODB.Recordset")
    Sql="select * from clkj_BigClass where clkj_BigClassID ="&request("clkj_BigClassID")
    Rs.open Sql,conn,1,1
%>

Thank you for your Time!

Mike Brind · Accepted Answer · 2013-04-29T11:27:08.777

0

You don't need to make any changes to the SQL in this case, but you need to change the connection string. You can find example connection strings here: http://connectionstrings.com/sql-server-2012

Soemthing like this should do it:

connstr = "Provider=sqloledb;Data Source=<name of server>;Initial Catalog=<name of database>;User Id=<user id>;Password=<password>"

Going forward, the main differences between JET SQL (Access) and T-SQL (SQL Server) are:

You cannot use * in DELETE statements in T-SQL: DELETE * From MyTable becomes DELETE FROM MyTable in SQL Server.

Yes/No columns in Access become BIT fields in SQL Server. True is represented by 1 in SQL Server, not -1 as in Access.

If you are using # as delimiters for dates in JET SQL, you have to change them to single quotes ' in T-SQL.

edited Apr 29 '13 at 11:27

answered Apr 29 '13 at 11:18

Mike Brind

28,238
6
56
88

What should I do instead of this variable: Clkj_mdb ?? `Clkj_mdb="/DaTa/#Clkj_Cms#.mdb"` – tollamie Apr 30 '13 at 06:55
Nothing. It's just a path to a file. SQL Server connection strings don't need paths. – Mike Brind Apr 30 '13 at 09:46
But this variable is used in other pages like: `` – tollamie Apr 30 '13 at 09:53
The variable Clkj_mdb that contains the path to a file is used in another asp page: `` what should I do instead of this variable ?? – tollamie Apr 30 '13 at 10:37
I have no idea what your application needs it for, so I can't answer that question. – Mike Brind Apr 30 '13 at 12:02

Migration of Access dataBase to SQLServer, what should I change in the code?

1 Answers1