Block Inserts

[rhnewfie]

I have done block inserts with large dbms but I have not been able to get it to work with access! Can you do block inserts in access and if so how?

Thanks in Advance
RHNewfie There are 3 Types of People in the World
Those Born to Think Logically
Those that can Learn to Think Logically
Those that Shouldn't Try

 

[rhnewfie]

I want to be able to do the following:

Insert into tablename values(1,2,3)
values(4,5,6) etc... There are 3 Types of People in the World
Those Born to Think Logically
Those that can Learn to Think Logically
Those that Shouldn't Try

 

[tlbroadbent]

The syntax you propose is invalid. You can do the following.

Insert into tablename values(1,2,3)
Insert into tablename values(4,5,6)
Insert into tablename values(7,8,9)
Insert into tablename values(10,11,12)
.
.
.
Put as many inserts as you want into a single batch. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[rhnewfie]

Terry

In DB2 and Oracle you can do the following

Insert into tablename values((1,2,3),(4,5,6),(7,8,9))

and that would insert 3 rows into the table. I think you should be able to do that in access but I can't get it to work.

Thanks Again
RHNewfie There are 3 Types of People in the World
Those Born to Think Logically
Those that can Learn to Think Logically
Those that Shouldn't Try

 

[MichaelRed]

" ... I think you should ... "in WHAT group? Perhaps you would at least consider Terry's suggestion?

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rhnewfie]

Michael

I have considered the suggestion that Terry made, in fact, that is how I am doing it now, but access is a real bottle-neck to the application. I am retrieving an initial recordset from DB2 with an average size of 450 records, then I have to go back to DB2 to get additional info for each record in the first recordset and populate an access table containing 60 fields. They way the DBA's set up the DB2 table structure there isn't always a foreign key so I have to do initial inserts and then multiple updates in access. Thus, I now populate an array (equivalent to the access table) and then insert everything, but it takes a long time to do the inserts and I'd like to find a faster way.

RHNewfie

There are 3 Types of People in the World
Those Born to Think Logically
Those that can Learn to Think Logically
Those that Shouldn't Try

 

[MichaelRed]

rhnewfie,

Asking for help in a process is different than 'telling' a helpful user what a 'shrink wrap' app "should" do?

For the type of help you are asking for, the entire app/process would need to be laid out in quite some detail. I'm not sure Tek-Tips is really the appropiate place to have that much 'dialog'. I'm sure that I would not want to do such a project via Tek-Tips public forums.

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rhnewfie]

As a matter of fact I was asking for help with a 'process' rather than telling anyone what a 'shrink-wrap' app should do. Have you never asked a question that began with: "I SHOULD be able to...". Unfortunately, tone-of-voice is not something that is easily conveyed here. My apologies if my post rubbed you or anyone the wrong way, it was not my intent to offend. I am very pleased with information that I obtain from this forum and you are right, my entire app would be too complicated to lay out here, not that it would help. I will ensure that my posts appear in true question format in the future.

RHNewfie

There are 3 Types of People in the World
Those Born to Think Logically
Those that can Learn to Think Logically
Those that Shouldn't Try