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 · Accepted Answer · 2012-07-27T13:20:37.017

3

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]

edited Jul 27 '12 at 13:20

answered Jul 27 '12 at 13:09

Fionnuala

90,370
7
114
152

I have a project that requires to evaluate MS Access Db, must work with it eventually.. I checked in couple of reliable palces which discussed whether to use ADO or DAO for MS Excel to/from ACCESS DB connections. I am still not convinced fully. Rather a dilema. And I have been using DAO. Appreciate if you could provide any direction, that you think the most suitable :) If I am using C#, VB.Net by all means I use ADO.Net to connect to Access DB. However when it comes to Excel, mostly it's DAO...isn't it? – bonCodigo Mar 08 '13 at 06:49
1

@bonCodigo You will find ADO for Excel all over the place, but working with Access, you are nearly always better off with DAO, no matter where you are coding. DAO generally is much faster. Furthermore, even wotking with Excel, you are less likely to get memory leaks using DAO. – Fionnuala Mar 12 '13 at 11:57
1

@bonCodigo Speed reference http://stackoverflow.com/questions/2986831/ms-access-why-is-adodb-recordset-batchupdate-so-much-slower-than-application-im/2987017#2987017 ; Memory leak references http://support.microsoft.com/kb/319998 and http://stackoverflow.com/questions/13068022/is-it-possible-to-embedded-a-sqlite-database-into-an-excel-2007-file-zip-archiv I have a lot more references, if you need them. BTW, even in C# and VB.Net DAO can be a much better choice. – Fionnuala Mar 12 '13 at 12:00

Insert Userform data into Access table using excel vba

1 Answers1