Microsoft KB Archive/268848

= ACC2000: How to Write More than 255 Columns to a Text File =

Article ID: 268848

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q268848



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
Because Microsoft Access has a limit of 255 columns in a table or a query, you cannot export text files that must have more than 255 columns as you usually would. This article shows you how you can export text files (sometimes referred to as flat files) that have more than 255 columns. The method described in this article uses a query to split the data, and then uses Visual Basic for Applications to export the information into one file.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

The sample code contained in this article works with separate recordset objects, and uses sequential file statements to export all the data. This example uses two queries to split the Customers table in Northwind.mdb. To preserve the integrity of the data, both queries should have the same number of records, and they should be sorted in the same order.

  Create the first query as follows:   Query: qryCustomers1 Type: Select Query

Field: CustomerID Table: Customers

Field: CompanyName Table: Customers

Field: ContactName Table: Customers

Field: ContactTitle Table: Customers   Create the second query as follows:   Query: qryCustomers2 Type: Select Query

Field: Address Table: Customers

Field: City Table: Customers

Field: Region Table: Customers

Field: PostalCode Table: Customers

Field: Country Table: Customers

Field: Phone Table: Customers

Field: Fax Table: Customers   Create a new module, and enter the following line in the Declarations section if it is not already there: Option Explicit   Enter the following procedure in the module: Sub WriteFlatFile(sFileName As String, sDelimiter As String)

On Error GoTo WriteFileErrors

Dim rst1 As DAO.Recordset Dim rst2 As DAO.Recordset Dim fld1 As DAO.Field Dim fld2 As DAO.Field 'Counter variable used to print the delimiter except after 'the last field for rst2 Dim intCount As Integer 'Delete the specified file if it already exists If Dir(sFileName) <> &quot;&quot; Then If MsgBox(&quot;The file you entered already exists. Would you &quot; _              & &quot;like to delete it?&quot;, vbExclamation + vbYesNo) = vbYes Then Kill sFileName Else Exit Sub End If   End If    'Create a separate recordset for each query Set rst1 = CurrentDb.OpenRecordset(&quot;qryCustomers1&quot;) Set rst2 = CurrentDb.OpenRecordset(&quot;qryCustomers2&quot;) 'Open the file using the Open statement Open sFileName For Output As #1 'Write the header row from both recordsets using the Print statement For intCount = 0 To rst1.Fields.Count - 1 Print #1, rst1(intCount).Name & sDelimiter; Next 'This uses a counter variable to see determine if we are writing the 'last field. If we are, do not print the delimiter. Only check this 'for the end of the second recordset, to make sure there is a delimiter 'between the 2 recordsets For intCount = 0 To rst2.Fields.Count - 1 If intCount < rst2.Fields.Count - 1 Then Print #1, rst2(intCount).Name & sDelimiter; Else Print #1, rst2(intCount).Name; End If   Next 'Write new line Print #1, 'Write the data from each recordset Do While Not rst1.EOF And Not rst2.EOF For intCount = 0 To rst1.Fields.Count - 1 Print #1, rst1(intCount).Value & sDelimiter; Next rst1.MoveNext For intCount = 0 To rst2.Fields.Count - 1 If intCount < rst2.Fields.Count - 1 Then Print #1, rst2(intCount).Value & sDelimiter; Else Print #1, rst2(intCount).Value; End If       Next rst2.MoveNext 'write to new line Print #1, Loop

MsgBox &quot;File has been written to &quot; & sFileName WriteFileExit: 'Close objects and destroy DAO object variables rst1.Close rst2.Close Set rst1 = Nothing Set rst2 = Nothing 'close the sequential file opened earlier Close #1 Exit Sub

WriteFileErrors: MsgBox &quot;An error has occurred: &quot; & vbCrLf & Err.Number & &quot; &quot; & Err.Description Resume WriteFileExit End Sub   To test this procedure, type the following line in the Immediate window, and then press ENTER: WriteFlatFile &quot;C:\TestFile.txt&quot;,&quot;,&quot; Note that a comma delimited file, C:\TestFile.txt, has been created, and that the entire Customers table has been exported to this file. </li></ol>

Additional query words: inf

Keywords: kbhowto KB268848

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.