Insert blank records in a table or export

[AlanM]

I am exporting customer order records to another application from Access2000. The receiving application needs a blank record as a "separator" when the customer name changes. Customers can have any number of order records in the table.

Simply put, I need to read an Access table, writing each record to another table (or txt file) and when FieldX changes, insert a blank record before copying the record in the table.

Does anyone have a piece of code to do this.

Thanks.

 

[llkhoutx]

Access doesn't export records one by one. They all go out together. Further, Access indexes records (via indexes set in the table's design view) whether viewed in a table or query, they are not necessarily in the order input.

Therefore, you have know how they're indexed and output so as to be able to create an appropriate index for the (otherwise) blank record to be inserted.

Records are easily added on Customer change with DAO/ADO recordset operations. Many, but not all, Access books have recordset manipulation code examples.

In other words, tables and queries are ordered, inserting a record in a particular position requires an appropriate index.

Does you table have a primary key, perhaps an autonumber? How are you exporting you data?

 

[john471]

Output to Text File

With deference to llkhoutx's information pertaining to tables, I suggest you go with your stated option b, and output to a text file.

Below is some code I knocked up for you to use as a starting point (using the (AC97) sample Northwind database).

You would, naturally, want to tailor it to your own table and field names, and add in error handling etc.

HTH.

Regards

John.

Sub WriteOutPutFile()
    Const cszOutPutFilePathAndName As String = "C:\MyOutPut.txt"
    Dim szSQL As String
    Dim rs As Recordset
    Dim szLastCustomerID As String  ' Yes, string, but that is how it is in Northwind
    
    szSQL = "SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.RequiredDate "
    szSQL = szSQL & "FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID "
    szSQL = szSQL & "ORDER BY Customers.CustomerID;"
    
    Set rs = CurrentDb.OpenRecordset(szSQL)
    If Not (rs.EOF And rs.BOF) Then
        Open cszOutPutFilePathAndName For Output As #1    ' Open file for output.
        With rs
            .MoveFirst
            szLastCustomerID = .Fields("CustomerID").Value
            Do While Not .EOF
                If Not szLastCustomerID = .Fields("CustomerID").Value Then
                    Write #1,   ' Write blank line.
                End If
                Write #1, .Fields("CustomerID").Value, .Fields("CompanyName").Value, .Fields("OrderID").Value, Format(.Fields("RequiredDate").Value, "DD-MMM-YYYY")  ' Write comma-delimited data.
                szLastCustomerID = .Fields("CustomerID").Value
                .MoveNext
            Loop 'while not .eof
        End With 'rs
        Close #1
    End If
    rs.Close
    Set rs = Nothing
    
End Sub

 

[WayneRyan]

John,

Very nice!

I often find that Access doesn't readily provide a ready means for input and
output of tables (mostly input). Sometimes you just have to write a little
code.

Wayne

 

[Pat Hartman]

Access provides several ways to import or export tables. You just need to understand what a "table" is. What Alan described as a "table" does not fit the definition supported by relational databases, hence the need to write custom code. At its simplest, all rows in a table have the same field definition. This file deviates from that definition with its blank rows used as separators. Also, recordsets by their nature are unordered unless you specifically order them. That won't work in this case because there is no way to order a recordset with blank rows and have the blank rows appear anywhere but together at the point in the sort sequence where a blank would logically fall.

 

[AlanM]

Thanks to you all for your responses.

FYI, a minor point of clarification: the customer order records are in fact held in two tables (like the NorthWind MDB, orders and order details). The blank separator is only required on the output text file.

So I think the code from John will do very nicely as a start point.

Many thanks

Alan M