Upload Excel data to Access error

[Andrew]

I have a VBA script that loops through a large number of text files
(over 100), opens each in Excel, uploads the file info (file name,
date,...) to "UploadedFiles" table in Access, and uploads the contents
of the file into another table ("APC") in the same Access db. The
tables are connected with referential integrity.
When I do a lot at once, I usually get an error at a random point (>30
files in) when connecting to the database for UploadedFiles (the last
line):

Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & AccessLocation

I have Excel and Access 2010 and the reference for Microsoft DAO 3.6.

This always happens when I am connecting to UploadedFiles, and not to
APC, even though I connect to APC in the exact same way. (Although I
do sometimes get errors when I am in the middle of uploading an APC
file - but not connecting.) When I close and restart Excel, I am able
to continue.

I also have a query in the Excel sheet that lists some info about the
UploadedFiles table that I use to determine which files need uploaded.
After I get the error and I tried refreshing this table and Excel gave
me an error that says: "...there is not enough temporary storage space
on the disk to store the query result." But the query result is only
one row.

I have noticed that I think Compacting and Repairing the database
helps. Also, I am pretty sure that it takes longer to get an error if
I create a new table and/or db and upload to that.

Thanks

 

[GS]

I have a VBA script that loops through a large number of text files
(over 100), opens each in Excel, uploads the file info (file name,
date,...) to "UploadedFiles" table in Access, and uploads the contents
of the file into another table ("APC") in the same Access db. The
tables are connected with referential integrity.
When I do a lot at once, I usually get an error at a random point (>30
files in) when connecting to the database for UploadedFiles (the last
line):

Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & AccessLocation

I have Excel and Access 2010 and the reference for Microsoft DAO 3.6.

This always happens when I am connecting to UploadedFiles, and not to
APC, even though I connect to APC in the exact same way. (Although I
do sometimes get errors when I am in the middle of uploading an APC
file - but not connecting.) When I close and restart Excel, I am able
to continue.

I also have a query in the Excel sheet that lists some info about the
UploadedFiles table that I use to determine which files need uploaded.
After I get the error and I tried refreshing this table and Excel gave
me an error that says: "...there is not enough temporary storage space
on the disk to store the query result." But the query result is only
one row.

I have noticed that I think Compacting and Repairing the database
helps. Also, I am pretty sure that it takes longer to get an error if
I create a new table and/or db and upload to that.

Thanks

First thing I see here is that you are setting a ref to ADODB, stating
you have set a project ref to Microsoft DAO 3.6; -this is not ADO!