15.6. Using the Access Object Model to Pull Data from Access to Other Applications

The following sections don't use any VBA within Access, but if you're going to use Access to control other applications, chances are you'll eventually need to use VBA or VBScript within other applications to manipulate Microsoft Access. We'll only spend a very brief couple of pages on these concepts. You can utilize the Access object model from Visual Basic to enhance your Visual Basic applications with Access reports. Before you can write code utilizing the Access object model from your Visual Basic applications, you need to first add a reference to the Access 11 object model in your project's References dialog box.

The following code opens an Access report based on prompted criteria and prints it from within your Visual Basic application.

Private Sub mnuConsumptionReport_Click()
Dim objAccess As Access.Application
Set objAccess = CreateObject("Access.Application")
If Not (objAccess Is Nothing) Then
    objAccess.OpenCurrentDatabase _
"\serverfSkidControlSkidControlBE.mdb", False _
    objAccess.DoCmd.OpenReport "AllocationReport", acViewNormal
    objAccess.CloseCurrentDatabase
    Set objAccess = Nothing
Else
    MsgBox "Report not printed. Please contact Tech Support", _
vbOKOnly, "Report failure"
End If
If Err <> 0 Then
    Err.Clear
End If
End Sub

You can even utilize Visual Basic to export information from Access into Excel. The following code performs that export and allows the user to either create a new spreadsheet or add a worksheet to an existing spreadsheet. This method utilizes an ADO Recordset that pulls data from an Access database. You could just as easily alter your code to utilize the Access object model to execute a query to gather this data to export to Excel.

Private Sub mnuSheeter_Click()
Dim strSQL As String
Dim xlapp As Object
Dim xlwkb As Object
Dim xlwks As Object
Dim objRST As ADODB.Recordset
Dim Response As String
Dim lvlColumn As Long
Dim NewFile As String
Dim flname As String

'Always have a way to handle errors
On Error GoTo errhandler
'Establish your ADO connection
Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
g_objDBPath = "\serverfFrameControlFrameControlBE.mdb"
objConn.Open g_objDBPath
'Allow the user to enter the filter parameter for the report
Response = InputBox("Please enter the date for the inventory report." _
, "Enter Date")
'dt will be the name of the worksheet
dt = DatePart("m", Response) & DatePart("d", Response) _
& DatePart("yyyy", Response)
strSQL = "SELECT SkidNumber, PONumber, ReceivedBy, PriceMSF, " _

& "Width, Grain, Type, SkidType, InitialQuantity, " _
& "InitialValue, Format([ReceivedDate], 'Short Date') " _
& "AS [Date] From Skids WHERE " _
& "(((Format([ReceivedDate],'Short Date')) " _
& "= #" & Response & "#) AND Location <> 'Deleted'),"
'Create and open your recordset
Set objRST = CreateObject("ADODB.Recordset")
objRST.Open strSQL, objConn, adOpenStatic, adLockReadOnly
'Create your Excel spreadsheet
Set xlapp = CreateObject("Excel.Application")
blnNewFile = MsgBox("Do you want to create a new file?", vbYesNo, _
"Create File?")
If blnNewFile = vbYes Then
Set xlwkb = xlapp.Workbooks.Add
Else
    'Allow the user to select an existing spreadsheet
    Me.CommonDialog1.ShowOpen
    flname = Me.CommonDialog1.FileName
    Set xlwkb = xlapp.Workbooks.Open(flname)
End If
xlapp.Visible = True
With xlwkb
    Set xlwks = .Worksheets.Add
    xlwks.Name = dt
    For lvlColumn = 0 To objRST.Fields.Count - 1
      xlwks.cells(1, lvlColumn + 1).Value = _
objRST.Fields(lvlColumn).Name
    Next
    xlwks.Range(xlwks.cells(1, 1), _
    xlwks.cells(1, objRST.Fields.Count)).Font.Bold = True

    With xlwks
        xlwks.Range("A2").CopyFromRecordset objRST
    End With
End With
xlapp.Visible = True
objRST.Close
Set objRST = Nothing
Set objConn = Nothing
Set xlwks = Nothing
Set xlwkb = Nothing
Set xlapp = Nothing
Exit Sub
errhandler:
    If Err.Number = 3021 Then
        MsgBox "There are no records for today. Please enter another
date.", vbOKOnly, "Error"
        objRST.Close
        Set objRST = Nothing
    Else
        basErrorLogger.LogAddInErr Err, "Sheeter Report", _
"Export to Excel", "error line"
        basErrorLogger.LogAddInErr Err, Err.Number, _

Err.Description, "error specifics"
    End If
Exit Sub
End Sub

No matter how you choose to utilize other Office Applications within your VBA code, you're sure to dazzle your coworkers with the rich content you can provide from your Access database.