VB Forum / Database Access / July 2007

Dear All,

how to write the to Ms Excel File by retireve data from SQL Server.

below src code i use:

Public Sub Write2Excel()
Dim db_connect
Path = "C:\Book1.xls"
   Set db_connect = New ADODB.Connection
    Dim rse As New ADODB.Recordset
   Dim rs As New ADODB.Recordset
   
   db_connect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Path &
"; Extended Properties=Excel 8.0;"
   'db_connect2.Open "PROVIDER=SQLOLEDB;Data Source=GHSERP;Initial
Catalog=GHS;User ID=sa;Password=admin123"
 
    SQL = "Select OrderNo From [Sheet1$]"
   
    Set rse = db_connect.Execute(SQL)
    rs.Open ("select * from ExportDetail"), sqldb
'sqldb is PROVIDER=SQLOLEDB;Data Source=OSTRIO10;Initial
Catalog=BUsinessUnit;User ID=sa;Password=admin;
    Do While Not rs.EOF
    With rse
    .AddNew
    .Fields(0) = rs.Fields(0)
    .Update
    End With
    rs.MoveNext
    Loop

    'SQL = "Insert Into Shet1$(OrderNo) values('" & rs.Fields(0) & "')"
   'db_connect.Execute (SQL)
    MsgBox "finish"

End Sub

run-time error '3251':

Current Recordset does not support updating. This may be a limitation of the
provider, or or the selected locktype.

Kindly advice

thank you

best regards,
Carmen