PDA

View Full Version : Records Count



sairam123
11-21-2013, 02:29 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 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
11-22-2013, 05:25 AM
Are yoiu just missing an opening quote before Select when you set the variable QueryA?

sairam123
11-22-2013, 06:38 AM
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
11-22-2013, 07:03 AM
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
11-22-2013, 08:32 AM
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
11-22-2013, 09:19 AM
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
11-22-2013, 09:52 AM
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....

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
[FONT=Verdana] '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
11-22-2013, 09:56 AM
why you are using
a = RecordSet.GetRows
MsgBox LBound Filter,(a), , UBound(a)
MsgBox a(0), , a(1)
[COLOR=#006400]Write the field names,[FONT=Verdana]CopyFromRecordset RecordSet

please answer the above...

sairam123
11-22-2013, 11:13 AM
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
11-23-2013, 04:39 AM
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
11-23-2013, 10:09 AM
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
11-24-2013, 08:15 AM
[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
11-25-2013, 02:42 PM
[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...