Turning off import errors

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

Hello all,

I have a tab-delimited file where the first 4 fields are data that is specific to a single account. Fields 5 thru 20+ are the
account balances at month end through a time series of many months. The problem I'm having is if there is no balance in a month,
instead of having a zero, there is a single decimal point in the text file. Well Access' import wiz can't convert a single decimal
point to a double data type and nulls out that month for that account.

I don't care if it nulls out that piece of data, that is fine. But I don't want Access to create an Import Errors table when it
does this. This can create a pretty big Import Errors table because most records have multiple months with a single decimal point.

Is there any way I can shut off the "Create Import Errors Table" feature of Access?

Thanks for any help anyone can provide,

Conan Kelly
 
IF you are using a macro to do the importing , then create a delete qry for
the table and it will then delete after you run the macro
 
Nadine,

Thanks for the feedback.

There is not enough files being imported to use a macro. There are only 3 files being imported. Yeah, I already have them
imported, but every quarter there will be 3 files that will need to be imported. So I would still like to know for future
reference.

The other problem with your solution is that it will take computer resources to create this table and insert rows describing the
errors. I'm guessing that this will make my Import process take longer. I'm also guessing that it will increase the .mdb file size
so this data that will end up being deleted will be written to disk. I would like to Compact the DB as little as possible, it gets
to be kind of a hassle when the DB is pretty big.

Thanks again for all of your help,

Conan
 
What you need to do is to import the files into a "temporary" table where
the fields are defined as text type. Then use an append query to copy the
data to the permanent table; that append query can be used to "clean up" the
data (e.g, convert a "." value to a zero, etc.) as part of its work.

As for compacting a database, that is a task that needs to be done routinely
for any ACCESS file where data are being added/edited/deleted, or where
objects are being created/edited/deleted. An ACCESS database file will
continue to "grow" in size otherwise and eventually will get too big to be
compacted.
--

Ken Snell
<MS ACCESS MVP>
 
Conan,
Within access you can get the dbase to compact on close by using the action
tools ,options, general and tick compact on close.
 
Back
Top