I don't know if you ever resolved this, I've done a bit of programming in both Access and the FactoryTalk HMI, just not together. Within the VBA project window of the Display you are working with, if you set a reference (Tools, References) to the Microsoft Access xx.x Object library, you should be able to use the Access objects.
You'll need to create an Access object, then refer to it. After that, it is pretty much like programming in any Microsoft VBA environment.
This is some Access code I use, outside of FTView:
Public Sub BuildInventoryHistory()
On Error GoTo Proc_Error
Dim ws As Workspace
Dim dbCurr As Database
Dim rstHistory As Recordset
Dim rstProduct As Recordset
Dim rstPeriods As Recordset
Dim rstBeginningBal As Recordset
Dim arrHistory As Variant
Dim arrProduct As Variant
Dim strSQL As String
Dim intRow As Integer
Dim intProdRow As Integer
Dim intMinProd As Integer
Dim intMaxProd As Integer
Dim dblMinPeriod As Double
Dim intCurrPeriod As Integer
Dim strCurrDate As String
Dim bolHasData As Boolean
Dim intPrecision As Integer
'
' Set up
'
' Set dbCurr = CurrentDb ' Replace this with the set dbCurr below, put the appAccess above it
'
' Load History from qryInventoryCalc (monthly net tons by product)
'
Set rstHistory = dbCurr.OpenRecordset("qryInventoryCalc")
rstHistory.MoveLast
rstHistory.MoveFirst
arrHistory = rstHistory.GetRows(rstHistory.RecordCount)
'
' Load Product with list of all products
'
strSQL = "SELECT CodeID " _
& "FROM tblCodes " _
& "WHERE CodeTypeID = 1 ORDER BY CodeID;"
Set rstProduct = dbCurr.OpenRecordset(strSQL)
rstProduct.MoveLast
rstProduct.MoveFirst
arrProduct = rstProduct.GetRows(rstProduct.RecordCount)
once the data gets into the array, you can use it as you want. The first instance uses a pre-built query, the second create an sql query; both return a recordset and then move the recordset into the array.
You will first need to create a reference to the Access application, after creating the application object:
dim appAccess as Application
then in the code below, create an instance of it:
Set appAccess = New Access.Application
use the application object to open your database:
Set dbCurr = appAccess.OpenAccessProject("\\MyServer\MyPath")
and go from there.