Breaking down a field

[Eddie Mason]

I have a database that contains a single address field with each line of the address entered onto a different line e.g.:
1 West Way
Southampton
Hampshire

What I need to do is to migratate this to a new database that has a field for each line eg:
Address1
Address2
Address3, etc.

I need to migrate about 4,000 records is there a way that this can be done programmatically.

Hope that someone can help.

Kind regards,

Eddie

 

[Beetle]

Assuming each instance of the old address field is always 3 lines, you could;

1) Create a recordset based on the old table.
2) Loop through the recordset and Split the address field into an array using Chr(10) (new line) as the delimiter.
3) Assign each element of the resulting array to a new address variable.
4) Insert the existing data + the new address variables into a new table.

Example code follows;

Dim varArray As Variant
Dim strSelect As String
Dim strInsert As String
Dim strAddress1 As String
Dim strAddress2 As String
Dim strAddress3 As String
Dim rs As DAO.Recordset
 
strSelect = "Select ClientID, ClientName, ClientAddress From tblOldTable"
 
Set rs = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
 
With rs
    .MoveFirst
    Do While Not .EOF
        varArray = Split(!ClientAddress, Chr(10))
 
        strAddress1 = varArray(0)
        strAddress2 = varArray(1)
        strAddress3 = varArray(2)
 
        strInsert = "Insert Into tblNewTable (ClientID, ClientName, Address1, Address2, Address3) " _
                  & "Values (" & !ClientID & ", """ & !ClientName & """, """ & strAddress1 & """, """ _
                  & strAddress2 & """, """ & strAddress3 & """)"
 
        CurrentDb.Execute strInsert, dbFailOnError
 
    .MoveNext
    Loop
End With

If the old address field can be a random number of lines then the above approach would need to be modified.

 

[Eddie Mason]

I'm strugling getting the module to work. I've created a test database with two tables: tblOldTable and tblNewTable with the fields as named in the example, however when I try to run the code it stops highling the following line:
strSelect = "Select ClientID, ClientName, ClientAddress From tblOldTable"
with a message box: Compile Error - Invalid outside procedure.

Kind regards,

Eddie

 

[spikepl]

The MS employee who invented - or approved - this ambiguous message text ought to be hanged, drawn and quartered.

The message means that your code must be inside a Sub or Function. Put Sub MySub first and End Sub last and you should be good.

 

[Eddie Mason]

Hi spikepl

Many thanks for the advice its working perfectly now.

Kind regards

Eddie

 

[Beetle]

My apologies. I should have mentioned that you would need to place the code inside a procedure or function.

Glad it worked for you.