Sincro.asp allows you to load data between two databases on line.
It opens two parallel connections, and it synchronizes two recorset that
works in the two databases, reading in one, and writing in the other.
Simply, the name of the origin table must be indicated, as well as odbc
dsn of each data bases. This example works with two ccess data bases
without dsn, although in fact I use script to load data in a Oracle system
from a data base Access via odbc.
<HTML><HEAD><META AUTHOR='Santi mondejar, smondejar1@excite.es'></HEAD><body>
<% Dim tbl
'table name
tbl="some_table"
'Opening Database 1.
Set db_cnx1 = Server.CreateObject("ADODB.Connection")
db_path="DBQ=" & server.mappath("db1.mdb")
db_cnx1.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & db_path
sql1="Select * from " & tbl
on error resume next
Set db_rcdst1=db_cnx1.execute(sql1)
'Opening Database 2
Set db_cnx2=server.createobject("adodb.connection")
db_path="DBQ=" & server.mappath("db2.mdb")
db_cnx2.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & db_path
'Starting table & recorsets iteraction
db_rcdst1.MoveFirst
all_flds = db_rcdst1.Fields.Count-1
all_records = 0
db_cnx2.BeginTrans
Do While Not db_rcdst1.EOF
all_records=all_records+1
Sqlz=""
For nCounter=0 to all_flds
current_value = db_rcdst1(nCounter)
If IsNull(current_value) Then current_value=Chr(32)
If VarType(current_value) > 6 Then
Sqlz=Sqlz & Chr(39) & current_value & "',"
Else
current_value=Replace(current_value, Chr(44), Chr(46) )
Sqlz=Sqlz & current_value & ","
End If
Next
a = Left(Sqlz, Len(Sqlz)-1)
sql1="Insert Into " & tbl & " values(" & a & ")"
Set db_rcdst2=db_cnx2.execute(sql1)
db_rcdst1.movenext
loop
If db_cnx2.Errors.Count = 0 Then
db_cnx2.CommitTrans
Response.Write"<br>"
Response.Write all_records & " rows inserted in destiny."
Else
db_cnx2.RollbackTrans
Response.Write " Unexpected error. Operation canceled"
End If
db_cnx2.close
set db_cnx2=nothing
db_cnx1.close
set db_cnx1 = nothing %>
</BODY></HTML>