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

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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 _ "\\server\f\SkidControl\SkidControlBE.mdb", False _

objAccess.DoCmd.OpenReport "AllocationReport", acViewNormal objAccess.CloseCurrentDatabase

Set objAccess = Nothing


MsgBox "Report not printed. Please contact Tech Support", _ vbOKOnly, "Report failure" End If

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 = "\\server\f\FrameControl\FrameControlBE.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') " _

& "(((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


'Allow the user to select an existing spreadsheet Me.CommonDialogl.ShowOpen flname = Me.CommonDialogl.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, _


"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.

Was this article helpful?

+1 0

Post a comment