views:

32

answers:

2

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.

A: 

Of course, I couldn't find anything on the web before, then I found three answers on the web within five minutes of posting my question.....

Bill_B
Thanks to LittleBobbyTables for the only useful answer I have ever found to this question. The other answers that I found on the web were all crap.
Bill_B
Why don't you edit them into your answer with an explanation of why they don't work?
David-W-Fenton
A: 

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
LittleBobbyTables