Da die Geschwindigkeit meiner Base-Datenbank mir immer noch nicht gefällt, will ich etwas herumoptimieren. In Roberts Handbuch habe ich gelesen, daß ODBC-Verbindungen "weniger" Java brauchen als JDBC-Verbindungen. Und da ich irgendwo aufgeschnappt habe, daß Java einfach langsam ist, hoffe ich deshalb auf eine Beschleunigung.
Bisher baue ich die Verbindung zu meiner lokalen gesplitteten DB mit JDBC auf, mit einem Pfad, der durch dieses Makro eingerichtet wurde:
Code: Alles auswählen
Sub Setup 'Tools > Customize... > Events > Open Document
'Globalscope.BasicLibraries.LoadLibrary("MRILib")
On Error Goto ErrorHandler
'get the current path to this .odb file
sPathURL = ThisDatabaseDocument.URL
sPath = ConvertFromURL(sPathURL)
sName = ThisDatabaseDocument.Title
iLen = InStr(sPath, sName)
sPath = Left(sPath, iLen-1)
'setup Class Path
ClassPath:
sClassPath = sPath & "hsqldb.jar"
If Not FileExists(sClassPath) Then
sLine1 = "Please add a copy of the HSQLDB engine (hsqldb.jar) to the current folder : "
sLine2 = "NOTE: This is necessary for proper wizard function, but additional benefits include : "
sLine3 = "* enhanced portability of the database-folder"
sLine4 = "* ensures database compatibility across computers and *Office installations"
sLine5 = "* guards against inadvertent upgrade of your database since the results are uncertain and irreversible"
sLine6 = "* hsqldb 2.x provides a built-in database management GUI accessible by clicking hsqldb.jar."
iButton = MsgBox (chr(13) & sLine1 & chr(13) & chr(13) & sPath & chr(13) & chr(13) & sLine2 & chr(13) & sLine3 & _
chr(13) & sLine4 & chr(13) & sLine5 & chr(13) & sLine6, 18, "hsqldb.jar not found")
If iButton = 3 Then Exit Sub 'ThisDatabaseDocument.close(True)
If iButton = 4 Then Goto ClassPath
End If
sClassPath = ConvertToURL(sClassPath)
ThisDatabaseDocument.DataSource.Settings.JavaDriverClassPath = sClassPath
'get the HSQL database name from the current folder
NextFile = Dir(sPath, 0)
While NextFile <> ""
If (Right(NextFile, 7) = ".script") Then dbName = (Left(NextFile, Len(NextFile)-7))
NextFile = Dir
Wend
If dbName = Empty Then
sLine1 = "Optionally provide a name for your back-end data files. "
sLine2 = "NOTE: The particular name is not important. The default below will suffice. "
dbName = InputBox(sLine1 & chr(13) & chr(13) & sLine2, "Create a new database * JDBC | HSQL database engine | non-embedded data files *", "mydb")
If dbName = "" Then dbName = "mydb"
End If
'check for existing database
sLine1 = "A new database will be created in the current folder: "
sLine2 = "NOTE: This folder constitutes your ""database."" "
sLine3 = "A dedicated database folder is fully portable, and as such it may be renamed or moved as desired."
sLine4 = "NOTE: This Base front-end file (" & sName & ") must remain in this database folder. "
sLine5 = "You may rename this file as desired, but do maintain the .odb extension if visible. Create a desktop shortcut to this file as desired."
sLine6 = "NOTE: The back-end HSQL data files will be named: " & dbName & ".* "
sLine7 = "These files must also remain in this database folder."
If Not FileExists(sPath & dbName & ".script") Then MsgBox sLine1 & chr(13)_
& sPath & chr(13) & chr(13) & sLine2 & sLine3 _
& chr(13) & chr(13) & sLine4 & sLine5 _
& chr(13) & chr(13) & sLine6 & sLine7 _
, 64, "Please Read"
'setup Data Source URL
sURL_prefix = "jdbc:hsqldb:"
sURL_args = ";default_schema=true;shutdown=true;hsqldb.default_table_type=memory;get_column_name=false"
sURL = sURL_prefix & "file:///" & sPath & dbName & sURL_args
ThisDatabaseDocument.DataSource.URL = sURL
Exit Sub
ErrorHandler:
MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")", 16, "Setup: macro code error"
End Sub
Jetzt würde ich gerne wissen: kann ich einfach
- ODBC installieren (HSQLDB kommt wohl mit dem PostgresQL-ODBC-Treiber zurecht, siehe https://hsqldb.org/doc/2.0/guide/odbc-c ... onfig-sect)
- und dann dieses Makro einfach abändern, indem ich in der Zeile "sURL_prefix = "jdbc:hsqldb:"" jdbc durch odbc ersetze?
Hat das schon einmal jemand ausprobiert?
Und: bringt es einen Geschwindigkeitsvorteil?
Ich hoffe auf Hinweise jeder Art. Vielen Dank.
Freischreiber