October 28th, 2003, 03:26 PM
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.
Thanks in advance.
' 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"
if (Request.form("search") = "" ) or (Request.form("search") = " " ) then
SQL1 = "SELECT * FROM jobs"
set Rs = Conn.Execute(SQL1)
strSplit = split(Request.form ("search"),",") ' value to search for in title
For i = 0 To UBound(strSplit)
if (strSplit(i) <> "") and (strSplit(i) <> " ") Then
if (i=0) then
Booltxt="WHERE ( "
sql_search1 = sql_search1 & Booltxt & " description like '%" & strSplit(i) & "%' "
sql_search1 = sql_search1 & ")"
SQL = "SELECT * FROM jobs " & sql_search1
set Rs = Conn.Execute(SQL)
October 31st, 2003, 04:50 PM
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
October 31st, 2003, 11:51 PM
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.