Insert Userform data into Access table using excel vba

Question

I'm currently making a simple inventory system application using Excel VBA. I have a userform to get the input and I would like to save the import data into a few Microsoft Access tables.

I am trying to pull the data from the userform and enter it into the access table when the user hits enter. When I run this code, a new record is made with the table ID but the two records I am trying to import are left blank.

    Public Sub AddDatabaseEntry()

      'Initialize all variables
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim stDB As String, stSQL As String, stProvider As String
      Dim orderNum As String
      Dim orderDate As String

      orderNum = txtOrderNum
      orderDate = txtDate


      stDB = "Data Source= " & ThisWorkbook.Path & "\obsDatabase.accdb"
      stProvider = "Microsoft.ACE.OLEDB.12.0"


      'Opening connection to database
      With cn

          .ConnectionString = stDB
          .Provider = stProvider
          .Open

      End With

     'SQL Statement of what I want from the database
      stSQL = "INSERT INTO Orders (OrderNumber, OrderDate) " & _
              "Values ('" & orderNum & "', '" & orderDate & "')"

      Set rs = cn.Execute(stSQL)


      'Looping through the records I pulled and inserting the data into the comboBox

      cn.Close
      Set rs = Nothing
      Set cn = Nothing

    End Sub

    Private Sub btnAdd_Click()

      AddProduct
      AddDatabaseEntry

    End Sub

Fionnuala Fionnuala · Accepted Answer · 2012-07-27T13:09:40

I suspect you have the data types wrong:

tSQL = "INSERT INTO Orders (OrderNumber, OrderDate) " & _
          "Values (" & orderNum & ", #" & orderDate & "#)"

I think your order number is likely to be numeric, so no quotes, and the delimiter for dates is hash (#), not a quote (').

It is nearly always safer to format dates to an unambiguous format:

"Values (" & orderNum & ", #" & Format(orderDate,"yyyy/mm/dd") & "#)"

You cannot set a recordset to an action query, so:

cn.Execute stSQL

Finally, you can save problems with data types, string problems, etc. with parameters.

stSQL = "INSERT INTO table1 (id, adate) " & _
          "Values (?, ?)"

cmd.ActiveConnection = cn
cmd.CommandText = stSQL
cmd.CommandType = adCmdText
cmd.Parameters.Append _
      cmd.CreateParameter("p1", adInteger, adParamInput, , OrderNum)
cmd.Parameters.Append _
      cmd.CreateParameter("p2", adDate, adParamInput, , OrderDate)

cmd.Execute

As an aside, you can insert a range of numbers or a sheet into an Access table with a single query:

INSERT INTO Table1 ( ADate ) 
SELECT SomeDate FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]

Insert Userform data into Access table using excel vba

1 Answers