Importing multiple .xls files to tables at once with code

[Astello]

I need to import about 18 excel files into one big table in Access.
They are all the same format with the same headings, but there are
almost 1 million records. I have tried to import them one at a time,
but after about 8 imports, Access gives me an "Invalid Error" box and
this error message:

Import, 5, Anchor Sheet, C:\CostModel\COLLECTDATA\Collect Shipments -
Anchor 8.xls, Yes,

I have no idea what this means, but after I get this message the first
time nothing will import, not even files that imported without a hitch
before.

I think my only other options are to use a code that imports all of the
files at once (haven't tried it yet, will probably crash) or to put all
of the records into one big text file and import it that way. Any
ideas?

 

[Astello]

It seems that nobody came up with a solution for me, so I'll explain
what I had to do to make this work.
Access had to be repaired, which is where my error message was coming
from. Once I did this, I put the data from all 18 excel files into one
Notepad file (scrappy, I know) with almost 1 million lines. I then
imported this file through "Get external data", and it worked on the
first try. What a relief. I'm pretty sure the problem was repairing
the database, but I guess I'll never know.

 

[aaron.kempf]

use SQL Server and / or DTS/SSIS; Access MDB isn't reliable enough in
handling errors.. importing from Excel