Most of the functionality available in Microsoft Access can be called from an external program, such as one written in Visual Basic 6 or Excel. This class contains methods and properties that show you how to get to database objects in a Microsoft Access database.
|(Declarations)||Declarations||Declarations and private variables for the CAccessAutomation class.|
|AppAccess||Property||Handle to the Access application.|
|AccessPath||Property||Path where Access is installed.|
|AccessVersion||Property||Access Version number.|
|Visible||Property||Get the visible property of the current instance of Access.|
|WorkgroupPath||Property||The workgroup information file contains security definitions of users, groups and permissions for Access and Jet databases.|
|StartAccess||Method||Starts an instance of Access without opening a database; not needed if the OpenDatabase function is called.|
|OpenDatabase||Method||Open the specified database in the current instance of Access.|
|OpenTable||Method||Open the specified table.|
|OpenQuery||Method||Open the specified query.|
|OpenForm||Method||Open the specified form.|
|OpenReport||Method||Open a report in design, preview, or report view mode. Also print it. To specify a range of pages to print, use the ReportPrintPages procedure instead.|
|ReportPrint||Method||Print an entire report. To specify a range of pages to print, use the ReportPrintPages procedure instead.|
|ReportPrintPages||Method||Print a range of pages from a report.|
|CloseAccess||Method||Close the instance of Access.|
|CloseDatabase||Method||Close the current open database.|
|CloseObject||Method||Close an object.|
|DeleteObject||Method||Delete the named object from the database.|
|DuplicateObject||Method||Duplicates (copies) the specified object to a new name (an object with the new name must not already exist).|
|ExecuteSQL||Method||Executes an action query represented by a SQL string.|
|ExportObject||Method||Exports the named object from the current database to another database in the specified format.|
|ImportObject||Method||Imports the named object into the current database.|
|TableToXML||Method||Export data from a table or query to an XML file.|
|ReportOutput||Method||Export a report to a file such as a PDF, XPS, HTML, Snapshot, etc.|
|ReportOutputPDF||Method||Export a report to a file in PDF format.|
' Example of CAccessAutomation ' ' To use this example, create a new module and paste this code into it. ' Then run the procedure by putting the cursor in the procedure and pressing: ' F5 to run it, or ' F8 to step through it line-by-line (see the Debug menu for more options) Private Sub Example_CAccessAutomation() ' Comments: Examples of using the CAccessAutomation class to launch and run a Microsoft Access database using VBA and VB6 Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\" Const cstrSampleDB As String = cstrSamplePath & "SAMPLE.MDB" Const cstrReportPDF As String = cstrSamplePath & "Invoices.PDF" Const cstrDuplicate As String = "Copy of Customers" Const cstrReport As String = "rptInvoice" Const cfVisible As Boolean = True ' Limit to the first 30 orders. Without it, there'd be 830 orders. Const cstrReportWhere As String = "[OrderID] <= 10277" Dim clsAccess As CAccessAutomation Dim fOK As Boolean Dim accApp As Access.Application Dim strSQL As String ' Initialize the variable to the automation class Set clsAccess = New CAccessAutomation With clsAccess If .StartAccess(cfVisible) Then ' Open the database If .OpenDatabase(cstrSampleDB, False) Then ' Use the Access object to invoke any Access method or property Set accApp = .AppAccess Debug.Print "Access Version " & accApp.Version Set accApp = Nothing ' Use the properties of the class Debug.Print "Access Path: " & .AccessPath Debug.Print "Access Version: " & .AccessVersion Debug.Print "Workgroup Path: " & .WorkgroupPath .Visible = False Debug.Print "Database set to invisible" fOK = .OpenForm("frmDepartment") Debug.Print "Form opened " & fOK ' Show Access again .Visible = True ' ===================================== ' Previewing and printing a report ' Create a PDF file from the report. The report should not be in preview mode when this is called. A WHERE clause is used to limit the number of invoices. ' The PDF file is opened when it's created. .ReportOutputPDF cstrReport, cstrReportPDF, True, , cstrReportWhere fOK = .OpenReport(cstrReport, acViewPreview) Debug.Print "Report previewed " & fOK If MsgBox("Do you want to print one page of the sample report?", vbYesNo) = vbYes Then ' Print page 3 fOK = .ReportPrintPages(cstrReport, 3, 3) ' This prints the entire report ' fOK = .ReportPrint(cstrReport) End If ' Close the report that's being previewed (the report doesn't need to be previewed before calling the print functions) .CloseObject acReport, cstrReport, acSaveNo ' ===================================== ' Copying tables, modifying data, and deleting objects fOK = .DuplicateObject("", cstrDuplicate, acTable, "Customers") Debug.Print "Table duplicated " & fOK strSQL = "UPDATE [" & cstrDuplicate & "] SET Fax = Null" fOK = .ExecuteSQL(strSQL) Debug.Print "Update query executed " & fOK fOK = .OpenTable(cstrDuplicate) Debug.Print "Table opened " & fOK fOK = .CloseObject(acTable, cstrDuplicate) Debug.Print "Duplicate table closed " & fOK fOK = .DeleteObject(acTable, cstrDuplicate) Debug.Print "Duplicated table deleted " & fOK If MsgBox("Do you want to leave Access open?", vbQuestion + vbYesNo) = vbNo Then .CloseAccess End If End If End If End With Set clsAccess = Nothing 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