warnings during import

[Edward Reid]

I'm importing CSV and tab-delimited files into my Access DB. With some
of these imports, I'm getting two warnings that I have to click away.
That's a minor deal when I'm doing the imports manually, but I've just
got it set up to import an entire directory of files at once (using
VBA), and because of these warnings I have to sit here and click them
away. Is there any way to suppress these dialog boxes? I don't see any
help in the TransferText specs.

The first happens when I get most of the way through the import. It
says

: Your computer is out of disk space.
: You won't be able to undo this paste append.
: Do you want to continue anyway?

Well, yeah, duh, I do. Then the second one comes up:

: Microsoft Access was unable to append all the data to the table.
: The contents of fields in 0 records were deleted,
: and 0 records were lost due to key violations.
: etc

Yep, that's right: zero and zero. And indeed it does not create an
error table despite this message.

It's my programs creating the input files (which are downloaded from a
mainframe computer), so if it's something in the file doing this, I can
fix it. I looked for an extra CR or something in the files getting the
error, but found no artefacts.

My DB is currently about 500MB in two tables. There's 45GB available on
the HD. The database is not well normalized -- oh, let's say it isn't
normalized at all. There's only about 20 fields between the two tables,
but one field in each table has to be defined as up to 255 characters
and probably averages about 30; the values in these text fields are not
arbitrary and they should be linked tables. And I realize that even
with normalizing, this DB is going to outgrow Access completely in a
few months if I keep adding data to it (which is not certain to happen;
it's performance data for a mainframe computer and we might terminate
the monitoring), and I'll need to look at probably MySQL.

But in the mean time I'd be happier if I could bypass these clicks.

I had a bunch of other questions ... but answered them by searching the
mpae archives.

Edward

 

[Jeff Boyce]

Edward

Just a note ... the size of an Access database is limited. Your hard drive
might have more room, but that doesn't mean Access can use it. Depending on
version, Access can use up to 2 Gbytes (1 Gb in older versions).

Have you tried creating a new, empty .mdb file and importing all the data &
structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Edward Reid]

Hi Jeff,

I forgot to say that I'm using Access 2002, and the database is in
Access 2002 format.

The DB is currently about 554MB. The files I'm importing are typically
2 to 3 MB. Hmm, I forgot several important points.

I did in fact recreate the DB and start over about a month ago, for
data quality reasons. I started getting these messages quite soon after
I created the new DB, though I don't know exactly when they started.

My guess is that Access is hitting some other limit but showing a
one-size-fits-all message. But I can't guess what limit that would be,
nor how to circumvent the warning.

As I say, I know I'll have to move to some other database software in a
few months, but it doesn't look like I'm even close to the overall size
limits now.

Edward