addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscrossdots-three-verticaleditemptyheartexporteye-with-lineeyefacebookfolderfullheartglobegmailgooglegroupsimageimagesinstagramlinklocation-pinm-swarmSearchmailmessagesminusmoremuplabelShape 3 + Rectangle 1outlookpersonJoin Group on CardStartprice-ribbonImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruseryahoo

Re: [Berkeley-Access-Database-Users-Group] Major import problem...

From: Jon S.
Sent on: Thursday, October 11, 2012 1:35 PM
Yep; custom import spec which defines all fields as simple text; validation & field tyoes done after basic data imported into access/SQL.

My money is on bad data; we've had issues with this app before.


Sent from my iPhone

On Oct 11, 2012, at 10:18 AM, Cory Nott <[address removed]> wrote:

My experience with imports has been that the post processing is usually not the problem. Once Access has the table and can read end to end, it won’t choke on anything except poorly constructed queries. If the file won’t import, or is not fully accessible, then it’s likely to be a problem in the ISAM (the libraries that run the methods for importing different, non-database files), perhaps running out of memory or encountering unexpected data. Another fun fact about Access is that it will only read the first few rows of a data file to determine the data types of the columns. If you aren’t using an import specification, a data column that Access thinks is numbers  but really is text later on could cause a failure.


Check your debug error. If it’s “Error in Installable ISAM”, you have an issue with either the size, the accessibility (as you point out below), or unexpected/corrupted data in the file.




From: [address removed] [mailto:[address removed]] On Behalf Of Jon Seidel
Sent: Thursday, October 11,[masked]:52 AM
To: [address removed]
Subject: RE: [Berkeley-Access-Database-Users-Group] Major import problem...


Thanks, Cory… The complete process requires  several steps (e.g., delete the old SQL import table; import the new data into a temp SQL table using DoCmd.transferSpreadsheet with a custom import specification; add processing date to temp table; find/copy error records to an exception table; delete the error records from the import table; delete any records with the new transaction date from the production table; append the imported data from the temp table to the production table) <whew!>


I use this same approach for the other three (smaller) import files which works just fine.


I did notice that the SQL database was defined with the option to increase size when needed by 1MB (total size = 30Gig right now), so increased that to a 5% growth factor on the guess that maybe doing several expansions during the import might cause sufficient delays to result in the problem.




Jon Seidel, CMC®

Effective Decisions... Priceless!


From: [address removed] [mailto:[address removed]] On Behalf Of Cory Nott
Sent: Thursday, October 11,[masked]:48 AM
To: [address removed]
Subject: RE: [Berkeley-Access-Database-Users-Group] Major import problem...


Another thing you can try is linking the file and going through it record by record (automated, of course) and seeing where it breaks.





From: [address removed] [mailto:[address removed]] On Behalf Of Jon Seidel
Sent: Thursday, October 11,[masked]:50 AM
To: [address removed]
Subject: [Berkeley-Access-Database-Users-Group] Major import problem...


I’ve got a problem that maybe someone can help me with.



The application uses Access 2010 as a front end to a SQL Server 2008 back end. My app gets its information by importing data from another, purchased application that does the first chunk of work (payroll). We have to do imports because, although the other app also uses SQL Server, we are not allowed to get data directly from their database.


The payroll app exports data as tab-delimited text files [FYI, I really like this format because – unlike the CSV format -- there is no confusion as to which fields are which] which are then imported into my app with custom code and validations/error reports.



There are four files that are imported each week; three of them work just fine. The fourth one – the largest, something like 20MB – hangs / winds up throwing a debug error. The other three files are only 2-4MB. I can’t see anything wrong with the code… the size is the only difference I can see.


Has anyone run into a situation like this and have any pointers to a solution? It’s a REAL problem for my client.


Thanks much…jon


Jon Seidel, CMC®

Effective Decisions... Priceless!


Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Jon Seidel ([address removed]) from Berkeley Access Database Users Group.
To learn more about Jon Seidel, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages


Our Sponsors

  • FMS, Inc.

    World Class Access Add-In Software

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy