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

[Bob Phillips]

Are yoiu just missing an opening quote before Select when you set the variable QueryA?

[sairam123]

queryA is there in my code..i didn't mention that one here...queryA does the activity like information about the file,who uploaded,it is going to process_tracking table...

i need t get row count or record count from queryB..beause there is insert statement...so all the uploaded rows in the file goes to DB here only..into SOURCE_DB.

i have some code here...getting error here......complie error....Dim dbs As Database.

currentDb() what i need to mention....

Dim dbs As Database 
Dim rst As Recordset Set dbs = CurrentDb() 
Set rst = dbs.OpenRecordset(queryB) 
If rst.RecordCount = 0 Then 
     'Display a message
     
Else 
     ' Display message
    endif 

[Bob Phillips]

There is no Database data type, so it is hardly surprising that you get a compile error when you dimension a variable to that data type.

You create a connection, which defines the database to be queried, setup some SQL, and return the query results to a recordset.

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

[Kenneth Hobs]

Please post code inside code tags. You have posted parts that have obvious syntax errors. It is very difficult to help without seeing code that works. Obviously, we know that you are using ADO.

There are many things that can cause the problem.

Look at this code and note how it checks the record count.

'http://msdn.microsoft.com/en-us/library/ms808325.aspx

Sub ADO()
'   Set Reference in Tools to: Microsoft ActiveX Data Objects 2.x Library
    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    Dim Col As Integer, Row As Long, s As String
    
'   Database information
    DBFullName = ActiveWorkbook.Path & "\NWind2003.mdb"
'Exit?
    If Dir(DBFullName) = "" Then Exit Sub

'Clear any existing data from activesheet
   Cells.Clear

'   Open the connection
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Cnct
    
'   Create RecordSet
    Set RecordSet = New ADODB.RecordSet
'   Record locking
  RecordSet.CursorType = adOpenKeyset
  RecordSet.LockType = adLockOptimistic
    
    With RecordSet
'       Filter
        'Src = "SELECT * FROM Products WHERE ProductName = 'Watch' "
        'Src = Src & "and CategoryID = 30"
        Src = "SELECT Orders.CustomerID, Orders.OrderDate " & _
          "FROM Orders " & _
          "WHERE (((Orders.OrderDate) " & _
          "Between #8/1/1994# and #8/30/1994#))"
        RecordSet.Open Source:=Src, ActiveConnection:=Connection

'       Write the field names
        For Col = 0 To .Fields.Count - 1
           Range("A1").Offset(0, Col).Value = RecordSet.Fields(Col).Name
        Next Col
        
'       Write the recordset
        Range("A1").Offset(1, 0).CopyFromRecordset RecordSet
        Dim a As Variant
        .MoveFirst
        a = RecordSet.GetRows
        MsgBox LBound(a), , UBound(a)
        MsgBox a(0), , a(1)

        If .RecordCount < 1 Then GoTo endnow
        .MoveFirst
        For Row = 0 To (.RecordCount - 1)
          'Debug.Print CStr(.Fields(Row).Value)
          .MoveNext
        Next Row
  End With
endnow:
    Set RecordSet = Nothing
    Connection.Close
    Set Connection = Nothing
End Sub

[sairam123]

can u please explain the above code...by keeping comments in the middle...i can't understand the code...iam learning and new to Vba....

[FONT=Verdana] Record locking....Why you are locking here.....
RecordSet.CursorType = adOpenKeyset
RecordSet.LockType = adLockOptimistic

        Range("A1").Offset(1, 0).CopyFromRecordset RecordSet 
        Dim a As Variant 
        .MoveFirst 
        a = RecordSet.GetRows 
        MsgBox LBound(a), , UBound(a) 
        MsgBox a(0), , a(1) 

  If .RecordCount < 1 Then Goto endnow 
        .MoveFirst 
        For Row = 0 To (.RecordCount - 1) 
             'Debug.Print CStr(.Fields(Row).Value)
            .MoveNext 
        Next Row 
    End With 
       'Write the recordset

please give ur explanation by line by line using comments....i know it is difficult for u...but i need to understand it...

[sairam123]

why you are using[FONT=Verdana]

a = RecordSet.GetRows 
        MsgBox [COLOR=#0000ff]LBound Filter,(a), , UBound(a) 
        MsgBox a(0), , a(1) 
Write the field names,CopyFromRecordset RecordSet 

please answer the above...

[sairam123]

Dim cmdsqldata As ADODB.Command
Set cmdsqldata =New ADODB.Command
Dim cmdSQLdata1 As ADODB.Command
Set cmdSQLData1=New ADODB.Command
cn1.opem GetDBconnectionString_source
Set cmdsqldata1.Activeconnection=cn1

Iam uploading total two types of excel files using excel Vba.
The uploaded files are inserting to database.

If fileType="xyz" Then
QueryA="select count(Track_ID) as cnt ,max ( p_number)+1 as prn from METADATA_DB.TRACKER_VW where ID = 4"
cmdsqldata1.commandText=queryA
cmdsqlData1.commandType= adCmdText
cmdsqldata1.CommandTimeout=0
Debug.print queryA
Set rs1= cmdsqlData1.Execute()

If rs1("cnt")=0 Then
prn=1
else
Prn=rs1("prn")
End If
Else 
If filetype=abc.....then also similar code here...
cn1.Close

This is for selecting files and executing insert statements into Database.

If fileType= "xyz" Then
queryA= "select count (version) as cnt,max(version)+1 as ver from SOURCE_DB.table_VW where submission_period=' "
Debug.print queryA
cmdsqldata.commandText=queryA
cmdsqldata.commandType=adCmdText
cmdsqldata.commandTimeout=0
set rs=cmdsqldata.execute()

Here there is for loop to insert all the fileds from the uploaded file...

For x=2 to rows.   ex: Range("A" & x)
So x value goes...

queryB= insert into SOURCE_DB... blah blah blah,..,
cmdsqldata.commandtext = queryB
cmdsqldata.commandatype=adcmdatext
cmdsqldata.commandtimeout=0
Debug.Print queryB
Set rs= cmdsqldata.execute()
rows = rows+1
Next x
END IF

set rs= Nothing
 
-------------------------
public function GetDBConnectString_Source() As String
Const str_connect= "Data source= RST; Database=SOURCE_DB;Persist Security Info= True;Session Mode=ANSI;"
'My connection details to database here...
End Function

My code is there as above now...i need now that no of records are inserted into database..i need records count...


If fileType

[Bob Phillips]

You must put your SQL inside of quotes.

That aside, I am completely lost as to what you are trying to do and specifically what help you need.

[sairam123]

Can i get number of records that are inserted into SOURCE_DB from queryB...
i think we can get with Recordset..if so please give ur code..
if there is a way to get no of records that are inserted from queryB..plz give me the code,how to get it...

Thanks in advance....

[SamT]

[FONT=Verdana] is an artifact of VBA Express. The Font tags are hidden inside the code boxes on the page. When you copy text from a code box, it also copies the font tags automatically. Then, when you paste it also pastes the font tags.

If you paste into Notepad first, then copy from Notepad, it will not paste the Font tags. Or you can manually delete them from the VBA Express editor.

[sairam123]

[FONT=Verdana] is an artifact of VBA Express. The Font tags are hidden inside the code boxes on the page. When you copy text from a code box, it also copies the font tags automatically. Then, when you paste it also pastes the font tags.

If you paste into Notepad first, then copy from Notepad, it will not paste the Font tags. Or you can manually delete them from the VBA Express editor.

Originally Posted by ruby123
Thank you for ur reply...


Can u please explain with a sample code...


Thanks...
Just like you did with "Rows = Rows + 1" except you should use another variable beside "Rows". "Rows" is the iterator variable for the loop, you should only read it inside the loop and never change it.




Hi....


Thanks for your reply...


we are not taking header count....I will explain with the following code,where we used in VBA while uploading sheet into excel...
Here my sheet also an excel....VBA excel is uploading one excel from outside path...

VarSheetA= worksheets("sheet1").Range(strRangeToCheckA)
'Here strRangeToCheckA is the range on the uploaded file/sheet that needs to checked
Set wbkB= Workbooks.open(Filename:=path)
Set varsheetB = wbkB.worksheets(sheetName).Range(strRangeToCheck)


'Determine number of rows in the file being uploaded
rows=Lastrow(wbkB.worksheets(sheetName).Range(strRangeToCheck)

Here I kept one message box for that no of rows being uploaded....
MsgBox " no of rows being uploaded is " & rows-1 'Header row is excluded in the count here....

If( sheetName = "xyz")Then
  if (wbkB.Worksheets (sheetName).Range ("A" & rows) =" ") Then
rows=rows-1
End If
EndIf

Coming to my answer is I think rows are adding there ...by rows+1..


The above code is only for uploading no of rows into an excel...
After uploading this rows,it is going to inserting into Database...


That code is mentioned here....I need records count that how many they are inserted into Database...

Dim cmdsqldata As ADODB.Command
Set cmdsqldata =New ADODB.Command
Dim cmdSQLdata1 As ADODB.Command
Set cmdSQLData1=New ADODB.Command
cn1.opem GetDBconnectionString_source
Set cmdsqldata1.Activeconnection=cn1

Iam uploading total two types of excel files using excel Vba.
The uploaded files are inserting to database.

If fileType="xyz" Then
QueryA="select count(Track_ID) as cnt ,max ( p_number)+1 as prn from METADATA_DB.TRACKER_VW where ID = 4"
cmdsqldata1.commandText=queryA
cmdsqlData1.commandType= adCmdText
cmdsqldata1.CommandTimeout=0
Debug.print queryA
Set rs1= cmdsqlData1.Execute()


If rs1("cnt")=0 Then
prn=1
else
Prn=rs1("prn")
End If
Else

If filetype="abc"....then also similar code here...

cn1.Close

This is for selecting files and executing insert statements into Database.

If fileType= "xyz" Then
queryA= "select count (version) as cnt,max(version)+1 as ver from SOURCE_DB.table_VW where submission_period=' "
Debug.print queryA
cmdsqldata.commandText=queryA
cmdsqldata.commandType=adCmdText
cmdsqldata.commandTimeout=0
set rs=cmdsqldata.execute()

Here there is for loop to insert all the fileds from the uploaded file...

For x=2 to rows. ex: Range("A" & x)

So x value goes...

queryB= insert into SOURCE_DB... blah blah blah,..,
cmdsqldata.commandtext = queryB
cmdsqldata.commandatype=adcmdatext
cmdsqldata.commandtimeout=0
Debug.Print queryB
Set rs= cmdsqldata.execute()
rows = rows+1
Next x
END IF


set rs= Nothing

-------------------------

public function GetDBConnectString_Source() As String
Const str_connect= "Data source= RST; Database=SOURCE_DB;Persist Security Info= True;Session Mode=ANSI;"
'My connection details to database here...login and password
End Function

My code is there as above now...i need now that no of records are inserted into database..i need records count...


Iam using ADO in my code not DAO....


I think,I will get my records count that are inserted into my SOURCE_DB from queryB
Or Recordset




If iam not wrong ,I can get no of records count from Recordset..am i correct..


If so,my code is here...

Dim recount as integer
Dim recordcount as integer


rscount = rs.RecordCount
MsgBox " No of records inserted is " & rscount

Here I need one messages for...if the no of rows being uploaded or read into excel ( rows-1) is not equal to no of records uploaded into Database...

If rows-1 <> rscount Then
 MsgBox " Not all rows were inserted ", vbOKOnly+ vbCritical, " Error"
End If

And I need the missing rows into a separate sheet( new sheet)...


Iam getting error 3704
Run time error 3704: operation is not allowed when the objet is closed.




Thanks...