ADO SQL statment

J

JT

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.
 
M

marbarru

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

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)
 
R

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


Bob Phillips said:
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)



JT said:
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.
 
B

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)



Robin Hammond said:
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


Bob Phillips said:
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)



JT said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top