Option Compare Database
Option Explicit

Dim idxReg As RegExp
Dim idxMatches As MatchCollection

Public Function ExtractIndexFromAddress(ByVal SourceStr As String) As String
   If idxReg Is Nothing Then CreateIdxReg
   Set idxMatches = idxReg.Execute(SourceStr)
   If idxMatches.Count >  0  Then
      ExtractIndexFromAddress = idxMatches.Item( 0 )
   End If
End Function

Private Sub CreateIdxReg()
   Set idxReg = New RegExp
   idxReg.Pattern = "\d{6}"
End Sub

UPDATE Table1 SET aIndex = ExtractIndexFromAddress(Address);

UPDATE Table1 SET Table1.aIndex = ExtractIndexFromAddress(Address), Table1.Address = Replace(Address,aIndex,"");

UPDATE Table1 SET Table1.aIndex = ExtractIndexFromAddress(Address), Table1.Address = Replace(Address,aIndex,"");

UPDATE Table1 SET Table1.aIndex = ExtractIndexFromAddress(Address)
UPDATE Table1 SET Table1.Address = Replace(Address,aIndex,"");

UPDATE Table1 SET Table1.aIndex = ExtractIndexFromAddress(Address)
, Table1.Address = Replace(Address,ExtractIndexFromAddress(Address),"");