Inserting Records into AX5 Table Using VBA and COM

so you want to use MS Access to insert/update a Dynamics AX table. No problems! just a few things.

1. ensure that you have the COM object registered on the PC you are working on (axcom.dll - date stamp ‎Tuesday, ‎20 ‎May ‎2008, ‏‎3:33:40 PM) this is the one that comes with AX5 (2009), not the version 3 com object.

2. ensure that in MS Access, Visual Basic Editor > Tools > Reference, that you select that dll and the database file knows you are using it.

3. Do the following Code

Function InsertToProjJournalTrans(strApprovedBy As String) As Boolean
On Error GoTo err
Dim comAx As AxaptaCOMConnector.Axapta3
Dim updateTable As AxaptaCOMConnector.IAxaptaRecord

Set comAx = New AxaptaCOMConnector.Axapta3

With comAx
.Logon "", "", "", ""
Set updateTable = .CreateRecord("AxmProjJournalTrans")
.TTSBegin
.ExecuteStmt "SELECT * FROM %1", updateTable

With updateTable
.Field("TransIdRef") = 5555
.Insert
End With

.TTSCommit
.Logoff
End With

eer:
Set comAx = Nothing
err:
MsgBox err.Description & " - InsertToProjJournalTrans", vbExclamation, strAppTitle
Resume eer
End Function

hope that helps.