Discussion Locked

String together Comments in Access

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have to import data from a RS6000 application into Access 2000 to run a report.
With this data there is a comment field in the RS6000 application one work order will show all the comments for that work order, but in Access each line of the comment is a separate record. I need to write a procedure that will string together the comments for each Work order into one field (a memo field) I am not sure how to start? Below is a list of the fileds and thier types
ID (auto Number)
WorkOrder (Text)
Customer (text)
Make (text)
Model(text)
Serial(text)
DateCompleted(Date/Time)
DateOpened(Date/Time)
KohlerId(text)
KohlerAsset(text)
DeptNumber(text)
Comment(text)
Can I run something based on the work order?

5 replies

by mslizny

Each line is coming in because there is some delimiter, such as a tab or comma, between each part of the comment.

I do a lot of data massaging in Excel to get Access to work with my data, and I would suggest bringing the material, into Excel, anduse the text function Concatenate to assemble the pieces of the comment from the side-by-side columns, use copy to copy the concatenated data and then "Paste Special" to paste just the "values" into a new column. Delete the columns with the separated pieces and the column with the concatenate formula, rename the column with the "Paste Special" data "Comments" and import the data back into Access.

If you can only move the data into Access first, then bring it into an Access table so that each piece of the comment is in a separate field, export the table into Excel, and proceed as described above.

by dennisbv

I am really looking for a VBA procedure to do this. I donot have the time to do all the work. Basicly I am doing it the way you describe.

by andrew

Answer 1 will work as long as the amount of data is relatively small (if you have thousands of records it would be laborious).

You could right a small VB app to grab the data from the RS6000 system, massage the comment/memo data, and insert the new record into the Access table.

Feel free to email me for details if this sounds like the way you want to go.

by dennisbv

I have inserted the answer so it will help others.
The code would be something like this:

Dim oConRS6000 as ADODB.Connection
Dim oConAccess as ADODB.Connection
Dim oRs as ADODB.Recordset
Dim oRsAccess as ADODB.Recordset
Dim oCmd as ADODB.Command

Set oConRS6000 = New ADODB.Connection
Set oConAccess = New ADODB.Connection
oConRS6000.Open "RS6000_DSN"
oConAccess.Open = "AccessDSN"

Set oCmd = New ADODB.Command
oCmd.CommandType = adCmdText
oCmd.CommandText = "SELECT * FROM RS600Table"
oCmd.ActiveConnection = oConRS6000
Set oRs = New ADODB.Recordset
oRs.CursorLocation = adUseClient
oRs.Open oCmd, , adOpenForwardOnly, adLockReadOnly 'use the open method NOT the cmd.Execute
Set oCmd.ActiveConnection = Nothing 'disconnect

'let's create an empty Access recordset that we'll fill with data...

oCmd.CommandType = adCmdText
oCmd.CommandText = "SELECT * FROM AccessTable"
oCmd.ActiveConnection = oConAccess
Set oRsAccess = New ADODB.Recordset
oRsAccess.CursorLocation = adUseClient
oRsAccess.Open oCmd, , adDynamic, adLockBatchOptimistic 'use the open method NOT the cmd.Execute 'this will give us blank recordset

'ok, nowwe have all the RS6000 data in a recordset, so let's loop through it and add it to the the Access recordet...

do
oRsAccess.AddNew Array([field names...]),Array([values from oRs fields...]) 'add a new record to the access recordset
oRs.MoveNext
loop until oRS.EOF

oRsAccess.UpdateBatch 'add the new rows to the Access db

'clean up...
oRs.Close
oRsAccess.Close

This discussion is closed to new replies.