Table Row to CSV string function???

[reggiemack]

I'd like to convert any particular table row into a Comma Separated Value string and move the string into a memo field. This is to be done as a part of a transaction logging subsystem of an existing application to store the before snapshot of a row that's been updated.

Is there any built-in functionality to do this?

Best Pratice?

[MMcCarthy]

I'd like to convert any particular table row into a Comma Separated Value string and move the string into a memo field. This is to be done as a part of a transaction logging subsystem of an existing application to store the before snapshot of a row that's been updated.

Is there any built-in functionality to do this?

Best Pratice?

I don't know of any built-in fucntion but you could always use vba to create a string of the values with commas inserted between the values.

[nico5038]

Why the effort of creating a string in a Memo field?
It will ask extra processing and introduce problems reverting the process when (one or more of) the fields hold also a comma.....

I simply use a copy of the original table with an additional DateTime field holding a timestamp (Use the Now() function).
By adding this field to the unique key you have an excellent log table that can be exported into a .csv once in a while to store the data off-line.

Idea ?

Nic;o)

[MMcCarthy]

Why the effort of creating a string in a Memo field?
It will ask extra processing and introduce problems reverting the process when (one or more of) the fields hold also a comma.....

I simply use a copy of the original table with an additional DateTime field holding a timestamp (Use the Now() function).
By adding this field to the unique key you have an excellent log table that can be exported into a .csv once in a while to store the data off-line.

Idea ?

Nic;o)

Sounds good to me Nico. It's what I would do.

BTW, thanks for looking at that other question for me. I appreciate your prompt responses to my little requests. ;)

Mary

[reggiemack]

Thank-you for your responses! I should have elaborated on the requirements a bit more but I tried to keep it simple. There are several factors that led to the CSVString to Memo field approach.

The application is comprised of multiple tables (MANY).

The application is transaction based with each transaction updating multiple tables.

Each transaction must be captured in a transaction log.

Each log entry must capture the entire transaction (I.e., the before images of all of the table rows involved in the transaction).

All tables involved in a particular transaction are disparate in length, number of columns, format and content. All of the tables' attributes are either numbers or short text (names, addresses).

The transaction log will be utilized additionally for undo/redo functionality as well as a significant piece of a disaster recovery process/procedure.

Comments?

WHEW ;-)


Now, in response to the posts:

Ordinarily, I would have choosen the same approach recommended by your posts. But since this application involves many tables in a given update situation and that the multi-table update must be captured in a transaction log entry and that all of the tables are "disparate in length, number of columns, format and content", it seemed appropriate to take advantage of memo field's atributes as a very flexible datatype for this type of use. (I.e., containing multiple CSVStrings delimited by table-name/colon and semi-colon/EOL.)

In addition, any embedded commas are easily mitigated by quoted text strings. (BTW,There are no quotes as part of the text within the application.)

Is there a variation on your recommendation that you may have worked with before that may address the above requirements?

I welcome any methodologies, comments, suggestions, etc., addressing the above requirements. Thanks to all who have and will respond!

R

[nico5038]

I would still use a log table per table and add a transactionID (sequencenumber and/or datetimestamp) to be able to extract the complete transaction afterwards.

Another alternative that's really flexible would be to have one log table like:
TransID
UniqueKeyOfChan gedTable
TableName
Fieldname
FieldOldValue

The FieldOldValue can be a textstring able to hold all different values or you could use an OldValue per datatype.

Nic;o)

[reggiemack]

I don't know of any built-in fucntion but you could always use vba to create a string of the values with commas inserted between the values.

What would the VBA code look like to do this generically, given any table row presented to this code. Ideally, I'd like to input some sort of table row reference for 1 of any of numerous tables and output a comma separated string of values equivalent to the number of columns of the table row. Thanks!

R

[NeoPa]

Does this help at all?

'ToCSV Converts the parameters to a Comma Separated Values record.
Public Function ToCSV(ParamArray avarArgs() As Variant) As String
    Dim strParam As String
    Dim intIdx As Integer

    ToCSV = ""
    For intIdx = LBound(avarArgs) To UBound(avarArgs)
        strParam = avarArgs(intIdx)
        If InStr(1, strParam, ",") > 0 Then strParam = """" & strParam & """"
        ToCSV = ToCSV & "," & Nz(avarArgs(intIdx), "")
    Next intIdx
    ToCSV = Mid(ToCSV, 2)
End Function

To call it you would enter each field from the record in the arguments list.
EG. If you had a Table with Four fields (ID; Name; Age; Phone) you would call it as :

StrLog = ToCSV([ID], [Name], [Age], [Phone])