VB Forum / Database Access / June 2007

Hi, All,

   I created a small tool using VB6, in which I needed to insert some data
from Sybase database into an EXCEL file. I use an EXCEL template file with
only one row which will be recognized as each column name when connected
using ADO.  It's OK to open the EXCEL file using ADO, and add new records to
it.
   However, my problem is, each time when I connect the EXCEL file, I need
to delete existing records before adding new records, but I got error message

   I use VB ADODC control to do this, this is my code :

============================================
   On Error GoTo ExcelConnectError

   Dim strFileName     As String
   
   strFileName = "c:\Excel.xls"
   adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & strFileName & ";" & _
                                    "Extended Properties=""Excel
8.0;HDR=YES;"""
   adodc1.RecordSource = "[" & "Sheet1$" & "]"
   
   adodc1.Refresh

   Dim nCount   As Long
   Dim nCnt        As Long

   nCount = adodc1.Recordset.RecordCount

   If Not adodc1.Recordset.BOF Then
       adodc1.Recordset.MoveFirst
   End If

   If nCount > 0 Then
       adodc1.Recordset.MoveLast

       For nCnt = 0 To (nCount - 1)
           adodc1.Recordset.Delete
       Next

       adodc1.Recordset.Update
   End If
   
ExcelConnectError:
   strMsg = "File open error: " + strFileName
   MsgBox strMsg, vbOKOnly, "ADO"

==========================================