By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

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

P: 20
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..."

Expand|Select|Wrap|Line Numbers
  1. 'This query updates the DET table and replaces "000000000000" with "0.00"
  2. Public Function qryReplaceAllZeros()
  3.  
  4. DoCmd.OpenQuery "qryReplaceAllZeros", acViewNormal, acEdit
  5.  
  6. End Function
  7.  
  8. 'This query updates the DET table and replaces all leading zeros
  9. Public Function qryRemoveLeadingZeros()
  10.  
  11. DoCmd.OpenQuery "qryRemoveLeadingZeros", acViewNormal, acEdit
  12.  
  13. End Function
  14.  
  15.  
  16. Public Function CreateTextFile()
  17. 'This function creates a fixed-width text file using the Orders table
  18. 'in CDA Fixed File Import tool. The CHRGAMT field will be Right-aligned
  19.  
  20.  
  21.  Dim strRECTYPE As String * 4 'specifies width of 4 characters
  22.  Dim strPROVNUM As String * 20  'specifies width of 20 characters
  23.  Dim strPCN As String * 50    'specifies width of 50 characters
  24.  Dim strCHRGCODE As String * 12 'specifies width of 12 characters
  25.  Dim strFILLER1 As String * 18 'specifies width of 18 characters
  26.  Dim strCHRQTY As String * 7 'specifies width of 7 characters
  27.  Dim strCHRGAMT As String * 15 'specifies width of 15 characters
  28.  Dim strSRVCDATE As String * 8 'specifies width of 8 characters
  29.  Dim strPROC As String * 8 'specifies width of 8 characters
  30.  Dim strORDERMD As String * 22  'specifies width of 22 characters
  31.  Dim strORDERMDTYPE As String * 2    'specifies width of 2 characters
  32.  Dim strFILLER2 As String * 34 'specifies width of 34 characters
  33.  Dim var1 As Variant
  34.  Dim result As String
  35.  Dim mydb As DAO.Database, myset As DAO.Recordset
  36.  Dim intFile As Integer
  37.  
  38.  
  39.  var1 = ""
  40.  
  41.  
  42.  Set mydb = CurrentDb()
  43.  Set myset = mydb.OpenRecordset("DET_STAR", dbOpenTable)
  44.  
  45.  
  46.  
  47.  intFile = FreeFile
  48.  
  49.  Open "C:\Daily_Files\DET.txt" For Output As intFile
  50.  
  51.  
  52.  
  53.  
  54.  'This section puts the records from the DET table in the text
  55.  'file.
  56.   myset.MoveFirst
  57.   Do Until myset.EOF
  58.       LSet strRECTYPE = myset![RECTYPE] 'Field name in brackets
  59.       LSet strPROVNUM = myset![PROVNUM]
  60.       LSet strPCN = myset![PCN]
  61.       LSet strCHRGCODE = myset![CHRGCODE]
  62.       RSet strCHRQTY = myset![CHRQTY]
  63.       RSet strCHRGAMT = myset![CHRGAMT]
  64.       LSet strSRVCDATE = myset![SRVCDATE]
  65.       LSet strPROC = Nz(myset![PROC], [var1])
  66.       LSet strPROVNUM = Nz(myset![PROVNUM], [var1])
  67.       LSet strORDERMD = Nz(myset![ORDERMD], [var1])
  68.       LSet strORDERMDTYPE = Nz(myset![ORDERMDTYPE], [var1])
  69.       LSet strFILLER2 = Nz(myset![FILLER2], [var1])
  70.  
  71.       'Concatenate all of the variables together as in the following:
  72.       Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & strORDERMDTYPE & strFILLER2
  73.       myset.MoveNext
  74.   Loop
  75.  
  76.   Close intFile
  77.   myset.Close
  78.   mydb.Close
  79.  
  80.   MsgBox "Text file has been created!"
  81.  
  82. End Function
  83.  
  84.  
Feb 19 '14 #1
Share this Question
Share on Google+
10 Replies


zmbd
Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. '(code omitted)
  2. DIM zDB as DAO.Database
  3. Set zDB = CurrentDB
  4. '
  5. '(code omitted)
  6. '
  7. zDB.execute "qryReplaceAllZeros", dbfailonerror
  8. '
  9. '(code omitted)
  10. '
  11. if not zDB is nothing then Set zDB = Nothing
  12.  
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.
Feb 19 '14 #2

ADezii
Expert 5K+
P: 8,636
  1. You may wish to consider a couple of Code Changes that I feel will increase your overall Performance and Efficiency:
    Expand|Select|Wrap|Line Numbers
    1. '********************** CODE HAS BEEN INTENTIONALLY OMITTED **********************
    2. Dim MyDB As DAO.Database
    3. Dim myset As DAO.Recordset
    4.  
    5. Set MyDB = CurrentDb()
    6. Set myset = MyDB.OpenRecordset("DET_STAR", dbOpenForwardOnly)
    7.  
    8. intFile = FreeFile
    9.  
    10. Open "C:\Daily_Files\DET.txt" For Output As intFile
    11.  
    12. 'This section puts the records from the DET table in the Text File.
    13. With myset
    14.   Do Until .EOF
    15.     LSet strRECTYPE = ![RECTYPE] 'Field name in brackets
    16.     LSet strPROVNUM = ![PROVNUM]
    17.     LSet strPCN = ![PCN]
    18.     LSet strCHRGCODE = ![CHRGCODE]
    19.     RSet strCHRQTY = ![CHRQTY]
    20.     RSet strCHRGAMT = ![CHRGAMT]
    21.     LSet strSRVCDATE = ![SRVCDATE]
    22.     LSet strPROC = Nz(![Proc], "")
    23.     LSet strPROVNUM = Nz(![PROVNUM], "")
    24.     LSet strORDERMD = Nz(![ORDERMD], "")
    25.     LSet strORDERMDTYPE = Nz(![ORDERMDTYPE], "")
    26.     LSet strFILLER2 = Nz(![FILLER2], "")
    27.  
    28.     'Concatenate all of the variables together as in the following:
    29.     Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & _
    30.                     strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & _
    31.                     strORDERMDTYPE & strFILLER2
    32.       myset.MoveNext
    33.   Loop
    34. End With
    35.  
    36. Close intFile
    37. myset.Close
    38. MyDB.Close
    39. '********************** CODE HAS BEEN INTENTIONALLY OMITTED **********************
    40.  
  2. To omit those annoying Warning Prompts:
    Expand|Select|Wrap|Line Numbers
    1. DoCmd.SetWarnings False
    2.   'Execute Action Query
    3. DoCmd.SetWarnings True     'RESET
Feb 20 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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
Feb 20 '14 #4

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub X()
  2.     Call DoCmd.SetWarnings(False)
  3.     ...
  4.     Call Y
  5.     ...
  6.     Call DoCmd.SetWarnings(True)
  7. End Sub
  8.  
  9. Private Sub Y()
  10.     Call DoCmd.SetWarnings(False)
  11.     ...
  12.     Call DoCmd.SetWarnings(True)
  13. End Sub
Everything's fine until returning from Y(). All code after that is running with Warnings=True when it should be Warnings=False.
Feb 20 '14 #5

ADezii
Expert 5K+
P: 8,636
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.
Feb 20 '14 #6

zmbd
Expert Mod 5K+
P: 5,397
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.
Feb 20 '14 #7

P: 20
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.
Feb 20 '14 #8

zmbd
Expert Mod 5K+
P: 5,397
Post your "new" code and we'll go from there (^_^)
Feb 20 '14 #9

P: 20
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.



Expand|Select|Wrap|Line Numbers
  1. Public Function CreateTextFile1()
  2.  
  3. 'This function creates a fixed-width text file using the Orders table
  4. 'in CDA Fixed File Import tool. The CHRGAMT field will be Right-aligned
  5.  
  6.  
  7.  Dim strRECTYPE As String * 4 'specifies width of 4 characters
  8.  Dim strPROVNUM As String * 20  'specifies width of 20 characters
  9.  Dim strPCN As String * 50    'specifies width of 50 characters
  10.  Dim strCHRGCODE As String * 12 'specifies width of 12 characters
  11.  Dim strFILLER1 As String * 18 'specifies width of 18 characters
  12.  Dim strCHRQTY As String * 7 'specifies width of 7 characters
  13.  Dim strCHRGAMT As String * 15 'specifies width of 15 characters
  14.  Dim strSRVCDATE As String * 8 'specifies width of 8 characters
  15.  Dim strPROC As String * 8 'specifies width of 8 characters
  16.  Dim strORDERMD As String * 22  'specifies width of 22 characters
  17.  Dim strORDERMDTYPE As String * 2    'specifies width of 2 characters
  18.  Dim strFILLER2 As String * 34 'specifies width of 34 characters
  19.  Dim var1 As Variant
  20.  Dim mydb As DAO.Database, myset As DAO.Recordset
  21.  Dim intFile As Integer
  22.  
  23.  
  24.   var1 = ""
  25.  
  26.  Set mydb = CurrentDb()
  27.  Dim zDB As DAO.Database
  28.     Set zDB = CurrentDb
  29.  
  30.     '(code omitted)
  31.     DoCmd.SetWarnings False
  32.  
  33.     zDB.Execute "qryReplaceAllZeros", dbFailOnError
  34.  
  35.     zDB.Execute "qryRemoveLeadingZeros", dbFailOnError
  36.  
  37.     DoCmd.SetWarnings True  'Reset
  38.  If Not zDB Is Nothing Then Set zDB = Nothing
  39.  
  40.  'zDB.Close
  41.  
  42.  
  43.  
  44.  Set myset = mydb.OpenRecordset("DET_STAR", dbOpenForwardOnly)
  45.  
  46.  intFile = FreeFile
  47.  
  48.  Open "C:\Daily_Files\DET.txt" For Output As intFile 
  49.  
  50.  
  51.  'This section puts the records from the DET table in the text
  52.  'file.
  53.   With myset
  54.     Do Until .EOF
  55.       LSet strRECTYPE = myset![RECTYPE] 'Field name in brackets
  56.       LSet strPROVNUM = myset![PROVNUM]
  57.       LSet strPCN = myset![PCN]
  58.       LSet strCHRGCODE = myset![CHRGCODE]
  59.       LSet strCHRQTY = myset![CHRQTY]
  60.       RSet strCHRGAMT = myset![CHRGAMT]
  61.       LSet strSRVCDATE = myset![SRVCDATE]
  62.       LSet strPROC = Nz(myset![PROC], [var1])
  63.       LSet strPROVNUM = Nz(myset![PROVNUM], [var1])
  64.       LSet strORDERMD = Nz(myset![ORDERMD], [var1])
  65.       LSet strORDERMDTYPE = Nz(myset![ORDERMDTYPE], [var1])
  66.       LSet strFILLER2 = Nz(myset![FILLER2], [var1])
  67.  
  68.       'Concatenate all of the variables together as in the following:
  69.       Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & _
  70.                       strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & _
  71.                       strORDERMDTYPE & strFILLER2
  72.         myset.MoveNext
  73.     Loop
  74.  
  75.   End With
  76.  
  77.  
  78.   Close intFile
  79.   myset.Close
  80.   mydb.Close
  81.  
  82.   MsgBox "Text file has been created!"
  83.  
  84. End Function
Feb 20 '14 #10

zmbd
Expert Mod 5K+
P: 5,397
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
Expand|Select|Wrap|Line Numbers
  1.  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
Feb 20 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.