Syncronise Databases

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>

You might also like...

Comments

Santi Mondejar

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Linux is only free if your time has no value” - Jamie Zawinski