#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Essex, UK
    Posts
    164
    Rep Power
    11

    change from Access to MS SQL


    At the moment I have an MS Access database and is getting full very quickly.

    I want to upgrade to a MS SQL database provided by my hosting provider. My current site is written in ASP and SQL, I want to know would I have to allter any SQL or ASP coding to make it compatible with the MS SQL database instead of access.

    This is an example of the coding that I am using at the moment to perform searches.

    Code:
    <%
    
    ' Open the Database Connection
    SET Conn = Server.CreateObject("ADODB.Connection")
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & Server.MapPath("\db\jobdatabase.mdb") & ";" & _
                  "Persist Security Info=False"
    Conn.Open(sConnection)
    %>
    
    <%
    if (Request.form("search") = "" )  or (Request.form("search") = " " ) then
      SQL1 = "SELECT * FROM jobs" 
      set Rs = Conn.Execute(SQL1)
    Else
    
      strSplit = split(Request.form ("search"),",") ' value to search for in title
      Dim iNext
      For i = 0 To UBound(strSplit)
        if (strSplit(i) <> "") and (strSplit(i) <> " ") Then
          if (i=0) then
            Booltxt="WHERE ( "
          Else
            Booltxt="OR "
          End If
          sql_search1 = sql_search1 & Booltxt & " description like '%" & strSplit(i) & "%'   " 
         End If
      Next 'i
      sql_search1 = sql_search1 & ")"
    
      SQL = "SELECT * FROM jobs  " & sql_search1  
      set Rs = Conn.Execute(SQL)
    End If
    %>
    Thanks in advance.
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    The above SQL statement will work unmodified on MSSQL. You'll need to change the sConnection string though, to connect to MSSQL instead of Access.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,439
    Rep Power
    4539
    There are other differences you'll have to accomodate in your code. For example, in Access the delimiter for date fields is #, but in sql server it's '

    There is no boolean datatype in sql server

    There is a page on the MS website that describes the differences between the two databases.

IMN logo majestic logo threadwatch logo seochat tools logo