sairam123
11-21-2013, 02:39 PM
Hi....
i need records count from a query in vba....
iam uploading one excel into my macro...my excel writes its rows into database....i need to count no of records that are inserted....
My code here follows...
Dim cn As ADODB.Connection
Dim cn1 As ADODB.Connection
set cn = new ADODB.Connection
set cn1 = new ADODB.Connection
Dim rs As ADODB.Recordset
set rs = new ADODB.Recordset
Dim rs1 As ADODB.Recordset
set rs1 = new ADODB.Recordset
Dim cmdSQLData As ADODB.Command
set cmdSQLData =New ADODB.Command
Dim cmdSQLData1 As ADODB.Command
Set cmdSQLData = new ADODB.Command
cn1.open (here i called one function for DBconnection)
Set cmdSQLData1.ActiveConnection=cn1
If filetype="XYZ" Then
QueryA= select count(version),max(version)+1 from source_db.abc_vw where SUBMISSION_PERIOD=' " & subperiod & " ' "
Debug.Print queryA
cmdSQLData.CommandText=queryA
cmdSQLData.CommandType=adCmdText
cmdSQLData.CommandTimeout=0
Set rs=cmdSQLData.Execute()
for x=2 to rows...........//////To read from uploaded file //////
QueryB=insert statement(inserting into source DB)
cmdSQLData.CommandText =queryB
cmdSQLData.CommandType=adCmdText
Debug.print queryB
Set rs=cmdSQLData.Execute()
by this above code i think record are inserted into database.....
Here i need code for how many records were inserted into datbase....upto my knowledge i written the following code...
i taken variables as ,
Dim rscount as integer
Dim recordcount as integer
rscount=rs.recordcount
msgbox "No of records inserted " & rscount
iam getting error message err 1004..
please suggest me..
Thanks...
i need records count from a query in vba....
iam uploading one excel into my macro...my excel writes its rows into database....i need to count no of records that are inserted....
My code here follows...
Dim cn As ADODB.Connection
Dim cn1 As ADODB.Connection
set cn = new ADODB.Connection
set cn1 = new ADODB.Connection
Dim rs As ADODB.Recordset
set rs = new ADODB.Recordset
Dim rs1 As ADODB.Recordset
set rs1 = new ADODB.Recordset
Dim cmdSQLData As ADODB.Command
set cmdSQLData =New ADODB.Command
Dim cmdSQLData1 As ADODB.Command
Set cmdSQLData = new ADODB.Command
cn1.open (here i called one function for DBconnection)
Set cmdSQLData1.ActiveConnection=cn1
If filetype="XYZ" Then
QueryA= select count(version),max(version)+1 from source_db.abc_vw where SUBMISSION_PERIOD=' " & subperiod & " ' "
Debug.Print queryA
cmdSQLData.CommandText=queryA
cmdSQLData.CommandType=adCmdText
cmdSQLData.CommandTimeout=0
Set rs=cmdSQLData.Execute()
for x=2 to rows...........//////To read from uploaded file //////
QueryB=insert statement(inserting into source DB)
cmdSQLData.CommandText =queryB
cmdSQLData.CommandType=adCmdText
Debug.print queryB
Set rs=cmdSQLData.Execute()
by this above code i think record are inserted into database.....
Here i need code for how many records were inserted into datbase....upto my knowledge i written the following code...
i taken variables as ,
Dim rscount as integer
Dim recordcount as integer
rscount=rs.recordcount
msgbox "No of records inserted " & rscount
iam getting error message err 1004..
please suggest me..
Thanks...