Problem with limit of DoCmd.RunSQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Otto Miros - 30 Nov 2005 15:45 GMT

Hi,
I use the command

DoCmd.RumSQL (select * into tempTB from table1)

in VBA.

The command runs fine but only insert 199 items. (tabel1 has 3000 items)

How can I run the command, so that all 3000 items are insert in tempTB.

Thanks
Otto

Reply to this Message

Klatuu - 30 Nov 2005 16:18 GMT

There are 3000 rows in table1 and only 199 are getting into tempTB? How
strange. This is not, however AFAIK, a problem with the RunSQL (It is
mispelled in the post, by the way). Are there validation rules in tempTB
that may be excluding records. Also, I would suggest you use:
CurrentDb.Execute("select * into tempTB from table1;") instead. You will be
suprised at how much faster it is.

> Hi,
> I use the command
[quoted text clipped - 9 lines]
> Thanks
> Otto

Reply to this Message

Otto Miros - 30 Nov 2005 21:19 GMT

You are right the statement CurrentDB.Execute(..) is much faster, but the
result is the same-
I do not use any validation rules.

The statement is very simple, just select * into table2 from table1

The result is alway 199 Rows.

Any ideas?

Thanks Otto

> There are 3000 rows in table1 and only 199 are getting into tempTB? How
> strange. This is not, however AFAIK, a problem with the RunSQL (It is
[quoted text clipped - 17 lines]
>> Thanks
>> Otto

Reply to this Message

Klatuu - 30 Nov 2005 21:51 GMT

I mean validation rules on table2 either at the table or field levels. Also,
I would carefully examine table1. There may be some data corruption or some
other problem with it. If a SELECT INTO statement were limited to 199 rows,
SQL would be worthless. There is some other problem you have not yet
identified.

> You are right the statement CurrentDB.Execute(..) is much faster, but the
> result is the same-
[quoted text clipped - 29 lines]
> >> Thanks
> >> Otto

Reply to this Message