Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access closes (Access 2003 (SP2))

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access closes (Access 2003 (SP2))

    > 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.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access closes (Access 2003 (SP2))

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access closes (Access 2003 (SP2))

    You apparently don't require explicit declaration of variables. See <post:=314,748>post 314,748</post:> 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, ""))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •