Hi Old Pedant
Thanks again for your interest. Good news! I finally got it to work. Bad news! I don't remember how - I made so many changes, moved things around, renamed files, detached and re-attached databases, etc. that I lost track completely. However, the outcome was worth it.
There is one point I'd like to make however. One of the tables I was migrating was empty and this caused an error in the 'Finally' section of the published code due to the fact that because there were no rows in the DataReader, the objData.command didn't exist, so it couldn't be disposed! I changed the code so that the 'cleanup' was contained within the 'If' - 'End If' section as below and removed the 'Finally' section completely:
Code:
If .DataReader.HasRows Then
......
'Process all rows
While .DataReader.Read
.......
'Execute the INSERT statement
objData.Command.ExecuteNonQuery()
EndWhile
'cleanup
objData.Command.Dispose()
objData.Command = Nothing
EndIf
EndWith
'close the DataReader
objAccessDB.DataReader.Close()
.......
This avoids the problem. Maybe someone will find it useful!