Invalid argument

[Sune Fibaek]

Hi

I have an Access database that is contains some tables that were imported
from an Oracle DB. The tables are updated (well replaced realy) with a very
simple macro running a series of make-table queries. This has worked for some
time, but today I started getting some invalid argument whenever I run the
queries. The tables that are replaced are gone (deleted by the query) from
the DB, but I can't create a new one or re-import it from Oracle. It seems as
if whenever I try to create a new table I get that Invalid Argument error.

What to do? And where to start looking?

Thanks!

/Sune

 

[John W. Vinson]

Hi

I have an Access database that is contains some tables that were imported
from an Oracle DB. The tables are updated (well replaced realy) with a very
simple macro running a series of make-table queries. This has worked for some
time, but today I started getting some invalid argument whenever I run the
queries. The tables that are replaced are gone (deleted by the query) from
the DB, but I can't create a new one or re-import it from Oracle. It seems as
if whenever I try to create a new table I get that Invalid Argument error.

What to do? And where to start looking?

Thanks!

/Sune

First thing to check is the size of the database. Is it approaching or over
the 2 GByte limit on database size? If it's under, try Tools... Database
Utilities... Compact and Repair. Repeated maketable queries bloat your
database badly, since Access doesn't clean up the space occupied by the
deleted tables.

You may want to consider a different approach. Rather than deleting tables and
creating brand new ones, you can either link to the Oracle tables directly
using ODBC (this may or may not be either possible or appropriate though); or
you can have a set of tables in your Access database with the appropriate
field types, sizes, indexes, primary keys, relationships, etc. and run Delete
queries to empty them and Append queries to fill them from your Oracle
database. You'll still need to compact regularly (I'd suggest after every
import session) but the entire process will be more efficient, as will your
resulting database.

John W. Vinson [MVP]

 

[Sune Fibaek]

Hi John,

Thank you for your reply.

First thing to check is the size of the database. Is it approaching or over
the 2 GByte limit on database size? If it's under, try Tools... Database
Utilities... Compact and Repair. Repeated maketable queries bloat your
database badly, since Access doesn't clean up the space occupied by the
deleted tables.

2,095,108 KB so yes, it is getting dangerously close to 2 GB. I was
blisfully unaware of that limit - might have guessed given the limit in the
old MSDE though. I suppose I will have to move this to an SQL server if I
need more space. Will try your solution first though.

You may want to consider a different approach. Rather than deleting tables and
creating brand new ones, you can either link to the Oracle tables directly
using ODBC (this may or may not be either possible or appropriate though); or
you can have a set of tables in your Access database with the appropriate
field types, sizes, indexes, primary keys, relationships, etc. and run Delete
queries to empty them and Append queries to fill them from your Oracle
database. You'll still need to compact regularly (I'd suggest after every
import session) but the entire process will be more efficient, as will your
resulting database.

Linking the tables isn't really an option as it has proved to make the
queries unresonable slow. Unfortunatly this means that I will have to start
over with building th DB. Again.

Again thank you for your most helpful answer!

/Sune