We have a database hosted on godaddy which feeds a website built in classic ASP with some Javascript. We have a daily script which refreshes the database with an overwrite ie. full replacement and not incremental.
The downside of this is that if the website is in use and database locked, the site will fall over when we overwrite.
So we are investigating the option of inserting new/updating modified records only - we cannot get the code below to work:
Here is some sample code which needs polishing but works to test the scenario we are evaluating:
<%
'
' Show Date
'
Response.Write ("Date Today :" & Date & "<br/>")
'
' Connect to DB object
'
sConnect = "Driver={Microsoft Access Driver (*.MDB)}; DBQ=" & Server.MapPath("/temp/propertyDB.mdb")
sConnect_tmp = "Driver={Microsoft Access Driver (*.MDB)}; DBQ=" & Server.MapPath("/temp/propertyDB_tmp.mdb")
'
' Create recordset object
'
Set rsProperty = Server.CreateObject("ADODB.Recordset")
Set rsProperty_tmp = Server.CreateObject("ADODB.Recordset")
'
' Compose the SQL statement for the recordset.
'
sSQL = "SELECT * FROM Property ORDER BY txtRefNo"
'
' Open a recordset and move to the first record
'
rsProperty_tmp.Open sSQL, sConnect_tmp, 3
rsProperty_tmp.MoveFirst
Response.write "- The number of records to INSERT into PropertyDB is : [ " & rsProperty_tmp.RecordCount & " ] <br/>"
'
' Open a recordset and move to the first record + show record count
'
rsProperty.Open sSQL, sConnect_tmp, 3
rsProperty.MoveFirst
Response.write "- Total Number of Records before Insert = : [ " & rsProperty.RecordCount & " ] <br/>"
rsProperty.Close
'
' Insert Into SQL
'
sSQL ="INSERT INTO [property] SELECT distinct * FROM " & Server.MapPath("/temp/propertyDB_tmp.mdb") &".property"
rsProperty.Open sSQL,sConnect,3
Response.write "- The number of records in PropertyDB is now: [ " & rsProperty.RecordCount & " ] <br/>"
'
' CLOSE RecordCount
'
rsProperty_tmp.Close
rsProperty.Close
Set sConnect = nothing
set sConnect_tmp = nothing
Response.Write "- Connection & Recordsets CLOSED OK"
%>