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

From: Jon S.
Sent on: Thursday, October 11, 2012 2:54 PM

Thanks Dan/Cory for your great feedback… excellent!

 

Dan… Is your source code available for that routine?

 

Thanks…jon

 

Jon Seidel, CMC®

Effective Decisions... Priceless!

 

From: [address removed] [mailto:[address removed]] On Behalf Of Dan Stern
Sent: Thursday, October 11,[masked]:57 PM
To: [address removed]
Subject: Re: [Berkeley-Access-Database-Users-Group] Major import problem...

 

I've run into limits importing from text files into SQL Server via MS-Access using docmd.transfer...., so I've built custom VBA routines using ADO that parse each line and write record by record to SQL using ADO. Then I ran into ADO limitations; if Access tries to write too much data before closing and reopening the output recordset, it crashes. So I had to make my routine close and re-open every so often. I think it was around the order of magnitude of data that you're talking about.

- Dan

 

 


From: Cory Nott <[address removed]>
To: [address removed]
Sent: Thursday, October 11,[masked]:18 AM
Subject: RE: [Berkeley-Access-Database-Users-Group] Major import problem...

 

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.

 

Cory

 

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.

 

Cheers…jon

 

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.

 

Cory

 

 

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.

 

Background

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.

 

Problem

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

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed] Description: Description: Image removed by sender.





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Cory Nott ([address removed]) from Berkeley Access Database Users Group.
To learn more about Cory Nott, 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

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed] Description: Image removed by sender.





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

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed] Description: Image removed by sender.





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Cory Nott ([address removed]) from Berkeley Access Database Users Group.
To learn more about Cory Nott, 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

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]

 





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Dan Stern ([address removed]) from Berkeley Access Database Users Group.
To learn more about Dan Stern, 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

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]

This email message originally included an attachment.

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