There are technical considerations to separate the data from all other things to manipulate the data (forms-query-macro-reports). A practical considerations arises when f.i. you run the financial administration for some legal identities.
In the past splitting of HSQLDB bases has been recommended. Auto migration to firebird leaves you with an embedded FB database.To split that i followed the scheme:
new database > connect to existing > create new ! > enter a dbBackEnd name > register Yes > save as dbFrontend name. ( this creates a dbBackEnd.fdb and a registered dbFrontEnd.odb file )
open the embedded and new dbFrontEnd side by side
drop and drag all tables to the dbFrontEnd
this gives rise to the known issue “identity violation”, because during drop and drag of filled tables the autonumbering field is not updated.
with Create Query in SQL View a list of tables and names can be obtained,
SELECT “RDB$RELATION_FIELDS”.“RDB$RELATION_NAME”, “RDB$RELATION_FIELDS”.“RDB$FIELD_NAME”, “RDB$RELATION_FIELDS”.“RDB$GENERATOR_NAME” FROM “RDB$RELATION_FIELDS” JOIN “RDB$RELATIONS” ON “RDB$RELATION_FIELDS”.“RDB$RELATION_NAME” = “RDB$RELATIONS”.“RDB$RELATION_NAME” AND “RDB$RELATIONS”.“RDB$VIEW_BLR” IS NULL AND ( “RDB$RELATIONS”.“RDB$SYSTEM_FLAG” IS NULL OR “RDB$RELATIONS”.“RDB$SYSTEM_FLAG” = 0 ) AND NOT ( “RDB$GENERATOR_NAME” IS NULL ) ORDER BY “RDB$GENERATOR_NAME”
The autonumbering field can be updated With
SELECT MAX("ID") FROM "TABLENAME" Observe NUMBER in output field. ALTER TABLE "TABLENAME" ALTER "ID" RESTART WITH NUMBER
( I could not get this going in a macro only in the TOOLS>SQL window )
Following code can be used to connect to another BackEnd.fdb data content base
Sub linkToOtherBackEnd() Dim sName as string Dim filePicker As Object Dim FPtype(0) As Integer FilePicker = CreateUnoService("com.sun.star.ui.dialogs.FilePicker") FPtype(0)=com.sun.star.ui.dialogs.TemplateDescription.FILEOPEN_LINK_PREVIEW_IMAGE_TEMPLATE 'FILEOPEN_SIMPLE FilePicker.initialize(FPtype()) If FilePicker.execute() Then sName = "sdbc:firebird:" & ConvertToURL(FilePicker.Files(0)) EndIf ThisDatabaseDocument.DataSource.URL = sName End Sub
Are there easier methods to split an embedded base?