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
2010-09-27 19:02:15
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
2010-09-29 19:04:32
Why don't you edit them into your answer with an explanation of why they don't work?
David-W-Fenton
2010-09-30 03:19:23
A:
This sample code assumes a few things:
- You are inserting two values, which are found in A2 and B2.
- The values are both strings
You have a table called TestTable, containing two columns
Public Sub ExportRecord()
ErrRoutine: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
End SubMsgBox Err.Description
LittleBobbyTables
2010-09-27 19:02:31