Running SQL from VBA in Macro

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

This is probably an easy answer, but for some reason I just can't find it. I
have a macro that will do mulitple steps, one steps deletes records in a
table. Did an easy RunSQL with my SQL Statement of DELETE FROM [TBL-AKM
IMPORT].

After I import new data, I need to append this to another table (TBL_AKM
IMPORT is a temp table, that gets deleted every new import). Since the SQL
to move these items is longer than 255 characters, I have written it in VBA.
It runs fine, but I can't seem to get it to run in the macro. How do I call
the procedure?

The code I wrote is:

Public Sub doSQL()

Dim SQL As String

SQL = "INSERT INTO [TBL-TRANSACTIONS] ( [DISPUTE ID], " & _
"[CUSTOMER NAME], [CLAIM TYPE REASON], [CARD NUMBER], " & _
"[CLAIMS TRACK NUMBER], [DDA ACCOUNT NUMBER], [DATE RECEIVED], " & _
"[POSTED DATE], [POSTED AMOUNT], [CLAIM AMOUNT], [PROVISIONAL CREDIT
AMOUNT], " & _
"[OPERATOR ID], [OPERATOR EMAIL], [HOGAN TRAN DESC] )" & _
"SELECT [DISPUTE ID], [CUSTOMER NAME], [CLAIM TYPE], [CARD NUMBER],
" & _
"[CLAIMS TRACK NUMBER], [DDA ACCOUNT NUMBER], [DATE RECEIVED], " & _
"[POSTED DATE], [POSTED AMOUNT], [CLAIM AMOUNT], [PROVISIONAL CREDIT
AMOUNT], " & _
"[OPERATOR ID], [OPERATOR EMAIL], [HOGAN TRAN DESC]" & _
"FROM [TBL-AKM IMPORT]"

DoCmd.RunSQL SQL

End Sub

Thanks,
Ed
 
Back
Top