PRB: Updating DAO Recordset from Excel Increases File Size (254634)

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new Visual Basic Standard EXE project. Form1 is created by default.
2. On the Project menu, click to select References, and then select: Microsoft DAO 3.6 Object Library
   
   -and-
   
   Microsoft Excel Object Library 9.0
   
   
3. Add a CommandButton control to Form, and set the following properties:

        Control                 Name                Caption/Text
     Command Button           cmdUpdate          Update DAO Recordset
   					

4. Paste the following code into the General Declarations section of the form. If you don't have an existing Excel spreadsheet to use for a data source, you can create one by exporting a table from the NorthWind database using Microsoft Access. The Customers table was exported for this example.

   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim xlFileName As String
   
   Private Sub Form_Load()
       xlFileName = App.Path & "\Customers.xls"
       DisplayFileSize xlFileName, "Before DAO Update"
   '    Set xlObj = GetObject(xlFileName)  ' UnComment to retain file size
       Set db = OpenDatabase(xlFileName, _
                False, False, "Excel 8.0;")
       Set rs = db.OpenRecordset("Customers$")
       rs.MoveFirst
   
   End Sub
   
   Private Sub cmdUpdate_Click()
       rs.Edit
       rs.Fields("CompanyName").Value = "New Company"
       rs.Update
       rs.Close
       db.Close
       DisplayFileSize xlFileName, "After DAO Update"
       Unload Me
   End Sub
   
   Public Sub DisplayFileSize(sFile As String, sMessage As String)
       MsgBox sMessage & vbCrLf _
                & vbCrLf & "File Size is " & _
                Format(FileLen(sFile), "###,###,###."), _
                vbInformation, sFile
                
   End Sub
   					

5. Run the project. A message box reports the size of the Excel file before any processing takes place. Click the Update DAO RecordsetCommandButton control. A message boxes reports the Excel file size after the update. Note that the file size increases dramatically.
6. Uncomment the code in the form_load that starts with "Set xlObj = . . ." and run the project again. Note that when you click CommandButton for Update DAO Recordset again, the file returns to its original size.