Importing text file becomes unresponsive

[nick243243]

I am using cmd transfer text file to import a notepad file. When I run the process that normally works with some errors. We have had two recent files that when imported causes access to become unresponsive half way through the import process. Is there a way to better handle this?

'** Step 1 -- Define where and what naming sequence the files are.
ChDir ("K:\Departments\Support Services\#System-Output\")
strfile = Dir("K:\Departments\Support Services\#System-Output\SPO152TICREQ*.TXT")


Do While Len(strfile) > 0
DoCmd.SetWarnings False
DoCmd.OpenQuery "ClearInitial"

DoCmd.TransferText acImportDelim, "SPO152TICREQImport Specification", "SPOTicImport", "K:\Departments\Support Services\#System-Output\" & strfile, False
On Error GoTo Err_ShopAtHome
FileCopy "K:\Departments\Support Services\#System-Output\" & strfile, "K:\Departments\Support Services\#System-Output\History\" & strfile
Kill "K:\Departments\Support Services\#System-Output\" & strfile

DoCmd.OpenQuery "CaptureTicReqs"
DoCmd.OpenQuery "Capture_PO_Color"
DoCmd.OpenQuery "PO_CountOf_Color"
DoCmd.OpenQuery "Update_CountOfColors"
strfile = Dir
Loop
DoCmd.SetWarnings False
DoCmd.OpenQuery "UpdateVendorName"
Forms![MainFrm]![MainSub].Requery
DoCmd.SetWarnings True
'Call VerifyImportErrorTables

Err_ShopAtHome:


End Function

.

Thank You,
Nick

 

[NauticalGent]

Wow...lots of activity there. Without seeing what the queries are doing, I can't begin to guess what is going on and why Access becomes unresponsive.

I would try the file transfer by itself and if everything goes well, try the queries individually and see where it breaks down. For the time being, leave your warnings on until you get it sorted out.

You may want to consider Currentdb.Execute instead of OpenQuery.

Welcome to the forum BTW.

 

[missinglinq]

The first thing you need to do here, I would suspect, is to remove this line:

DoCmd.SetWarnings False

so that you might be able to get an error message when the problem occurs, giving you some idea of hat the problem is.

On the chance that this might be a timing problem, you might also want to use DoEvents between the opening of each Query, to give one time to complete opening before attempting to open the next one:

DoCmd.OpenQuery "CaptureTicReqs"
[B]DoEvents[/B]
DoCmd.OpenQuery "Capture_PO_Color"
[B]DoEvents[/B]
DoCmd.OpenQuery "PO_CountOf_Color"
[B]DoEvents[/B]
DoCmd.OpenQuery "Update_CountOfColors"

Linq ;0)>

 

[NauticalGent]

You bring up an interesting point missinglinq. One of my apps runs three update queries, one right after the other.

I haven't had any issues (so far), but I like your suggestion regarding DoEvents. Thanks for the tip...

 

[nick243243]

Ok I turned on the warnings and found that it freezes when it executes the docmd transfer text line. It starts to execute it but freezes about half way through the process. Thanks for the replies.

 

[nick243243]

It also looks like I miss copied some code in there where I started to create an error handler under docmd transfer text and the line at the bottom Err_shopathome. Act like those 2 lines are not there.