transfer data from one table into another and then into excel sheet

[progvar]

Hi!
CAN I USE THE FOLLOWING CODE FOR TRANSFERING THE DATA FROM ONE TABLE INTO ANOTHER AND THEN INTO EXCEL SHEET BUT WHEN I RUN THE PROGRAME IT PRODUCES THE ERROR "OPERATION IS NOT ALLOWED WHEN THE OBJECT IS CLOSED " AND IT IS ON THE LINE
" fldcount = rs2.Fields.Count" IT IS NOT COUNTING THE FILED
THE VALUE IN fldcount =0 but IT SHOULD BE fldcount=2

please check the code and give some idea iam sending little bit of code

con.Open "Driver={SQL Server};Server=" & listserver & "; Database=stu;Uid=sa;Pwd=" & txtpassword & ";"

Str1 = "DROP TABLE New_Rec "
On Error GoTo Err
rs.Open Str1, con, adOpenDynamic, adLockBatchOptimistic, adCmdText

Str2 = "CREATE TABLE New_Rec (Rec_Id numeric, Rec_No integer)"
rs1.Open Str2, con, adOpenDynamic, adLockBatchOptimistic, adCmdText
squery = "INSERT INTO New_Rec SELECT * FROM OUTPUT"
rs2.Open squery, con, adOpenDynamic, adLockBatchOptimistic, adCmdText

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\fin.xls")
Set xlWs = xlWb.Worksheets("sheet1")

xlApp.Visible = True ///// THE PROGRAME IS WORKING CORRECT UPTO THIS LINE
fldcount = rs2.Fields.Count ////// ERROR IS HERE
recarray = rs2.GetRows
reccount = UBound(recarray, 2) + 1

[QVeen72]

Hi,

"Inserts/Updates/Deletes" should be executed on a Command Or Connection Object..
Split your Insert into 2 commands:

Str2 = "CREATE TABLE New_Rec (Rec_Id numeric, Rec_No integer)"
Con.Execute str2
squery = "INSERT INTO New_Rec SELECT * FROM OUTPUT"
Con.Execute squery
 
squery = "Select * From New_Rec "
rs2.Open squery, con, adOpenDynamic, adLockBatchOptimistic, adCmdText
fldcount = rs2.Fields.Count   
recarray = rs2.GetRows
reccount = UBound(recarray, 2) + 1
 

I guess, if you are using New_Rec as a Temporary Table, then no need to Drop it and Re-Create every time.. Just Deleteing Records from the table would suffice..

Regards
Veena