Greetings everyone. I was recently looking to find a way to copy a bunch of records from an access database up to a SQL server. I couldn't find much, so I created this script. It is the fastest way of copying records that I could find. My goals were to make sure the records get there, no duplicates, and speed across both a LAN an a WAN. This script was able to copy about 60 records a second from access to SQL with all services on 1 PC (600MHz). It was almost that fast across a 100M LAN.
In both access and SQL, there is a table named "activity". There is a field called "tkey" in both tables. There is the addition of a localtkey field in the SQL database. That field is local to the client running access. This is to help tell if the record is already in SQL or not.
I case you are wondering what this code is for, it is an application that talks to a SQL server if it can. If the server or network goes down, the client can continue to record new records. when the link to SQL comes back up, this script can be run to sync the data to SQL.
Here is what this script does:
-Gets the first 10 non-uploaded records from Access.
-Writes these records to SQL
-Marks them as uploaded in Access.
Sample code:
-------------------
In both access and SQL, there is a table named "activity". There is a field called "tkey" in both tables. There is the addition of a localtkey field in the SQL database. That field is local to the client running access. This is to help tell if the record is already in SQL or not.
I case you are wondering what this code is for, it is an application that talks to a SQL server if it can. If the server or network goes down, the client can continue to record new records. when the link to SQL comes back up, this script can be run to sync the data to SQL.
Here is what this script does:
-Gets the first 10 non-uploaded records from Access.
-Writes these records to SQL
-Marks them as uploaded in Access.
Sample code:
-------------------
Code:
Dim nodata
Dim dblocation
Dim sqldc,sqlrs
Dim accessdc,accessrs
Dim cmdsqlread,cmdsqlwrite,cmdsqlstring
Dim cmdaccessread,cmdaccesswrite
Dim recordquantity,tkeyarray
Dim keepgoing,i,recordsuploaded
Dim POS,tkey,tdate,ttime,Price
dblocation = "C:\Inetpub\[URL unfurl="true"]wwwroot\testsql\speed.mdb"[/URL]
recordquantity = 10
'Open connections to SQL and Access
Set accessdc = Server.CreateObject("ADODB.Connection")
accessdc.Open = "DRIVER={Microsoft Access Driver (*.mdb)};dbq=" & dblocation & ";"
Set sqldc = Server.CreateObject("ADODB.Connection")
sqldc.Open "Driver={SQL Server};Server=127.0.0.1;Database=speedtest;UID=sa;PWD="
Response.Write("<HTML><HEAD><TITLE>SQL Speed Test</TITLE></HEAD>")
Response.Write("<BODY>")
'Delete all records from the SQL activity table
cmdsqlwrite = "DELETE FROM activity"
Set sqlrs = Server.CreateObject("ADODB.Recordset")
sqlrs.Open cmdsqlwrite, sqldc
'Set some records to be uploaded (total is x-1)
cmdaccesswrite = "UPDATE activity SET processed = 0 WHERE tkey < 9951"
Set accessrs = Server.CreateObject("ADODB.Recordset")
accessrs.Open cmdaccesswrite, accessdc
Response.Write("We are now going to copy all local unprocessed records to the main database.<BR><BR>")
Response.Write("<BR>Started<BR>")
'------------------------------------------------
keepgoing = 1 'This is so it runs until all records are done.
recordsuploaded = 0
cmdaccessread = "SELECT TOP " & recordquantity & " * FROM activity WHERE processed = 0"
Do Until keepgoing = 0
'Pull the first record(s) that needs to be updated.
Set accessrs = Server.CreateObject("ADODB.Recordset")
accessrs.Open cmdaccessread, accessdc
If accessrs.EOF Then
accessrs.Close
'We're all done with the updates. No more stuff to do.
keepgoing = 0
Else
'There is at least 1 record to work with.
tkeyarray = nodata
cmdsqlstring = nodata
cmdsqlstring = "BEGIN TRANSACTION "
Do Until accessrs.EOF
'Read in the data
POS = accessrs("POS")
tkey = accessrs("tkey")
tdate = DateValue(accessrs("tdate"))
ttime = TimeValue(accessrs("ttime"))
Price = accessrs("Price")
cmdsqlread = "SELECT * FROM activity WHERE (POS = '" & POS & "' AND localtkey = '" & tkey & "' AND tdate = '" & tdate & "')"
cmdsqlwrite = "INSERT INTO activity (tkey,localtkey,ID,code,Product,Price,tdate,ttime,FID,status,remarks,method,POS,meal) VALUES ('0', '" & tkey & "', '" & accessrs("ID") & "', '" & accessrs("code") & "', '" & accessrs("Product") & "', '" & Price & "', '" & tdate & "', '" & ttime & "', '" & accessrs("FID") & "', '" & accessrs("status") & "', '" & accessrs("remarks") & "', '" & accessrs("method") & "', '" & POS & "', '" & accessrs("meal") & "')"
cmdsqlstring = cmdsqlstring & "IF NOT EXISTS(" & cmdsqlread & ") "
cmdsqlstring = cmdsqlstring & "BEGIN "
cmdsqlstring = cmdsqlstring & cmdsqlwrite & " "
cmdsqlstring = cmdsqlstring & "END "
tkeyarray = tkeyarray & tkey & ","
i = i + 1
accessrs.MoveNext
Loop
accessrs.Close
cmdsqlstring = cmdsqlstring & "COMMIT TRANSACTION"
'Write the data to SQL
Set sqlrs = Server.CreateObject("ADODB.Recordset")
sqlrs.Open cmdsqlstring, sqldc
'Write as uploaded in access
tkeyarray = Left(tkeyarray, Len(tkeyarray)-1)
tkeyarray = Split(tkeyarray, ",")
For i = LBound(tkeyarray) To UBound(tkeyarray)
cmdaccesswrite = "UPDATE activity SET processed = 1 WHERE (tkey = " & tkeyarray(i) & ") "
Set accessrs = Server.CreateObject("ADODB.Recordset")
accessrs.Open cmdaccesswrite, accessdc
recordsuploaded = recordsuploaded + 1
Next
Response.flush
End If
Loop
If recordsuploaded = 0 Then
Response.Write("<BR>There were no records to write to SQL. Synchronization complete.")
End If
'------------------------------------------------
Response.Write("<BR>Stopped<BR>")
Response.Write("Records Uploaded = " & recordsuploaded & "<BR>")
Response.Write("</BODY></HTML>")
Response.End
'Close the connections to SQL and Access
accessdc.Close
Set accessrs = Nothing
Set accessdc = Nothing
sqldc.Close
Set sqlrs = Nothing
Set sqldc = Nothing
