Error 3047: Record is too large

[Imdabaum]

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.
[vba]
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
[/vba]

[Imdabaum]

I made it through about 100 iterations and when I quit to work on something else momentarily that Clipboard message popped up, "You have copied a lot of data onto the clipboard, do you want to keep the data on the clipboard?" vbyes, vbno.

Does the Update method use the clipboard at all?
**
Update:
(it doesn't help to empty the clipboard)

[OBP]

Have you tried a simple Append Query rather than VBA?
Have you ensured that the Field Data Types match, not trying to put a Memo in to Text field for instance?

[Imdabaum]

Append query does not work.

I copied the SQL Server table and pasted Definition only so all fields should be exactly the same.

I then did an unmatched query to find the missing records from the local table that were in the production data (this was all of the production records).

Then I did an append query to append all the data in missingquery to the local table. It does not work I get this same error message stating that the record is too large. This apparently happens if the record contains more than 2K of data. Help suggests that I split the data into tables.. A great idea if I had control over the server and anything else related to the data here.

[hansup]

If you have several text fields which contain hundreds of characters you could try converting them to memo in Access. The memo data type doesn't contribute to the record width limit. (Or contributes only the size of the pointer to the memo field data ... I forget.)

If changing those data types in Access doesn't gain you enough breathing room, then I think you have to split the wide rows into separate Access tables.

Say for example the Sql Server source table consists only of a primary key field, id, and 4 fields named f1, f2, f3, and f4. Then from Access you can:

[vba]INSERT INTO LeftHalf (id, f1, f2)
SELECT id, f1, f2 FROM SourceTable;[/vba]
and:

[vba]INSERT INTO RighttHalf (id, f3, f4)
SELECT id, f3, f4 FROM SourceTable;[/vba]
Do your data cleaning operations in Access with LeftHalf and RightHalf. Then when it's time to send the clean data up to the new server table:

[vba]INSERT INTO NewSourceTable (id, f1, f2, f3, f4)
SELECT a.id, a.f1, a.f2, b.f3, b.f4
FROM
LeftHalf AS a
INNER JOIN RightHalf AS B
ON a.id = b.id;[/vba]
It seems kind of fiddly, but it works. You said you can't alter the table structure on the server; this way you don't have to.

[Imdabaum]

Very nice. Thanks. I'll give that a try. That's going to be fun with 244 fields. But hey. It makes a nice even number. 167 fields per table. Yippee.



----Why can't people just normalize their databases?

[hansup]

Ouch! You sure sure got my sympathy ... for whatever that's worth.