Records Count

[sairam123]

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...

[mrojas]

In order to use the .RecordCount method you must first declare and initialize the required variables, and then create a recordset. Here's an example of what I've used in the past:

Dim dbs As Database
    Dim rst As Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("myTable")
If rst.RecordCount = 0 then
   'Display a message

else
    ' Display message
endif

[sairam123]

Here inserted records are into queryB....
i need total no of records that are inserted into my sourceDB

i need one clarification in above....
why did u mentioned rst.Recordset=0 in if statement....
I don'think,if statement works here....
plz suggest me...

[mrojas]

The "If rst.RecordCount = 0 Then" prevents a runtime error should the recordset be empty. If the recordset has records, control is passed to the Else statement, after which you may display a message stating the number of records.

MsgBox "This many records were inserted: " & rst.RecordCount

[sairam123]

Hi,

Iam getting error as compile error.....at Dim dbs As Database

currentdb()------------i need to mention my current sourceDB over there...?


The code which u mentioned will work for ADODB ....

If not please give code for ADODB...

[sairam123]

database connections are there,,,my code is working fine...additionally iam adding needs to count no of records that are inserted into DB....

iam using ADO...