Save Excel 2003 worksheet into an Access 2003 table

Question

I'm trying to write a macro to export a single row from a single worksheet from an Excel 2003 workbook to a new row in an Access 2003 table. I'm new to VBA, and everything I've found on the web refers to going the other way -- from Access to Excel. I want this to be an export, not a link, and I don't care about keeping them synced after the export.

Answer 1

This sample code assumes a few things:

1. You are inserting two values, which are found in A2 and B2.
2. The values are both strings

3. You have a table called TestTable, containing two columns

   Public Sub ExportRecord()
   
   
   On Error GoTo ErrRoutine
   
   
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   
   
   conn.Open "Your_DSN_Goes_Here", "userId", "pwd"
   
   
   Set rs = New ADODB.Recordset
   rs.Open "TestTable", conn, adOpenDynamic, adLockOptimistic, adCmdTable
   
   
   rs.AddNew
   
   
   rs.Fields("Col1") = CStr(Sheet1.Cells(2, 1))
   rs.Fields("Col2") = CStr(Sheet1.Cells(2, 2))
   
   
   rs.Update
   
   
   Set rs = Nothing
   Set conn = Nothing
   
   
   Exit Sub
   
   
   ErrRoutine:
   
   
   MsgBox Err.Description
   
   
   End Sub