Writing to a database table from an Access form

Question

I am trying to write to an editable database table, from an Access form, and I keep receiving a syntax error.

The table has about 30 columns, but I only want the record filling out 8 of them: (Job #, Job Name, Date, Superintendent, Hauling Vendor, Material, LTC, Loads).

The form consists of the 8 fields named the following:

ReportingCOM_Job
ReportingBox_JobName
ReportingBox_Date
ReportingCom_SI
ReportingCom_Vendor
ReportingCom_Mat
ReportingCom_LoadType
ReportingBox_Loads

These are a combination of drop down list from queries, text inputs, and hard coded values, so a simple Open Form > New Record will not work here; or so I do not believe.

Is this even even possible?

I am using the following line and can not get this error to clear.

CurrentDb.Execute "INSERT INTO MATLog(Job #, Job Name, Date, Superintendent, Hauling Vendor, Material, LTC, Loads) " & _
    " VALUES(" & Me.ReportingCOM_Job & "," & Me.ReportingCom_JobName & ",#" & Me.ReportingBox_Date & "#," & _
    Me.ReportingCom_SI & "," & Me.ReportingCom_Mat & "," & Me.ReportingCom_Vendor & "," & Me.ReportingCom_LoadType & ",#" & Me.ReportingBox_Loads & "#)"

This form should create a new record with these 8 fields, and leave the rest blank.

Lee Mac · Accepted Answer · 2019-06-10T20:06:50.470

Fields with spaces or other reserved names or symbols in their field names (such as Date) will need to be enclosed with square brackets, e.g.:

INSERT INTO MATLog([Job #], [Job Name] ... )

You will also need to surround the values of Text fields in the VALUES list with single or double quotes, e.g.:

",'" & Me.ReportingCom_JobName & "',

However, whilst the above should allow your query to execute successfully, your current approach of constructing a SQL statement using unvalidated form values input by a user is vulnerable to SQL injection, and you will also encounter issues when inserting text values which themselves contain SQL string delimiters (single/double quotes).

Instead, a better approach is to parameterise your query.

One possible example of this could be the following:

With CurrentDb.CreateQueryDef("", _
    "insert into matlog ([Job #], [Job Name], [Date], Superintendent, [Hauling Vendor], Material, LTC, Loads) " & _
    "values (p1,p2,p3,p4,p5,p6,p7,p8)")
    .Parameters!p1 = Me.ReportingCOM_Job
    .Parameters!p2 = Me.ReportingBox_JobName
    .Parameters!p3 = Me.ReportingBox_Date
    .Parameters!p4 = Me.ReportingCom_SI
    .Parameters!p5 = Me.ReportingCom_Vendor
    .Parameters!p6 = Me.ReportingCom_Mat
    .Parameters!p7 = Me.ReportingCom_LoadType
    .Parameters!p8 = Me.ReportingBox_Loads
    .Execute
End With

Thank you so much @LeeMac This worked like a charm. And thanks for the tip! — Joey B, Jun 13 '19 at 21:28

score 1 · Answer 2 · answered Jun 10 '19 at 20:37

Simply use a saved query and open it to run. Doing so, you avoid any need to concatenate or punctuate.

SQL (saved below as stored query, replace myFormName with actual form name)

INSERT INTO MATLog([Job #], [Job Name], [Date], [Superintendent], 
                   [Hauling Vendor], [Material], [LTC], [Loads]) 
VALUES(Forms!myFormName!ReportingCOM_Job, Forms!myFormName!ReportingCom_JobName,
       Forms!myFormName!ReportingBox_Date, Forms!myFormName!ReportingCom_SI,
       Forms!myFormName!ReportingCom_Mat, Forms!myFormName!ReportingCom_Vendor, 
       Forms!myFormName!ReportingCom_LoadType, Forms!myFormName!ReportingBox_Loads)

VBA (no need to close action queries, use warnings to avoid user prompts)

'DoCmd.SetWarnings False
DoCmd.OpenQuery "mySavedQuery"
'DoCmd.SetWarnings True

Writing to a database table from an Access form

2 Answers2