Microsoft KB Archive/254634

= PRB: Updating DAO Recordset from Excel Increases File Size =

Article ID: 254634

Article Last Modified on 1/11/2001

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Data Access Objects 3.0
 * Microsoft Data Access Objects 3.5
 * Microsoft Data Access Objects 3.6

-



This article was previously published under Q254634



SYMPTOMS
When you open a Data Access Object (DAO) recordset from an Excel spreadsheet and update a record, the size of the Excel file increases dramatically when the recordset is closed.



CAUSE
Jet rewrites an alternate form for the data to each cell in the worksheet; in cells that appear to contain data and those that do not. This alternate form causes the file size to inflate.



RESOLUTION
Use OLE Automation to open an instance of the workbook prior to opening the recordset. If the workbook file is already open by using OLE Automation, DAO builds the recordset using OLE Automation instead of using Jet. When the recordset is updated and saved, the Excel file size is approximately the same as before the recordset was opened.



Steps to Reproduce Behavior
 Open a new Visual Basic Standard EXE project. Form1 is created by default. On the Project menu, click to select References, and then select: Microsoft DAO 3.6 Object Library

-and-

Microsoft Excel Object Library 9.0

  Add a CommandButton control to Form, and set the following properties:     Control                 Name                Caption/Text Command Button          cmdUpdate          Update DAO Recordset   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  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.</li> 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.</li></ol>

Keywords: kbprb KB254634

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.