Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/250970

From BetaArchive Wiki
Knowledge Base


ACC: How to Write More than 255 Columns to a File

Article ID: 250970

Article Last Modified on 1/27/2007



APPLIES TO

  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q250970

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


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.

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.

  1. 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
                        
  2. 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
                        
  3. Create a module, and then type the following line in the Declarations section if it is not already there:

    Option Explicit
                        
  4. Type the following procedure:

     
    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) <> "" Then
            If MsgBox("The file you entered already exists.  Would you " _      
            & "like to delete it?", vbExclamation + vbYesNo) = vbYes Then
                Kill sFileName
            Else
                Exit Sub
            End If
        End If
        
        'Create a separate recordset for each query
        Set rst1 = CurrentDb().OpenRecordset("qryCustomers1")
        Set rst2 = CurrentDb().OpenRecordset("qryCustomers2")
        
        '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 "File has been written to " & 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 "An error has occurred: " & vbCrLf & Err.Number & " " & Err.Description
        Resume WriteFileExit
    End Sub
                        
  5. To test this procedure, type the following line in the Debug window, and then press ENTER:

    WriteFlatFile "c:\testfile.txt",","
                            

    Note that a comma delimited file, C:\Testfile.txt, has been created, and that the entire Customers table has been exported to this file.



Additional query words: OFF97 ACC97 ACC95

Keywords: kbhowto KB250970