Have you ever try the Access wizard to import data from excel
sheets... maybe it works for you!

On Feb 6, 1:04 pm, JT wrote:
I'm trying to modify the code below. I have an Excel sheet with Journal
Voucher data that I want to add to a table in an Access Database. Each line
in the spreadsheet will be a new record in the Access table.

The Journal Voucher data has 12 fields, (Cust Num, Doc, Unit, DR Acct, DR
BR, DR Amt, Cr Acct, CR BR, CR Amt, Descr, Vend Num, Date, Time). I have 3
questions:

(1) What is the correct syntax to add a new record to Access with mupltiple
fields (having trouble with the correct syntax)?

(2) Is there a way to do all of the lines at once instead of one at a time?

(3) Is this the way to go or is it better to start an Access application and
use the acimport command?

Dim Recordset As ADODB.Recordset
Set Recordset = New ADODB.Recordset

vDataBase = ActiveWorkbook.Sheets("Macros").Range("E6")
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" &
vDataBase & "';Persist Security Info=False"

Dim r As Integer
r = 5

Do Until Len(Cells(r, 1)) = 0

Sql = "append [JV_Info] set [Customer Number] = '" & Cells(r, 1) & "'

Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly,
adLockReadOnly, CommandTypeEnum.adCmdText)

r = r + 1

Loop

Thanks for all of the help......Any suggestions or code on how to accomplish
this would be greatly appreciated.
--
JT