Hello,
Did not bother with your attachments. Please just zip & post here changing the ext to .odb and note it must be changed to .zip
Typically don’t deal with rar files.
The code has a number of naming errors. Even after correcting this nothing appears to happen. From the appearance of the code it is simply using SQL to read records in one table and write to another. May be a bit of code there for updating but to me not worth the effort.
This is not a new concept. Even if this worked (and the process seems backward) there is a matter of keeping things synced.
Edit:
Got the code to work. A number of errors in the documentation. Tested with HSQLDB embedded to HSQLDB embedded.
Here is the corrected code:
Sub DataCopy
Dim oDatabaseContext As Object
Dim oDatasource As Object
Dim oDatasourceZiel As Object
Dim oConnection As Object
Dim oConnectionZiel As Object
Dim oDB As Object
Dim oSQL_Command As Object
Dim oSQL_CommandTarget As Object
Dim oResult As Object
Dim oResultTarget As Object
Dim stSql As String
Dim stSqlTarget As String
Dim inID As Integer
Dim inIDTarget As Integer
Dim stName As String
Dim stTown As String
oDB = ThisComponent.Parent
stDir = Left(oDB.Location,Len(oDB.Location)-Len(oDB.Title))
Rem Modify to your odb
stDir = ConvertToUrl(stDir & "TargetDB.odb")
oDatasource = ThisComponent.Parent.CurrentController
If Not (oDatasource.isConnected()) Then
oDatasource.connect()
End If
oConnection = oDatasource.ActiveConnection()
oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDatasourceTarget = oDatabaseContext.getByName(stDir)
oConnectionTarget = oDatasourceTarget.GetConnection("","")
oSQL_Command = oConnection.createStatement()
Rem Modify to your table
stSql = "SELECT * FROM ""CUSTOMER"""
oResult = oSQL_Command.executeQuery(stSql)
While oResult.next
inID = oResult.getInt(1)
stName = oResult.getString(2)
stTown = oResult.getString(3)
oSQL_CommandTarget = oConnectionTarget.createStatement()
Rem Modify to your table
stSqlTarget = "SELECT ""ID"" FROM ""CUSTOMER"" WHERE ""ID"" = '"+inID+"'"
'oResultTarget = oSQL_CommandZiel.executeQuery(stSqlTarget) '
oResultTarget = oSQL_CommandTarget.executeQuery(stSqlTarget)
inIDTarget = - 1
While oResultTarget.next
inIDTarget = oResultTarget.getInt(1)
Wend
If inIDTarget = - 1 Then
Rem Modify to your table
stSqlZiel = "INSERT INTO ""CUSTOMER"" (""ID"",""NAME"",""ADDRESS"") VALUES ('"+inID+"','"+stName+"','"+stTown+"')"
oSQL_CommandTarget.executeUpdate(stSqlZiel)
End If
Wend
End Sub
