Insert Into Tbl Error

[Neil]

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.

 

[KARL DEWEY]

The best thing to do is get away from the flat file and use Access as it was
intended, a relational database.

 

[Neil]

Understood Karl, but I do have to resolve the issue. Unfortunately the
company I work with do not have funds to purchase a descent developers
tool for exclusive use. I would rather prefer something else than
Access but unfortunately all I have is Access and VBA to work off of
for now.

 

[KARL DEWEY]

Well I would use Excel to fill in by adding a new column A and using this
formula starting with row 2 after coping B2 to A1 --
=If(B2 ="Y", A1, B2)
Fill column A and then copy and paste special - Values.

Once you have it in Access then fix the "PARTNO1 QTY1 PARTNO2 QTY2
...... PARTNO5 QTY5" by running append queries so the data records look like --

 

[KARL DEWEY]

Like --
Jim Jones 1111 1
Jim Jones 1112 1
Jim Jones 1115 1
Jim Jones 1116 1
Jim Jones 1117 1

 

[Neil]

Thanks for the suggestion Karl. Anyone else have anything to add? I'm
99% done with just 1 small flaw. I dont know if its Access acting weird
as my code runs perfectly for the first few hundred of them.

 

[Neil]

Well after a little deeper research, I see this is a common problem
with Insert Statements. I saw a bunch of threads on using the OrderBy
clause and using Identity Index (a little vague on both of them for
now). Anyways, I'll update here if I find a solution, in the mean time
if anyone finds anything, your help will be much apprecaited.

 

[Neil]

Okay after a day long worth of searching and testing I concluded on a
messy result which gave me results for my initial test. I tested this
with 350 rows in my flat file and it seems to work for now. I made one
of my empty fields in the MOM table as autonumber and made the index
property as None.

Now when the DoCmd.RunSQL statement is ran it at least adds an
auto-incremented number to the field. But the problem still persists.
After getting momentarily excited thinking it worked since the top rows
were in order, I thought I was finished, but again to my surprise the
problem still existed. The advantage I had was that I could use the
auto-incremented number as an index.

By making a new query with the same table structure and necessary
fields, I then transfer the data sorting it out by the Index number
that is created and wolah you get the rows as you want them. I'm going
to keep growing my test file and see if it still works after 600+ lines
as thats where we cap out on most of our files. Hope this helps someone
if they need it.

INSERT INTO MOMFINAL
SELECT ALTNUM, LASTNAME, FIRSTNAME.........
FROM MOM
WHERE CUSTNUM>0
ORDER BY CUSTNUM;