Access closes (Access 2003 (SP2))

Topic

New Reply

Have a problem with Access 2003 (SP2)
Running code to import a text file.
The code was written because the data in the text file doesn’t follow
standard delimited text rules. The text file is approx 219000 lines long,
producing a table after import of approx 185000 records.

I have been getting the same error which says:
MicroSoft Office Access has encountered a problem and needs to close.
We are sorry for the inconvenience. Then there is the option to send an
error report.

Can’t find anything in the data which points to a problem. On subsequent runs,
it will get to different points in the data before erring out. Originally when
this was encountered, I changed which versions of the built-in functions I used from
MID to MID$, for example. This seemed to cure the problem, but it has now started
to occur again.

Not sure what I am chasing here. Tried it on different machines with various amounts of
RAM: 2G, 2G, 1G, 512M. It has tanked on each of them. I have also migrated the
code into a stand alone database with only a form with code and a single table.
(Table structure shown in attachment). It has also erred out on this one.

Wondering if this is a familiar kind of problem that someone else has experienced,
and/or what suggestions to fix? I can provide code and an sample of the text file
if necessary.

Thanks!
Drew

Reply | Quote

Replies

> I can provide code and an sample of the text file if necessary

Yes, please.

Note: it is not necessary to break lines the way you did – if you don’t, the browser will automatically fit the text to the window size and wrap lines where necessary.

Reply | Quote

Hans,

Here is a stripped version of what we use. Without a huge amount of records in a text file, the error can not be recreated. However you may be able to look at the code and give recommendations to improve performance and lessen the chance of getting an error. Any suggestions are GREATLY appreciated.

Regards,
Drew

Reply | Quote

You apparently don’t require explicit declaration of variables. See post 314,748 for reasons why you should. As a result, several variables are not declared.

In CreateImportLogTable, the variable tdfNew is not set to Nothing at the end.
In GetTextSample, the variable dbs is not set to Nothing at the end.
In OpenFileDialogBox, the variable fDialog is not set to Nothing at the end.
In SetLbx_TargetTable, the variable Tbl is not set to Nothing at the end.
In TargetTableName_GotFocus, the variable dbs is not set to Nothing at the end.

The code goes through a large number of hoops and loops, perhaps you get a stack overflow if you process a very large file. One of the things you could try (in a copy of the database) is to avoid the use of all those little utility functions such as IsOdd and CountZ. As a general programming practice, they are a good idea, but they might hamper performance. For example, instead of

CountZ(MasterRegister(i – 1), TxtQual)

use

Len(MasterRegister(i – 1)) – Len(Replace(MasterRegister(i – 1), TxtQual, “”))

Reply | Quote