Imdabaum
08-17-2010, 12:53 PM
I'm in the process of a migration here where all our data is going to be moved to a new data store.
However as part of that, they want me to run scripts and such to clean the data and make it valid for the requirements in the new store.
My boss asked that I copy all the tables to a local access database and perofrm these functions away from the current production data. No biggie, Access handles that fine.
The problem comes when one table... the last table to stand in my way of completion, fails to copy. I have written a VBA process in an attempt to handle the error, but running an append query will not work (gives error:3047 record is too large)
So I'm trying to start from the ground up and rethink my strategy.
Here's what I have so far.
On Error GoTo MigrateFSLocal_Error
Dim rstDest As DAO.Recordset
Dim rstSource As DAO.Recordset
Dim db As Database
Set db = CurrentDb()
Set rstDest = db.OpenRecordset("F/S2", dbOpenDynaset)
Set rstSource = db.OpenRecordset("Missing FS Record", dbOpenDynaset, dbSeeChanges)
While Not rstSource.EOF
With rstDest
.AddNew
For i = 0 To .Fields.count - 1
'Debug.Print .Fields(i).Name & ": " & rstSource.Fields(i).Name
iMarker = i
.Fields(i) = rstSource.Fields(i)
Next i
.Update
End With
rstSource.MoveNext
count = count + 1
Wend
GracefulExit:
rstDest.Close
rstSource.Close
Set db = Nothing
Set rstSource = Nothing
Set rstDest = Nothing
Exit Function
MigrateFSLocal_Error:
If Err.Number <> 3047 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MigrateFSLocal of Module Module1"
Resume GracefulExit
Else
'If the record is too large, then skip that record so I can analyze it in the Missing query.
' Or if there is a way to identify which record is going to fail (exceed 200Kb?)
' help??
Resume Next
End If
However as part of that, they want me to run scripts and such to clean the data and make it valid for the requirements in the new store.
My boss asked that I copy all the tables to a local access database and perofrm these functions away from the current production data. No biggie, Access handles that fine.
The problem comes when one table... the last table to stand in my way of completion, fails to copy. I have written a VBA process in an attempt to handle the error, but running an append query will not work (gives error:3047 record is too large)
So I'm trying to start from the ground up and rethink my strategy.
Here's what I have so far.
On Error GoTo MigrateFSLocal_Error
Dim rstDest As DAO.Recordset
Dim rstSource As DAO.Recordset
Dim db As Database
Set db = CurrentDb()
Set rstDest = db.OpenRecordset("F/S2", dbOpenDynaset)
Set rstSource = db.OpenRecordset("Missing FS Record", dbOpenDynaset, dbSeeChanges)
While Not rstSource.EOF
With rstDest
.AddNew
For i = 0 To .Fields.count - 1
'Debug.Print .Fields(i).Name & ": " & rstSource.Fields(i).Name
iMarker = i
.Fields(i) = rstSource.Fields(i)
Next i
.Update
End With
rstSource.MoveNext
count = count + 1
Wend
GracefulExit:
rstDest.Close
rstSource.Close
Set db = Nothing
Set rstSource = Nothing
Set rstDest = Nothing
Exit Function
MigrateFSLocal_Error:
If Err.Number <> 3047 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MigrateFSLocal of Module Module1"
Resume GracefulExit
Else
'If the record is too large, then skip that record so I can analyze it in the Missing query.
' Or if there is a way to identify which record is going to fail (exceed 200Kb?)
' help??
Resume Next
End If