473,326 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Need Help: Exporting more than 65k records to a fixed width text file

Hello.

I'm working in Access 2003 and I'm new to VBA.

I'm trying to create a button on a form that will run my query (based on the 2 drop down box parameter selections made by the user). The query runs in the background and needs to be exported to a fixed-width (as in fixed width columns) text file. The text file will be loaded to a mainframe for ftp to our client.

I was able to get the button and query to work, but I keep getting an error that I can not export more than 65,000 records at once.

Is there a way using VBA that I can automatically (via my button) export the entire contents of the query, or at least cut the output into multiple files, to concatenate manually later??

(*If there is a way to append them together programmatically, that would be SUPER WONDERFUL…. but at this point, just getting the query to export in its entirety… in any way…. would make me extremely happy*)

As per the forum instructions: I am working on a time frame of a few days, so any and all guidance would be deeply appreciated. Even if the answer is “not a function of Access 2003”, then I can start researching other avenues.

Thank you SO much, in advance!!
Mar 11 '08 #1
6 4602
Stewart Ross
2,545 Expert Mod 2GB
Hi, and sorry this reply is outside of your timescale.

The 65,536-row limitations can be overcome in VB by writing the text directly to a text file within a loop which will process all rows of your query. To output in fixed width format you will need to be able to control the width of each field you output. For text fields one way to do this is to define the fields as fixed-width strings, padding unused parts of the string with spaces (so that there are recognisable characters in unused portions of the string).

The skeleton example below uses a custom record specification defining the fixed-width record structure. There are string conversions in the skeleton, but of course you would need to adjust and add to these as necessary for your particular circumstances.[/font]

The code would be placed in a new or existing public code module, then a call to the output sub would be placed in the On Click code of a command button on a form somewhere to run the code.

You may need to add the Microsoft DAO 3.6 (or similar) object library to your project references. If you obtain compile errors on the recordset lines, from the VB editor select Tools, References and tick the MS DAO reference listed.

Expand|Select|Wrap|Line Numbers
  1. Public Type NameofRecord
  2.   VarName1 As String * 6
  3.   VarName2 As String * 8
  4.   VarName3 As String * 1
  5.   ...
  6. End Type
  7.  
  8. Public Sub OutputTextfile()
  9. Dim rs as DAO.Recordset
  10. Dim objFile As Object, TextFile As Object
  11. Dim TextRecord as nameofrecord
  12. Set rs = CurrentDB.Openrecordset("name of the query to output")
  13. Set objFile = CreateObject("Scripting.FileSystemObject")
  14. Set TextFile = objFile.CreateTextFile("name and path of file", True)
  15. Do Until rs.EOF
  16.   With Textrecord 
  17.     .Varname1 = CStr(rs![fieldname1])
  18.     .Varname2 = CStr(rs![fieldname2])
  19.     .Varname3 = CStr(rs![fieldname3])
  20.     ...
  21.     TextFile.Writeline(.Varname1 & .Varname2 & .Varname3 & ... & .Varnamex)
  22.   End With
  23.   rs.Movenext
  24. Loop
  25. Rs.close
  26. Textfile.close
  27. End Sub
-Stewart
Mar 16 '08 #2
Thank you for your response!!!! It's greatly appreciated..

Maybe you can help with my current problem regarding the same code. Or I can post to a new thread if you prefer.

Here's the situation.:

I went another route trying to get the button to launch the query that was run based on a combo box (named Combo0 below ) and Multi-Selection (Simple) list box (named Combo2 below). But I continue to get errors stating "Item not in Collection" or "Type Mismatch" I used a scheme.ini and that works for the more than 65k record export, but now I'm getting these other errors.

The errors seem (based on MsgBox breaks I had been using to check the code) to occur after my "qdef" statement.

I tried adding the Reference (Microsoft DAO 3.6). I'm sure I needed it, but it didn't change the error.

Here's the code I'm using. Can you see where I may be going wrong?? I'm really not good at VB, so any advice or assistance is greatly appreciated.

Thanks in Advance!!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdrunnewmadc3_Click()
  2. On Error GoTo Err_cmdrunnewmadc3_Click
  3.     Dim MyDB As DAO.Database
  4.     Dim qdef As DAO.QueryDef
  5.     Dim i As Integer
  6.     Dim strSQL As String
  7.     Dim strWhere As String
  8.     Dim strIN As String
  9.     Dim varItem As Variant
  10.  
  11.     Set MyDB = CurrentDb()
  12.  
  13.  
  14.    strSQL = "SELECT QFE.Record_Type, QFE.Line_Number, QFE.Data_Level, QFE.Plan_ID_Qualifier, QFE.Plan_ID_Code, QFE.Plan_Name, QFE.Provider_ID_Qualifier, QFE.Provider_ID, QFE.Product_ID_Qualifier, QFE.Product_ID_NDC, QFE.Product_Description, [total_quan] & [tqneg] AS Total_Quantity, QFE.Unit_of_Measure, [reb_dys_sup] & [rebdyssupneg] AS Rebate_Days_Supply, [pres_typ_srv_ct] & [prestypservctneg] AS Prescription_Type_Service_Ct, QFE.Prescription_Number_Qualifier, QFE.Prescription_Number, QFE.Date_of_Service_CCYYMMDD, QFE.Fill_Number, QFE.Record_Purpose_Indicator, [RebperUnitAmt] & [RebperUnitAmtNeg] AS Rebate_Per_Unit_Amount, [ReqRebAmt] & [ReqRebAmtNeg] AS Requested_Rebate_Amount, QFE.Formulary_Code, QFE.Claim_Number, QFE.Dispensing_Status, QFE.Rebate_File_Number FROM Q_FileExport800 AS QFE"
  15.  
  16.  
  17.     'Build the IN string by looping through the listbox
  18.     For i = 0 To Me.Combo2.ListCount - 1
  19.             If Me.Combo2.Selected(i) Then
  20.             strIN = strIN & "'" & Me.Combo2.Column(0, i) & "',"
  21.             End If
  22.     Next i
  23.  
  24.  
  25.     'Create the WHERE string, and strip off the last comma of the IN string
  26.     strWhere = " WHERE ([Rebate_Peroid] = ([Forms]![F_EXP800].[Combo0])) AND ([Record_Indicator] in " & "(" & (Left(strIN, Len(strIN) - 1)) & "))"
  27.  
  28.     strSQL = strSQL & strWhere
  29.  
  30.  
  31.     MyDB.QueryDefs.Delete "Q_FrmttdExportFile800b"
  32.     Set qdef = MyDB.CreateQueryDef("Q_FrmttdExportFile800b", strSQL)
  33.  
  34.     'Open the query, built using the IN clause to set the criteria
  35.     DoCmd.OpenQuery "Q_FrmttdExportFile800b", acViewNormal
  36.     'Export command
  37.  
  38. 'Clear listbox selection after running query  
  39.     For Each varItem In Me.Combo2.ItemsSelected
  40.         Me.Combo2.Selected(varItem) = False
  41.     Next varItem
  42.  
  43.     DoCmd.TransferText acExportDelim,  "NCPDP_ExportSpec",  "Q_FrmttdExportFile800b", "V:\CORPDATA23\Direct Rebates\MYcodeNCPDP.txt",  False, "", 1200
  44.     'Beep
  45.     MsgBox "Export Complete", vbInformation, "Confirmation"
  46.     'DoCmd.Close acQuery, "Q_FrmttdExportFile800b"
  47.  
  48.  
  49.  
  50. Exit_cmdrunnewmadc3_Click:
  51.     Exit Sub
  52.  
  53. Err_cmdrunnewmadc3_Click:
  54.  
  55.     If Err.Number = 5 Then
  56.         MsgBox "You must make a selection(s) from the list" _
  57.                , , "Selection Required !"
  58.         Resume Exit_cmdrunnewmadc3_Click
  59.     Else
  60.         'Write out the error and exit the sub
  61.         MsgBox Err.Description
  62.         Resume Exit_cmdrunnewmadc3_Click
  63.     End If
  64.  
  65.  
  66. End Sub
  67.  

Hi, and sorry this reply is outside of your timescale.

The 65,536-row limitations can be overcome in VB by writing the text directly to a text file within a loop which will process all rows of your query. To output in fixed width format you will need to be able to control the width of each field you output. For text fields one way to do this is to define the fields as fixed-width strings, padding unused parts of the string with spaces (so that there are recognisable characters in unused portions of the string).

The skeleton example below uses a custom record specification defining the fixed-width record structure. There are string conversions in the skeleton, but of course you would need to adjust and add to these as necessary for your particular circumstances.[/font]

The code would be placed in a new or existing public code module, then a call to the output sub would be placed in the On Click code of a command button on a form somewhere to run the code.

You may need to add the Microsoft DAO 3.6 (or similar) object library to your project references. If you obtain compile errors on the recordset lines, from the VB editor select Tools, References and tick the MS DAO reference listed.

Expand|Select|Wrap|Line Numbers
  1. Public Type NameofRecord
  2.   VarName1 As String * 6
  3.   VarName2 As String * 8
  4.   VarName3 As String * 1
  5.   ...
  6. End Type
  7.  
  8. Public Sub OutputTextfile()
  9. Dim rs as DAO.Recordset
  10. Dim objFile As Object, TextFile As Object
  11. Dim TextRecord as nameofrecord
  12. Set rs = CurrentDB.Openrecordset("name of the query to output")
  13. Set objFile = CreateObject("Scripting.FileSystemObject")
  14. Set TextFile = objFile.CreateTextFile("name and path of file", True)
  15. Do Until rs.EOF
  16.   With Textrecord 
  17.     .Varname1 = CStr(rs![fieldname1])
  18.     .Varname2 = CStr(rs![fieldname2])
  19.     .Varname3 = CStr(rs![fieldname3])
  20.     ...
  21.     TextFile.Writeline(.Varname1 & .Varname2 & .Varname3 & ... & .Varnamex)
  22.   End With
  23.   rs.Movenext
  24. Loop
  25. Rs.close
  26. Textfile.close
  27. End Sub
-Stewart
Mar 17 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
Hi. I think the most likely source of the error is your reference to the combo in the Where clause at line 26. Usually, you need to split the string and pass the value of the combo as a string literal (if it is a string) or as a numeric value (if rebate period is a number). Check also that the field references - all of them - are spelt correctly - I can't tell if [Rebate_Peroid], for example, is intentional or a mistake.

There are two versions of the line 26 replacement below. The first is used if Rebate_Peroid is a numeric value, the second if it is a string.
Expand|Select|Wrap|Line Numbers
  1. strWhere = " WHERE ([Rebate_Peroid] = " & [Forms]![F_EXP800].[Combo0] & ") AND ([Record_Indicator] in (" & (Left(strIN, Len(strIN) - 1)) & "))
  2.  
  3. strWhere = " WHERE ([Rebate_Peroid] = '" & [Forms]![F_EXP800].[Combo0] & "') AND ([Record_Indicator] in (" & (Left(strIN, Len(strIN) - 1)) & "))
-Stewart
Mar 17 '08 #4
Hello Again.

I corrected the misspelling
Thanks for catching that one!! I've obviously been staring at this too long. :)

The [Rebate_Period] is a string, so I used the second example, but I'm still getting the "Item not in Collection" error.

I'm trying to see if i understand (pardon my quasi-layman terms), you think I should split the strWhere into two parts, then put them together. In plain English, I should take a variable and equate it to the Rebate Period combo box, then create a second variable with a value of the Record Indicator list box loop. With the final step being to merge the two into one Variable that I can use as the WHERE statement in my SQL for the qdef?

I really don't know what else to do, if not that....

Thanks again for all your help.!!! Even if I can't get it to work due to my lack of knowledge, at least I've learned a few things.

I'm very greatful to have found such a wonderful forum, full of very kind and helpful people!


Hi. I think the most likely source of the error is your reference to the combo in the Where clause at line 26. Usually, you need to split the string and pass the value of the combo as a string literal (if it is a string) or as a numeric value (if rebate period is a number). Check also that the field references - all of them - are spelt correctly - I can't tell if [Rebate_Peroid], for example, is intentional or a mistake.

There are two versions of the line 26 replacement below. The first is used if Rebate_Peroid is a numeric value, the second if it is a string.
Expand|Select|Wrap|Line Numbers
  1. strWhere = " WHERE ([Rebate_Peroid] = " & [Forms]![F_EXP800].[Combo0] & ") AND ([Record_Indicator] in (" & (Left(strIN, Len(strIN) - 1)) & "))
  2.  
  3. strWhere = " WHERE ([Rebate_Peroid] = '" & [Forms]![F_EXP800].[Combo0] & "') AND ([Record_Indicator] in (" & (Left(strIN, Len(strIN) - 1)) & "))
-Stewart
Mar 18 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
Hi, and many thanks for your kind comments - much appreciated!

I think you are really close on this one, and to resolve things you need to take a very systematic approach to ruling things in or out.

Start by commenting out the string build creating the where clause, by adding a single quote to the start of the line (line 26 in post #3). This will mean there is no WHERE clause in the SQL. If the querydef is successful then the problem is in the WHERE clause; if it isn't then there is something wrong in the more complex SQL statement. By the way, there should be a closing ";" on the end of the SQL statement, though it can work without it.

If you get further run-time errors use the debug option to view the code line where the error occurred. If you hover your mouse over the various variables and object references you will see their current values shown as you hover over them with the mouse. Note these down, and in particular check for any which come out as <object not set> or some other warning value.

The WHERE clause in code is built as a string. We split the control value references from the WHERE string itself, as it is the current value of the control that is to be included in the string. As an example, if we wanted the where clause to refer to a text value:
Expand|Select|Wrap|Line Numbers
  1. WHERE [field] = 'SomeValue'
we would build this string in code as
Expand|Select|Wrap|Line Numbers
  1. "WHERE [field] = '" & [ControlStoringValue] & "'"
-Stewart
Mar 18 '08 #6
I just wanted to let you know, I never did get it to work. But I really appreciate your help. I ended up scrapping the 2 parameter thing and hard coded one of them into my query. Then I used the original suggestion you gave me in the beginning of this thread. I got it to work and I'm a VERY happy camper!!

Thank you so much for all your help and patience!!!!

TheScripts Rock! :)
Mar 25 '08 #7

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

Similar topics

3
by: N. Demos | last post by:
I have a single row table with fixed dimensioned cells nested inside a fixed dimensioned div, which has overflow: hidden. The div's dimensions are such that It should only display the first two...
20
by: titi | last post by:
Question The road and traffic authority for a small country currently uses a system to store information about all 'currently' licensed drivers. A licensed driver has the following info stored...
2
by: Brian | last post by:
Hello, I have a text file I'm attempting to parse. There are about 50 fixed width fields in each line / row. For example (shortened for brevity): W1234Somebody East 101110001111010101...
2
by: MyNameIsnt | last post by:
Can anyone tell me why, when I click on the buttons it register 2 characters on the display? if you use the right mousebutton it works ok, but the buttons dont flash?? it works fine without the...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
4
by: The Bicycling Guitarist | last post by:
Hi. My markup is valid but the page sucks when the window is shrunk, the text going behind the image. Can anyone suggest a way to fix this while keeping the same general layout it has now (nav...
9
by: dli07 | last post by:
Hello, I'm trying to convert a piece of code that creates a dynamic vertical resizing bar in a table from internet explorer to firefox. It's based on a post from...
2
by: davidson1 | last post by:
Hai friends..for menu to use in my website..i found in one website....pl look below website.... http://www.dynamicdrive.com/dynamicindex1/omnislide/index.htm i downloaded 2 files.... ...
1
by: Marty Klunk | last post by:
I have an Access97 data base where we are exporting records out to a text file that is then sent to a customer via EDI transmission. The problem I am having is that during the export process access...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.