For more information, read our papers on Microsoft Access Split Database Architecture and Significantly Improve the Performance of Microsoft Access Databases with Linked Tables.
|(Declarations)||Declarations||Declarations and private variables for the modStartup module.|
|CreateLinkedTable||Procedure||Create a new table linked to a Jet/Access table in another database.|
|RelinkAllTables||Procedure||Relink all the linked (attached) tables in the current database to a new database.|
|RelinkTableDef||Procedure||Relink (attach) a table definition to a new database (eliminates the need to create a new database handle if processing lots of tables).|
|RelinkTable||Procedure||Relink (attach) one table in the current database to a new database.|
|OpenBackendDatabase||Procedure||Open a handle to a backend database and keep it open; or close the previously opened database.
NOTE: Significant performance improvements can be achieved by keeping a database handle open during the entire time your application is running. Visit our page, Significantly Improve the Performance of Microsoft Access Database with Linked Tables for more information.
|OpenAllDatabases||Procedure||Use this procedure when there are multiple backend databases to keep open; or close the previously opened databases.
NOTE: Significant performance improvements can be achieved by keeping a handle open during the entire time your application is running. Visit our page, Significantly Improve the Performance of Microsoft Access Database with Linked Tables for more information.
|SetErrorTrapping||Procedure||Set or reset error trapping setting so errors are properly handled while your application runs. This setting can be manually set from the IDE under the Tools, Options menu, General Tab. If error trapping is set to Break on All Errors, code with On Error Resume Next will stop if an error occurs. This setting is at the machine level, not your application so what works fine on one machine may fail on another due to this setting. To avoid this, make sure error trapping is set to Break in Class Modules by calling this routine when your application starts. When the application finishes, call this routine again to reset the setting back to the original value. For more information, read our page on Error Handling and Debugging Tips and Techniques.|
|GetCommandLine||Procedure||Access can be started with a command line option passed after the /cmd tag (commonly in a shortcut). Your program can use this as a flag for special processing.|
|GetCommandLineValues||Procedure||Access can be started with a command line option passed after the /cmd tag (commonly in a shortcut) Your program can use this as a flag for special processing. If you pass multiple values in this manner separated by a delimiter, the values are loaded into the passed array.|
|StartupFormGet||Procedure||Get the Microsoft Access database's current startup form name.|
|StartupFormSet||Procedure||Set or remove the startup form for a Microsoft Access database when it opens.|
' Example of modStartup ' ' To try this example, do the following: ' 1. Create a new form ' 2. Create a command button 'cmdTest' ' 3. Link the table named Categories from Sample.mdb (in the directory where you installed TVSB sample files, usually C:\TVSBSamp). ' 4. Paste this code into the form's module #If VBA7 Then Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long) #Else Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long) #End If Private Sub cmdTest_Click() Const cstrSample As String = "C:\Total Visual SourceBook 2013\Samples\Sample.mdb" Const cstrLinkedTable As String = "Categories" Dim strTmp As String Dim astrTmp() As String Dim intCounter As Integer ' Set Error Trapping option SetErrorTrapping True Debug.Print "Error trapping set..." ' GetCommandLine and GetCommandLineValues ' To test this, create a shortcut for your database with the command line /cmd Test1;Test2;Test3 ' e.g. C:\Total Visual SourceBook 2013\Samples\sample.mdb /cmd Test1;Test2;Test3 strTmp = GetCommandLine If strTmp = "" Then Debug.Print "Access was not started with a command line option." Else Debug.Print "Access was started with command line: " & strTmp If GetCommandLineValues(astrTmp()) Then For intCounter = 0 To UBound(astrTmp) Debug.Print astrTmp(intCounter) Next intCounter End If End If ' Open a handle to the backend database when the program starts for improved performance. ' This eliminates the creation and deletion of database locks as you open and close individual tables. If OpenBackendDatabase(True, cstrSample) Then Debug.Print cstrSample & " opened..." ' Wait 1 second Sleep 1000 ' Relink one table If MsgBox("Relink " & cstrLinkedTable & " to " & cstrSample & "?", vbYesNo) = vbYes Then If RelinkTable(cstrLinkedTable, cstrSample) Then Debug.Print cstrLinkedTable & " relinked." End If End If ' Relink all linked tables in the current database to this database If MsgBox("Relink All Tables to " & cstrSample & "?", vbYesNo) = vbYes Then If RelinkAllTables(cstrSample) Then Debug.Print "All tables relinked." End If End If strTmp = StartupFormGet() Debug.Print "The startup form is : " & strTmp If False Then ' This example is shown but not run since it changes the startup form of your database If StartupFormSet("MyForm") Then Debug.Print "Startup form is set to MyForm" End If ' Removees the startup form on your database If StartupFormSet("") Then Debug.Print "Startup form removed" End If End If End If ' Close the database object. This is normally called when the database application closes. If OpenBackendDatabase(False, cstrSample) Then Debug.Print "..." & cstrSample & " closed" End If ' Reset Error Trapping option when the application closes SetErrorTrapping False Debug.Print "...error trapping reset to previous value." End Sub
The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.
Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."
Van T. Dinh, Microsoft MVP