How do I run update Queries before exporting table as text file in VBA

[GirthJohnson]

I'm building an automated report and need to be able to update a table with update queries before exporting the text files through my module. I'm still a bit of a noob when it comes to VBA and I've listed my code below at the bottom of this post.

The queries work as planned when ran on test tables seperately but I'm not sure how or where to call them in the VBA module. I tried setting the queries up as public functions and calling them in the export public function but the exported text file still has the wrong format. However, the DET Table is updated by the queries.

How do I get the updated DET table to export after the queries run?

Also, how do I get rid of all the warning pop-ups, i.e. "You're about to change 5497 records. Do you want..."

'This query updates the DET table and replaces "000000000000" with "0.00"
Public Function qryReplaceAllZeros()
 
DoCmd.OpenQuery "qryReplaceAllZeros", acViewNormal, acEdit
 
End Function
 
'This query updates the DET table and replaces all leading zeros
Public Function qryRemoveLeadingZeros()
 
DoCmd.OpenQuery "qryRemoveLeadingZeros", acViewNormal, acEdit
 
End Function
 
 
Public Function CreateTextFile()
'This function creates a fixed-width text file using the Orders table
'in CDA Fixed File Import tool. The CHRGAMT field will be Right-aligned
 
 
 Dim strRECTYPE As String * 4 'specifies width of 4 characters
 Dim strPROVNUM As String * 20  'specifies width of 20 characters
 Dim strPCN As String * 50    'specifies width of 50 characters
 Dim strCHRGCODE As String * 12 'specifies width of 12 characters
 Dim strFILLER1 As String * 18 'specifies width of 18 characters
 Dim strCHRQTY As String * 7 'specifies width of 7 characters
 Dim strCHRGAMT As String * 15 'specifies width of 15 characters
 Dim strSRVCDATE As String * 8 'specifies width of 8 characters
 Dim strPROC As String * 8 'specifies width of 8 characters
 Dim strORDERMD As String * 22  'specifies width of 22 characters
 Dim strORDERMDTYPE As String * 2    'specifies width of 2 characters
 Dim strFILLER2 As String * 34 'specifies width of 34 characters
 Dim var1 As Variant
 Dim result As String
 Dim mydb As DAO.Database, myset As DAO.Recordset
 Dim intFile As Integer
 
 
 var1 = ""
 
 
 Set mydb = CurrentDb()
 Set myset = mydb.OpenRecordset("DET_STAR", dbOpenTable)
 
 
 
 intFile = FreeFile
 
 Open "C:\Daily_Files\DET.txt" For Output As intFile
 
 
 
 
 'This section puts the records from the DET table in the text
 'file.
  myset.MoveFirst
  Do Until myset.EOF
      LSet strRECTYPE = myset![RECTYPE] 'Field name in brackets
      LSet strPROVNUM = myset![PROVNUM]
      LSet strPCN = myset![PCN]
      LSet strCHRGCODE = myset![CHRGCODE]
      RSet strCHRQTY = myset![CHRQTY]
      RSet strCHRGAMT = myset![CHRGAMT]
      LSet strSRVCDATE = myset![SRVCDATE]
      LSet strPROC = Nz(myset![PROC], [var1])
      LSet strPROVNUM = Nz(myset![PROVNUM], [var1])
      LSet strORDERMD = Nz(myset![ORDERMD], [var1])
      LSet strORDERMDTYPE = Nz(myset![ORDERMDTYPE], [var1])
      LSet strFILLER2 = Nz(myset![FILLER2], [var1])
 
      'Concatenate all of the variables together as in the following:
      Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & strORDERMDTYPE & strFILLER2
      myset.MoveNext
  Loop
 
  Close intFile
  myset.Close
  mydb.Close
 
  MsgBox "Text file has been created!"
 
End Function
 
 

[zmbd]

Also, how do I get rid of all the warning pop-ups, i.e. "

For your update queries, I suggest using the execute method instead. You can set error trapping if the update fails.

Using your stored query, use this:

'(code omitted)
DIM zDB as DAO.Database
Set zDB = CurrentDB
'
'(code omitted)
'
zDB.execute "qryReplaceAllZeros", dbfailonerror
'
'(code omitted)
'
if not zDB is nothing then Set zDB = Nothing
 

As for the export, is the table/query you are attempting to export in the correct formating/order? If not start with making a simple select query that has the fields in the correct order and formatting. Then use the export method.

[ADezii]

1. You may wish to consider a couple of Code Changes that I feel will increase your overall Performance and Efficiency:
   

   '********************** CODE HAS BEEN INTENTIONALLY OMITTED **********************
   Dim MyDB As DAO.Database
   Dim myset As DAO.Recordset
    
   Set MyDB = CurrentDb()
   Set myset = MyDB.OpenRecordset("DET_STAR", dbOpenForwardOnly)
    
   intFile = FreeFile
    
   Open "C:\Daily_Files\DET.txt" For Output As intFile
    
   'This section puts the records from the DET table in the Text File.
   With myset
     Do Until .EOF
       LSet strRECTYPE = ![RECTYPE] 'Field name in brackets
       LSet strPROVNUM = ![PROVNUM]
       LSet strPCN = ![PCN]
       LSet strCHRGCODE = ![CHRGCODE]
       RSet strCHRQTY = ![CHRQTY]
       RSet strCHRGAMT = ![CHRGAMT]
       LSet strSRVCDATE = ![SRVCDATE]
       LSet strPROC = Nz(![Proc], "")
       LSet strPROVNUM = Nz(![PROVNUM], "")
       LSet strORDERMD = Nz(![ORDERMD], "")
       LSet strORDERMDTYPE = Nz(![ORDERMDTYPE], "")
       LSet strFILLER2 = Nz(![FILLER2], "")
    
       'Concatenate all of the variables together as in the following:
       Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & _
                       strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & _
                       strORDERMDTYPE & strFILLER2
         myset.MoveNext
     Loop
   End With
    
   Close intFile
   myset.Close
   MyDB.Close
   '********************** CODE HAS BEEN INTENTIONALLY OMITTED **********************
    

2. To omit those annoying Warning Prompts:
   

   DoCmd.SetWarnings False
     'Execute Action Query
   DoCmd.SetWarnings True     'RESET

[zmbd]

You shouldn't need ADezii's "set warnings" code using the execute method.

Use ADezii's code, insert the execute method as I've outlined inbetween lines 5 and 6 of his code, (make sure to use the set db code only once (^_^)) Make sure to close and release your objects

[NeoPa]

I prefer the .Execute() method (over the .OpenQuery() or .RunSQL() methods) for three reasons :

1. It has an Options parameter where you can pass dbFailOnError to specify the process should have no effect unless all proposed updates are successful.
2. After running, you can refer to the same object (Database, Table, QueryDef, etc) and show how many rows were updated using .RecordsAffected.
3. You never have to fiddle with the status of the Warnings, which isn't a big deal but can leave them set incorrectly if ever the code crashes between settings. Also, your code has to know what the existing setting is before returning after completion.

Code example to illustrate last point :

Private Sub X()
    Call DoCmd.SetWarnings(False)
    ...
    Call Y
    ...
    Call DoCmd.SetWarnings(True)
End Sub
 
Private Sub Y()
    Call DoCmd.SetWarnings(False)
    ...
    Call DoCmd.SetWarnings(True)
End Sub

Everything's fine until returning from Y(). All code after that is running with Warnings=True when it should be Warnings=False.

[ADezii]

To add what NeoPa and zmbd have already stated:

1. If you are going to use SetWarnings, you may wish to set this Option to True within an Error Trap, should one occur.
2. You may want to encapsulate these Queries within a Transaction to ensure that the Data is left in a consistent state prior to writing to the Text File. Either all Actions succeed or none.

[zmbd]

OK, now to the report, Line 71 and 72 in op.
Build the string first!
Then you can see what is actually happening with resulting return by issing a debug.print command.

[GirthJohnson]

I guess I'm more of a noob than I thought. You guys lost me.

ZMBD,

I'm not sure I understand what you mean here...

"You shouldn't need ADezii's "set warnings" code using the execute method.

Use ADezii's code, insert the execute method as I've outlined inbetween lines 5 and 6 of his code, (make sure to use the set db code only once (^_^)) Make sure to close and release your objects "

I adopted ADezzi's approach and tried adding your code between lines 5 &6 but I'm not sure what you're referencing when you write 'code omitted and I'm not having any luck making it work.

After reading the posts further, I'm confused as to whether the queries should have their own sub or where/how exactly they should be placed in the function.

[zmbd]

Post your "new" code and we'll go from there (^_^)

[GirthJohnson]

zmbd,

I think I figured it out. This seems to have worked like a champ. Please review and let me know if there's something I missed.

Public Function CreateTextFile1()
 
'This function creates a fixed-width text file using the Orders table
'in CDA Fixed File Import tool. The CHRGAMT field will be Right-aligned
 
 
 Dim strRECTYPE As String * 4 'specifies width of 4 characters
 Dim strPROVNUM As String * 20  'specifies width of 20 characters
 Dim strPCN As String * 50    'specifies width of 50 characters
 Dim strCHRGCODE As String * 12 'specifies width of 12 characters
 Dim strFILLER1 As String * 18 'specifies width of 18 characters
 Dim strCHRQTY As String * 7 'specifies width of 7 characters
 Dim strCHRGAMT As String * 15 'specifies width of 15 characters
 Dim strSRVCDATE As String * 8 'specifies width of 8 characters
 Dim strPROC As String * 8 'specifies width of 8 characters
 Dim strORDERMD As String * 22  'specifies width of 22 characters
 Dim strORDERMDTYPE As String * 2    'specifies width of 2 characters
 Dim strFILLER2 As String * 34 'specifies width of 34 characters
 Dim var1 As Variant
 Dim mydb As DAO.Database, myset As DAO.Recordset
 Dim intFile As Integer
 
 
  var1 = ""
 
 Set mydb = CurrentDb()
 Dim zDB As DAO.Database
    Set zDB = CurrentDb
 
    '(code omitted)
    DoCmd.SetWarnings False
 
    zDB.Execute "qryReplaceAllZeros", dbFailOnError
 
    zDB.Execute "qryRemoveLeadingZeros", dbFailOnError
 
    DoCmd.SetWarnings True  'Reset
 If Not zDB Is Nothing Then Set zDB = Nothing
 
 'zDB.Close
 
 
 
 Set myset = mydb.OpenRecordset("DET_STAR", dbOpenForwardOnly)
 
 intFile = FreeFile
 
 Open "C:\Daily_Files\DET.txt" For Output As intFile 
 
 
 'This section puts the records from the DET table in the text
 'file.
  With myset
    Do Until .EOF
      LSet strRECTYPE = myset![RECTYPE] 'Field name in brackets
      LSet strPROVNUM = myset![PROVNUM]
      LSet strPCN = myset![PCN]
      LSet strCHRGCODE = myset![CHRGCODE]
      LSet strCHRQTY = myset![CHRQTY]
      RSet strCHRGAMT = myset![CHRGAMT]
      LSet strSRVCDATE = myset![SRVCDATE]
      LSet strPROC = Nz(myset![PROC], [var1])
      LSet strPROVNUM = Nz(myset![PROVNUM], [var1])
      LSet strORDERMD = Nz(myset![ORDERMD], [var1])
      LSet strORDERMDTYPE = Nz(myset![ORDERMDTYPE], [var1])
      LSet strFILLER2 = Nz(myset![FILLER2], [var1])
 
      'Concatenate all of the variables together as in the following:
      Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & _
                      strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & _
                      strORDERMDTYPE & strFILLER2
        myset.MoveNext
    Loop
 
  End With
 
 
  Close intFile
  myset.Close
  mydb.Close
 
  MsgBox "Text file has been created!"
 
End Function

[zmbd]

Just a really quick look at the code as I'm on my way out the door with the boss in 10 minutes:

Line 26/27/28 this is what I was warning you about.
Either use mydb or zdb but not both to set to CurrentDB

As you already have line20 and 26 with mydb, change all zdb to mydb and remove lines 27 and 28

Also remove Line 30,31,37,
Alter line 33,35,38 and other lines with zdb to mydb
Remove 40

Take line 38 as a template.
It is best practice to set the object to nothing after closing the object take line 79 and 80
so for example the "myset" object closed in line 79, using line 38 as template, should have

 If Not myset Is Nothing Then Set  myset = Nothing

following it and the same for the object you closed in line 80

Close BEFORE setting the object to nothing or the close method will error and have a slight risk of data loss. (^_^)

The rule of thumb here is that if you Open it Close it, if you set it, clear/release it.

OK, gota run now.
-z