title: Export data from Excel to Access using VBA
body: <p>I have a table in an Excel file with some data, and I want to export these data to my database on Access (in a concrete table on my database called Water Quality) with a VBA code to avoid to open my Database every time that I want to introduce more data on it.</p>
  
  <p>At the moment I have this code but it's not working...</p>
  
  <pre><code>Sub Button14_Click()
  
  ' Macro purpose: To add record to Access database using ADO and SQL
  ' NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
  
  ' Exports data from the active worksheet to a table in an Access database
  
  'Dim cnt As New ADODB.Connection
  'Dim rst As New ADODB.Recordset
  Dim cnt As DAO.Database
  Dim rst As Recordset
  Dim dbPath As String
  Dim tblName As String
  Dim rngColHeads As Range
  Dim rngTblRcds As Range
  Dim colHead As String
  Dim rcdDetail As String
  Dim ch As Integer
  Dim cl As Integer
  Dim notNull As Boolean
  Dim sConnect As String
  
  'Set the string to the path of your database as defined on the worksheet
  dbPath = "C:\Documents and Settings\Administrador\Mis documentos\MonEAU\modelEAU Database V.2.accdb"
  tblName = "Water Quality"
  Set rngColHeads = ActiveSheet.Range("tblHeadings")
  Set rngTblRcds = ActiveSheet.Range("tblRecords")
  
  'Set the database connection string here
  sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" &amp; dbPath &amp; "';"     'For use with *.accdb files
  ' sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; dbPath &amp; ";"     'For use with *.mdb files
  
  'Concatenate a string with the names of the column headings
  colHead = " ("
  For ch = 1 To rngColHeads.Count
      colHead = colHead &amp; rngColHeads.Columns(ch).Value
      Select Case ch
          Case Is = rngColHeads.Count
              colHead = colHead &amp; ")"
          Case Else
              colHead = colHead &amp; ","
      End Select
  Next ch
  
  'Open connection to the database
  cnt.Open sConnect
  'Begin transaction processing
  On Error GoTo EndUpdate
  cnt.BeginTrans
  
  'Insert records into database from worksheet table
  For cl = 1 To rngTblRcds.Rows.Count
      'Assume record is completely Null, and open record string for concatenation
      notNull = False
      rcdDetail = "('"
  
      'Evaluate field in the record
      For ch = 1 To rngColHeads.Count
          Select Case rngTblRcds.Rows(cl).Columns(ch).Value
  
                  'if empty, append value of null to string
              Case Is = Empty
                  Select Case ch
                      Case Is = rngColHeads.Count
                          rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) &amp; "NULL)"
                      Case Else
                          rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) &amp; "NULL,'"
                  End Select
  
                  'if not empty, set notNull to true, and append value to string
              Case Else
                  notNull = True
                  Select Case ch
                      Case Is = rngColHeads.Count
                          rcdDetail = rcdDetail &amp; rngTblRcds.Rows(cl).Columns(ch).Value &amp; "')"
                      Case Else
                          rcdDetail = rcdDetail &amp; rngTblRcds.Rows(cl).Columns(ch).Value &amp; "','"
                  End Select
          End Select
      Next ch
  
      'If record consists of only Null values, do not insert it to table, otherwise
      'insert the record
      Select Case notNull
          Case Is = True
              rst.Open "INSERT INTO " &amp; tblName &amp; colHead &amp; " VALUES " &amp; rcdDetail, cnt
          Case Is = False
              'do not insert record
      End Select
  Next cl
  
  EndUpdate:
  'Check if error was encounted
  If Err.Number &lt;&gt; 0 Then
      'Error encountered.  Rollback transaction and inform user
      On Error Resume Next
      cnt.RollbackTrans
      MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
  Else
      On Error Resume Next
      cnt.CommitTrans
  End If
  
  'Close the ADO objects
  cnt.Close
  Set rst = Nothing
  Set cnt = Nothing
  On Error GoTo 0
  
  End Sub
  </code></pre>
  
  <p>At the moment, the problem is when I debug the code, there appears the compiling error: "Method or data member not found" on the function "cnt.Open sConnect".</p>
  
  <p>If this is possible, any help would be greatly appreciated.</p>
  
  <p>Note: I'm using Office 2010.</p>