Insert Into Tbl Error

Hello everyone,

I have yet another Insert Into statement error which might be
relatively easy to solve. Here's the problem:

I have a working VBA module which includes various SQL Insert Into
statments. Our system has a limit of taking in only 5 PRODUCT codes and
responding quantities in one row. So if the customer ordered 7 items,
the next two will have to go in second row but for the same customer.
It looks something like this:

BILL_NAME CONTINUED PARTNO1 QTY1 PARTNO2 QTY2 ..... PARTNO5 QTY5
JIM JONES N 1111 1 1112 1 1115 1
Y 1116 1 1117 1

The continued field if marked Y tells the system that the following 5
items will be at going under the same customer. There are other rules
to follow which is why i have to use a statement which inserts Values
into the specific field in the table rather than just doing Select
Bill_Name, Address, PARTNO1, ..., PARTNO5 FROM tblXXXXX. The following
statement does what it needs to flawlessly:

DoCmd.RunSQL "INSERT INTO MOM(ALTNUM, CONTINUED, PRODUCT01,
PRODUCT02,......) VALUES ('" & strAltNum & "','" & strContinued4 &
"','" & rs("PARTNO6") & "','" & rs("PARTNO7") & "','" ......).

As you can imagine it is imperative that the statement with Y goes in
the table in order rather than ending up in the top or middle of the
list somewhere.

Now to the delimma. For some reason Access freaks out if the flat file
has more than 300 records to process. What it does is for the first 200
or so customers, it inserts the Y statement in order as it is being
inserted but once in a while it will insert the Y statement in the
middle of the list. What happens is when i export this table to a flat
file to feed it to our system, obviously it does not export it
correctly. I hope I'm making some sense here for some of you experts. I
dont know if the insert statement follows some rule of sorting while
inserting as it works fine in the earlier stages of inserting. Please
advice.

.

Relevant Pages

  • Re: daMyDatatable.Update fails for linked dataset tables (using Ol
    ... example you should see that the Customer update occurs before the Orders ... > You can't use a CommandBuilder if you have a join in the Sql Statement. ... >> I have set-up a component class to contain the example. ... >> inserting every set of parent-child rows. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Linking tasks between projects?
    ... Thank you both..I will give this information to my customer. ... > RoseC wrote: ... > inserting a task into the project before the sucessor in a ...
    (microsoft.public.project)
  • Re: Checking existing data
    ... > I' d like to check data before inserting. ... optimizer avoid a table/index scan. ... INSERT INTO Customer ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Checking existing data
    ... If a company PEUGEOT is already stored in the DB, ... "PEUGEOT SA" from being inserting. ... > INSERT INTO Customer ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Falt File - problems with file format
    ... Looking at your flat file structure, I don't see anything in the instance ... > PhoneNumber) are easy to map and that's why not very interesting. ... For the first customer CustomerName1, ... >>>>and the second scheme: ...
    (microsoft.public.biztalk.general)