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...
8 1346
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"
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..
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
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Krzys! |
last post by:
I'd like to create a table dynamicly. List of column in this table
should be taken from select: "select distinct fiel from table "
How to do it ?
tnx in advance for help
K.
|
by: Jonathan |
last post by:
We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need...
|
by: The_Original_MB |
last post by:
I have a task to create tables dynamically, using the javascript DOM.
The current method uses a 1px x 1px IFRAME to loop through some data
generation stuff, and then call JS functions in the parent...
|
by: Mark Lees |
last post by:
I need to create a report based on 4 tables
1. tbl_KidsList
2. tbl_Sub_Address
3. tbl_Sub_Phone
4. tbl_Sub_Parents
Tables 2-4 are actually subforms that are placed upon the KidsList
form.
...
|
by: Chris via AccessMonster.com |
last post by:
I have the challenge of needing to automate table creation. It doesn't end there the tables have to be created from one table that looks something like this...
Email ...
|
by: Stuart McGraw |
last post by:
I haven't been able to figure this out and would
appreciate some help...
I have two tables, both with autonumber primary
keys, and linked in a conventional master-child
relationship. I've...
|
by: Wilfried Mestdagh |
last post by:
Hi,
Using C# beta 2004 Express, I just downloaded sql beta 2005 express. But now
some questions:
1. Some turorials available somewhere to:
- create a database in code
- change structure of...
|
by: charliewest |
last post by:
I need to create textboxes in real-time, the actual number of which is
determine by a result from a database query. I have been able to create the
controls, and then add them to the ASPX page....
|
by: Jimmy M |
last post by:
I've got a page that generates dropdowns and text boxes based on
database data. I have them all set to auto-postback because I'll be
using this with Atlas to make a more seamless user experience....
|
by: shashi shekhar singh |
last post by:
Respected Sir,
I have to create multiple dynamic dropdownlist boxes and add items dynamically in <asp:table> server control but problem occurs , i.e. except of fist dropdown list no dropdownlist...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |