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

[ladybug76]

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 programmaticall y, 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!!

[Stewart Ross]

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.

[code=vb]Public Type NameofRecord
VarName1 As String * 6
VarName2 As String * 8
VarName3 As String * 1
...
End Type

Public Sub OutputTextfile( )
Dim rs as DAO.Recordset
Dim objFile As Object, TextFile As Object
Dim TextRecord as nameofrecord
Set rs = CurrentDB.Openr ecordset("name of the query to output")
Set objFile = CreateObject("S cripting.FileSy stemObject")
Set TextFile = objFile.CreateT extFile("name and path of file", True)
Do Until rs.EOF
With Textrecord
.Varname1 = CStr(rs![fieldname1])
.Varname2 = CStr(rs![fieldname2])
.Varname3 = CStr(rs![fieldname3])
...
TextFile.Writel ine(.Varname1 & .Varname2 & .Varname3 & ... & .Varnamex)
End With
rs.Movenext
Loop
Rs.close
Textfile.close
End Sub[/code]
-Stewart

[ladybug76]

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!!!

Private Sub cmdrunnewmadc3_Click()
On Error GoTo Err_cmdrunnewmadc3_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
    
    
   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"


    'Build the IN string by looping through the listbox
    For i = 0 To Me.Combo2.ListCount - 1
            If Me.Combo2.Selected(i) Then
            strIN = strIN & "'" & Me.Combo2.Column(0, i) & "',"
            End If
    Next i
 
    
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE ([Rebate_Peroid] = ([Forms]![F_EXP800].[Combo0])) AND ([Record_Indicator] in " & "(" & (Left(strIN, Len(strIN) - 1)) & "))"
   
    strSQL = strSQL & strWhere
    

    MyDB.QueryDefs.Delete "Q_FrmttdExportFile800b"
    Set qdef = MyDB.CreateQueryDef("Q_FrmttdExportFile800b", strSQL)
    
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery "Q_FrmttdExportFile800b", acViewNormal
    'Export command

'Clear listbox selection after running query  
    For Each varItem In Me.Combo2.ItemsSelected
        Me.Combo2.Selected(varItem) = False
    Next varItem

    DoCmd.TransferText acExportDelim,  "NCPDP_ExportSpec",  "Q_FrmttdExportFile800b", "V:\CORPDATA23\Direct Rebates\MYcodeNCPDP.txt",  False, "", 1200
    'Beep
    MsgBox "Export Complete", vbInformation, "Confirmation"
    'DoCmd.Close acQuery, "Q_FrmttdExportFile800b"



Exit_cmdrunnewmadc3_Click:
    Exit Sub

Err_cmdrunnewmadc3_Click:

    If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
        Resume Exit_cmdrunnewmadc3_Click
    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdrunnewmadc3_Click
    End If


End Sub

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.

[code=vb]Public Type NameofRecord
VarName1 As String * 6
VarName2 As String * 8
VarName3 As String * 1
...
End Type

Public Sub OutputTextfile( )
Dim rs as DAO.Recordset
Dim objFile As Object, TextFile As Object
Dim TextRecord as nameofrecord
Set rs = CurrentDB.Openr ecordset("name of the query to output")
Set objFile = CreateObject("S cripting.FileSy stemObject")
Set TextFile = objFile.CreateT extFile("name and path of file", True)
Do Until rs.EOF
With Textrecord
.Varname1 = CStr(rs![fieldname1])
.Varname2 = CStr(rs![fieldname2])
.Varname3 = CStr(rs![fieldname3])
...
TextFile.Writel ine(.Varname1 & .Varname2 & .Varname3 & ... & .Varnamex)
End With
rs.Movenext
Loop
Rs.close
Textfile.close
End Sub[/code]
-Stewart

[Stewart Ross]

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.
[code=vb]strWhere = " WHERE ([Rebate_Peroid] = " & [Forms]![F_EXP800].[Combo0] & ") AND ([Record_Indicato r] in (" & (Left(strIN, Len(strIN) - 1)) & "))

strWhere = " WHERE ([Rebate_Peroid] = '" & [Forms]![F_EXP800].[Combo0] & "') AND ([Record_Indicato r] in (" & (Left(strIN, Len(strIN) - 1)) & "))[/code]

-Stewart

[ladybug76]

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.
[code=vb]strWhere = " WHERE ([Rebate_Peroid] = " & [Forms]![F_EXP800].[Combo0] & ") AND ([Record_Indicato r] in (" & (Left(strIN, Len(strIN) - 1)) & "))

strWhere = " WHERE ([Rebate_Peroid] = '" & [Forms]![F_EXP800].[Combo0] & "') AND ([Record_Indicato r] in (" & (Left(strIN, Len(strIN) - 1)) & "))[/code]

-Stewart

[Stewart Ross]

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:

WHERE [field] = 'SomeValue'

we would build this string in code as

"WHERE [field] = '" & [ControlStoringValue] & "'"

-Stewart

[ladybug76]

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! :)