Over Night Error

[Dirtrider929]

I have a program that builds a text file from a query and places it on the network for download. There are other users who have a different application that when a button is pressed they pull in the text file and it automatically imports the txt file into a table.

Well overnight this process does not work anymore. I have concluded, IMO, that the problem exists with how the text files are compiled now. Does anyone know of any changes that could be affecting the process?

Here is the code to build the txt files:

If fs.FileExists(strAppInfoNetworkDBLocation + "CSM Download Items\SWSItems.txt") Then
Kill (strAppInfoNetworkDBLocation + "CSM Download Items\SWSItems.txt")
End If

If fs.FileExists(strAppInfoNetworkDBLocation + "CSM Download Items\ChainItems.txt") Then
Kill (strAppInfoNetworkDBLocation + "CSM Download Items\ChainItems.txt")
End If

Me.lblSaleRep.Caption = "Exporting SWS Items ..."
Me.lblProgress.Caption = ""
Me.Repaint
DoCmd.TransferText acExportDelim, , "qryBuildSWSItemsTxt", strAppInfoNetworkDBLocation + "CSM Download Items\" + "SWSItems.txt", True

Me.lblSaleRep.Caption = "Exporting Chain Items ..."
Me.Repaint
DoCmd.TransferText acExportDelim, , "qryBuildChainItemsTxt", strAppInfoNetworkDBLocation + "CSM Download Items\" + "ChainItems.txt", True

Set fs = Nothing
DoCmd.SetWarnings True

Me.lblSaleRep.Caption = "Process complete!"
Me.Repaint

And here is the code for the import:

Set dbExecute = CurrentDb()

On Error GoTo ErrImports:

If strSWSItems = "Yes" Then
Me.lblSyncProgress.Caption = "Importing new SWS Items"
Me.Repaint

dbExecute.Execute "Delete * from tblSWSItems"
dbExecute.Close

DoCmd.TransferText acImportDelim, , "tblSWSItems", CurrentProject.Path + "\CSMData\SWSItems.txt", True

End If

Set dbExecute = CurrentDb()

If strChainItems = "Yes" Then
Me.lblSyncProgress.Caption = "Importing new Chain Items"
Me.Repaint

dbExecute.Execute "Delete * from tblChainItems"
dbExecute.Close

DoCmd.TransferText acImportDelim, , "tblChainItems", CurrentProject.Path + "\CSMData\ChainItems.txt", True

End If


fnRefreshTableLinks

 

[spikepl]

Define "does not work", no interpretations but symptoms. And what checks have you undertaken.

We only have the info you provide, but no magic wand nor any remote vision equipment!

 

[Ranman256]

I notice you do not use a SPEC name on export NOR import. this tells the query how to assemble the text file.
Does the fact that you do not use a specification file to define your output affect the file?

(its why i never use text files, since everyone has Excel)

 

[Dirtrider929]

The files compile and are placed in the designated location correctly by the job on the network. When a remote used "Syncs" their application to the server copies of the files are downloaded to the local client. At the end of the "Sync" the local client application imports the text files to a local table. In import process the following error pops up. Error message attached.

I tried looking at the code to see if someone else accidentally changed something by comparing to a backup copies of the code in both applications. Then I replaced the newly compiled text files with older versions, compiled on the 7th of this month, and the application uploaded the files just fine.

Yesterday I did have to fix some of the Access Object Library references in the job that compiles the text files as they were "Missing" and are now 15.0.

 

[Dirtrider929]

I do not know that defining the SPEC would be the issue since it worked for the last 4 years until today, that is unless the object library updates changed that.

 

[winshent]

What happens when you run this yourself on a test environment, changing the output paths etc...

By "Overnight" are you implying this is a triggered batch job somehow called from a scheduler or is it being run by a user?

Does the user/ account running this have permissions to write to the output directory ?

I'd suggest writing to a log file each step to give you more detailed information on what is going on.

 

[CJ_London]

your attached error message is not a standard access message, it is something generated by your code - so look at the actual error generated to give you a clue.

You haven't said whether the files have actually been generated and if so are they correct?

You also haven't said whether the error applies to both files or only one

you haven't said whether you have checked the relevant queries to produce the right columns in the right order with the right names

you haven't said whether you have checked the data for nulls or inappropriate datatypes

If SPEC is not declared the default is used - you say you had to update libraries which implies you have moved to Access 2013 from an earlier version - tho' I don't think it is likely but perhaps this has resulted in a change to the default. Also are your users also updated to use the later libraries.

 

[vbaInet]

your attached error message is not a standard access message, it is something generated by your code

And a poor one at that. It's non-descriptive. Whoever developed it didn't use the error numbers to give a more descriptive error message.

As an aside, I don't know how fast your network is and even if it was fast, I would still create the file locally first, then copy the newly built local file across the network.

DoCmd.TransferText acExportDelim, , "qryBuildChainItemsTxt", [COLOR="Blue"]strAppInfoNetworkDBLocation [/COLOR]+ "CSM Download Items\" + "ChainItems.txt", True

Local location instead.

 

[Dirtrider929]

What happens when you run this yourself on a test environment, changing the output paths etc...

By "Overnight" are you implying this is a triggered batch job somehow called from a scheduler or is it being run by a user?

Does the user/ account running this have permissions to write to the output directory ?

I'd suggest writing to a log file each step to give you more detailed information on what is going on.

Yes the implied queries are triggered by a batch job and called from the scheduler and the User/Account has the appropriate permissions.

The job and output are working properly. The files are created and placed in the proper location.

 

[Dirtrider929]

your attached error message is not a standard access message, it is something generated by your code - so look at the actual error generated to give you a clue.

You haven't said whether the files have actually been generated and if so are they correct?

You also haven't said whether the error applies to both files or only one

you haven't said whether you have checked the relevant queries to produce the right columns in the right order with the right names

you haven't said whether you have checked the data for nulls or inappropriate datatypes

If SPEC is not declared the default is used - you say you had to update libraries which implies you have moved to Access 2013 from an earlier version - tho' I don't think it is likely but perhaps this has resulted in a change to the default. Also are your users also updated to use the later libraries.

I looked at the error message and it is as confusing as the message itself.

Yes both files get created and placed in the proper location.

I verified that the output of data and everything appears to be in the same delimited format as the old file. I then checked the order in which the file is imported, once downloaded by users, and the import to order and format are correct.

I first suspected that the error resided in the 2013 update but the server that the database resides is still on 2010. I did notice that when I went back into the job just yesterday that the Object Libraries were missing 14.0 not 15.0 this time.

The server and all the users will be updated to 2013 by August 1st. I turned off the code to update the files causing the current error and once everyone is updated to 2013 I will see if that solves the issue.

 

[Dirtrider929]

And a poor one at that. It's non-descriptive. Whoever developed it didn't use the error numbers to give a more descriptive error message.

As an aside, I don't know how fast your network is and even if it was fast, I would still create the file locally first, then copy the newly built local file across the network.

DoCmd.TransferText acExportDelim, , "qryBuildChainItemsTxt", [COLOR="Blue"]strAppInfoNetworkDBLocation [/COLOR]+ "CSM Download Items\" + "ChainItems.txt", True

Local location instead.[/QUOTE

My sentiment exactly about the error message. I am going through and cleaning up the non-descriptive error messages as much as I can.

The database and job are local on the network where the files are being placed. All of the files and DB's are in one VM on the network. Your suggestion would be to have the files created locally in my environment then copied to the network? What is the difference?

 

[vbaInet]

Good luck with that

Again, I don't know how good your network is, this is just some food for thought.
The process of creating a pdf file over a network is more resource intensive than doing it locally and in the case of the latter, you're more sure of the integrity of the file. Access acts funny in a network with low bandwidth. If you want to free your network (a very tiny amount) then I would do it as suggested. Otherwise, if you're not too bothered about all that, your current method is equally as good.