CSV creation from two tables

[jellybabygm]

I have two tables - Orders, Order Items - Access 2000 DB

I need to create a csv file to import this into another system

the format needs to be:

Order 1, Order 1 Customer name, Order 1 Address,
Order item 1.1, Order Item 1.1 weight,
Order item 1.2, Order Item 1.2 weight,
Order 2, Order 2 Customer name, Order 2 Address,
Order item 2.1, Order Item 2.1 weight,
Order item 2.2, Order Item 2.2 weight,

etc...

also I need some of these fields to be text with " ". All the fields are text format in the tables.

Any help will be welcomed !

NB I am presuming that I will need to parse this somehow...

[puppydogbuddy]

For illustrative purposes, if you had your database setup with separate, but related related tables between orders, order details, and customers (as shown in the Northwind sample db that comes with Access), you can extract the info you want by means of a select query simiilar to the following:

SELECT Orders.OrderID, [Order Details].ProductID, Customers.CustomerID, Customers.CompanyName, Customers.Address, Customers.City
FROM Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.CustomerID;

You can then save this query as Customer Orders and use the transfer text method to produce your CSV file by placing the following code behind a button.

DoCmd.TransferText acExportDelim, "Customer Orders", _
    "Orders Report", "C:\CSVFile\AugustOrders.doc"

[jellybabygm]

This won' t give me what I want as I need the order details and order items on seperate lines in the csv file. The select query will only put it all into one line.

I think I need to do it in VB, but I do not know the code.
Take Order details -> export to csv
Take Order item details -> export to same csv
next Order
do the same...

I have done the reverse and parsed a text file into Access, I need something similar to export it..

[NeoPa]

I think you may need to process through the two recordsets in VBA.

Basic DAO recordset loop using two recordsets is a template that should, at least, start you off.

[puppydogbuddy]

This won' t give me what I want as I need the order details and order items on seperate lines in the csv file. The select query will only put it all into one line.

I think I need to do it in VB, but I do not know the code.
Take Order details -> export to csv
Take Order item details -> export to same csv
next Order
do the same...

I have done the reverse and parsed a text file into Access, I need something similar to export it..

Ok, then try using a union query to extract the data on 2 lines (as shown), then export via transfertext method:

Select orderno, customer name, customer address from orders
Union
Select orderno, itemno, weight from order details
Order By orderno

[jellybabygm]

NeoPa

Thats the kinda thing I'm looking for..

Also found this..

................
Set rsData = New ADODB.Recordset
  With rsData
    .ActiveConnection = m_cnDatabase
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    
    .Source = "SELECT * FROM " & rst1
    
    .Open
    
    If (.State = adStateOpen) Then
      hFile = FreeFile
      Open "C:\Temp\orderimport.CSV" For Output As hFile
      
      Do Until .EOF
        sExportLine = ""
        For Each oField In .Fields
          sExportLine = sExportLine & oField.Value & ","
        Next
        
        sExportLine = VBA.Left$(sExportLine, Len(sExportLine) - 1)
        
        Print #hFile, sExportLine
        
        .MoveNext
      Loop
    End If
  End With..................

This looks like the kinda thing I need to write to a file, but it errors withType mismatch on the SELECT table name

[NeoPa]

No worries, but if you'd like me to look at this for you please let me know which line of the code the error message occurrs on.

[jellybabygm]

here is my code that works - any suggestions on tidying it up?
Or is it perfect :-) (doubt it)

Private Sub Command1_Click()

'set up error handler
On Error GoTo Proc_Err

Dim pRecordsetName As String
Dim pRecordsetName2 As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim r As DAO.Recordset, mFieldNum As Integer
Dim r2 As DAO.Recordset, mFieldNum2 As Integer
Dim mOutputString As String
Dim mFieldDeli As String
Dim myTime As String

myTime = Format(Date, "ddmmyy") & "-" & Format(Time, "hhmmss")
pRecordsetName = "FOPS Export Order Header"
pRecordsetName2 = "Fops Export Order Lines"
mPathAndFile = "C:\" & myTime & ".csv"
mFieldDeli = ","

'get a handle
mFileNumber = FreeFile

'close file handle if it is open
'ignore any error from trying to close it if it is not
On Error Resume Next
Close #mFileNumber
On Error GoTo Proc_Err

'open file for output
Open mPathAndFile For Output As #mFileNumber

'open the recordset
Set r = CurrentDb.OpenRecordset(pRecordsetName)

'loop through all records
Do While Not r.EOF()

'tell OS (Operating System) to pay attention to things
DoEvents
mOutputString = ""
For mFieldNum = 0 To r.Fields.Count - 1
mOutputString = mOutputString _
& r.Fields(mFieldNum) _
& mFieldDeli

Next mFieldNum

'write a line to the file
Print #mFileNumber, mOutputString

                'Process Order Line
                Set r2 = CurrentDb.OpenRecordset(pRecordsetName2)
                Do While Not r2.EOF()
                If r("Order Number") = r2("Order Number") Then
                    'tell OS (Operating System) to pay attention to things
                    DoEvents
                    mOutputString = ""
                    For mFieldNum2 = 0 To r2.Fields.Count - 1
                    mOutputString = mOutputString _
                    & r2.Fields(mFieldNum2) _
                    & mFieldDeli
                    
                   Next mFieldNum2
                                        
                    'write a line to the file
                    Print #mFileNumber, mOutputString
                End If

                'move to next record
                r2.MoveNext

                Loop

                r2.Close
                Set r2 = Nothing

'move to next record
r.MoveNext

Loop

MsgBox "Done Creating " & mPathAndFile, , "Done"

Proc_Exit:
On Error Resume Next
'close the file
Close #mFileNumber

'close the recordset
r.Close

'release object variables
Set r = Nothing

Exit Sub

'ERROR HANDLER
Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number _
& "   ExportDelimitedText"
'press F8 to step through code and correct problem
'comment next line after debugged
    Stop:   Resume
Resume Proc_Exit
End Sub

[NeoPa]

Nothing obviously wrong apart from the indenting.

When used properly it can be a great asset.
Otherwise it will just put everyone off from even looking I'm afraid (not to mention making your own job harder when you want to work on it again).

Anyway, the actual code seems fine.