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, [email protected]'></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>
Comments