TABLE Manipulation Coding...

[GUIDO22]

Hi
I have a CSV file with a number of records therein.
I import the file into Access and I DROP all unwanted columns.
But, I also need to add a couple of columns too.
Here is a summary of the tasks I need to complete...

1) ADD a date (simple enough) column - but I specifically need the date in the following format :dd-mm-yyyy (note the - seperator is not the default '/' character)

2) I also need to physically move one of the new columns that I have added (which is appended to the rightmost column) and place it to the immediate left such that it becomes the first column in the table

3) I need to force the default table order to critiera that I stipulate

4) I then need to output the table to CSV file again.

Oh, did I mention I need to do all of this from VBA code.....
Such that I can have all this in a module to run on a single button press.

Thanks in advance for any suggestions how to accomplish.

 

[MarkK]

Presumably you import this csv file into a table. Most of what you need to do you can do by modifying the query you use to retrieve data from that table.

1) a date column in a table is stored as a number. All formatting is done at retrieval time. Format this field in your query. Check out the Format() function and a format string like, "dd-mm-yyyy"

2) reorder the columns in your query. If your table has column order . . .

Field1, Field2, Field3

. . . write a query with column order . . .

SELECT Field3, Field2, Field1 FROM Table

. . . so again, format your data at retrieval time.

3) Do this with an SQL WHERE clause in your query.

4) Here's a loop within a loop that can write any recordset to a string

dim rst as dao.recordset
dim fld as dao.field
dim row as string
dim csv as string

set rst = currentdb.openrecordset("YourQueryHere")
do while not .eof
   for each fld in .fields
      row = row & "," & fld.value
   next
   csv = csv & mid(row, 2) & vbcrlf
   row = ""
   .movenext
loop

hope that helps,

 

[spikepl]

1) ADD a date (simple enough) column - but I specifically need the date in the following format :dd-mm-yyyy (note the - seperator is not the default '/' character)

SO what is keeping you? Dates are stored the way they are stored. The display of the dates YOU determine by setting the format of that column to what you want, or the format of the control in which the date is displayed to what you want.

2) I also need to physically move one of the new columns that I have added (which is appended to the rightmost column) and place it to the immediate left such that it becomes the first column in the table

Why? You can present data in a query in whichever way you like, independent of the order of the columns in a table.

3) I need to force the default table order to critiera that I stipulate

There is no such thing as "default table order", tables do not have any built-in order. When you are looking at it you are looking through a query. If you want to show the table in some order, use a query in the form in which you show the table. You want to show the table directly? Don't! - that is not done.

4) I then need to output the table to CSV file again.

So what specifically is stopping you ? Query or table, same same. Use the export wizard.

 

[GUIDO22]

Thanks for the input people - I have actually managed to accomplish most of what I wanted to do using the handy DoCmd.TransferText () method, the column and ordering criteria can be stipulated for the format of the output file.