ADO SQL statment

[Guest]

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.

 

[marbarru]

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

 

[Bob Phillips]

Inserting a row statement is like this

sSQL = "INSERT INTO Contacts " & _
" (FirstName, LastName,Phone, Notes) " & _
"VALUES ('Bob','Phillips','01202 345678','me')"

AFAIAA, you can't insert multiples at once into Access.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Robin Hammond]

Bob,

Not tested in Access, but with SQL you can get an empty DISCONNECTED
recordset (SELECT TOP 0 FROM MyTable), add your records to the rs,
reconnect, then do an UpdateBatch.

Robin Hammond
www.enhanceddatasystems.com

Inserting a row statement is like this

sSQL = "INSERT INTO Contacts " & _
" (FirstName, LastName,Phone, Notes) " & _
"VALUES ('Bob','Phillips','01202 345678','me')"

AFAIAA, you can't insert multiples at once into Access.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

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.

 

[Bob Phillips]

Hi Robin,

That is so, but I don't think that Access has that capability, or any such
capability.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

Not tested in Access, but with SQL you can get an empty DISCONNECTED
recordset (SELECT TOP 0 FROM MyTable), add your records to the rs,
reconnect, then do an UpdateBatch.

Robin Hammond
www.enhanceddatasystems.com

Inserting a row statement is like this

sSQL = "INSERT INTO Contacts " & _
" (FirstName, LastName,Phone, Notes) " & _
"VALUES ('Bob','Phillips','01202 345678','me')"

AFAIAA, you can't insert multiples at once into Access.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

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.