Solved

Record count from a Access database !!! (NewBee)

Posted on 2001-07-17
53
403 Views
Last Modified: 2008-02-01
Hi !

Pleaz give my a hand here, it's only return '-1' !
I want it to return the actual numbers of records !

.....must it be more complicated than this :
<%


Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, adOpenStatic

strSQL = "Select * From personer"
set rs = Conn.Execute(strSQL)

Records=rs.RecordCount

Conn.Close
Set Conn = Nothing



%>
0
Comment
Question by:MRSONNY
  • 16
  • 11
  • 8
  • +6
53 Comments
 
LVL 11

Expert Comment

by:jekl2000
ID: 6291252
Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, 1,1

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6291254
adOpenStatic does not support record count! Use adOpenStatic instead.

Fritz the Blank
0
 
LVL 7

Expert Comment

by:John844
ID: 6291260
set the recordset cursor location to be client side and open a keyset recordset
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6291261
Whoops, typo. Please ignore my previous comment.

Fritz the Blank
0
 
LVL 7

Expert Comment

by:John844
ID: 6291265
use
rs.open strSQL,Conn,3,1

0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291270
The -1 is a Null or Invalid response, try this;

<%
     Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, adOpenKeyset, adLockOptimistic, adCmdText
     
     'Use the RecordCount property of the Recordset object to get count.
     If rs.RecordCount >= 0 then
       Response.Write "We've have the " & rs.RecordCount & " records counted<BR>"    
     
       Else              
       Response.Write "No records found."          
     End If
       
   rs.Close
%>
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6291285
Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, adOpenStatic

rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = adOpenStatic
rs.open "Select * From personer" , Conn
Records=rs.RecordCount
0
 

Author Comment

by:MRSONNY
ID: 6291320
*************

mgfranz

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'
/skryte/skrutemain.asp, line 8


****************


fritz_the_blank  = error

Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'rs.CursorType'
/skryte/skrutemain.asp, line 8


*************
0
 
LVL 7

Expert Comment

by:John844
ID: 6291329
you need to add
Set rs = Server.CreateObject("ADODB.Recordset")
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291354
Thanks John.

Sonny, use this;

<%
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Tablename", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:/passord/database.mdb'", adOpenKeyset, adLockOptimistic, adCmdText
   
    'Use the RecordCount property of the Recordset object to get count.
    If rs.RecordCount >= 0 then
      Response.Write "We've have the " & rs.RecordCount & " records counted<BR>"    
   
      Else              
      Response.Write "No records found."          
    End If
       
  rs.Close
%>
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6291358
John caught a mistake in my code:

rs = Server.CreateObject("ADODB.Recordset")

should read

Set rs = Server.CreateObject("ADODB.Recordset")


Sorry for all of the mistakes--I am trying to do too many things at once.

0
 

Author Comment

by:MRSONNY
ID: 6291405
fritz_the_blank  :

Still returning '-1'
0
 
LVL 4

Expert Comment

by:aponcealbuerne
ID: 6291411
why don't you just do this:

part of your code

strSQL = "Select * From personer"
set rs = Conn.Execute(strSQL)

'instead of this !!!
'Records=rs.RecordCount

count = 0
do until rs.eof
  count = count + 1
loop
rs.movefirst 'just to return to the begining

Conn.Close
Set Conn = Nothing


0
 

Author Comment

by:MRSONNY
ID: 6291413

mgfranz :


Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/skryte/skrutemain.asp, line 4

4 =
rs.Open "SELECT * FROM Tablename", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:/passord/database.mdb'",
adOpenKeyset, adLockOptimistic, adCmdText
0
 

Author Comment

by:MRSONNY
ID: 6291417
aponcealbuerne :

Yes, I could do that, but I started out thinking that
it would be easyer to use a recordcount instead, and after all, woulde'nt the 'do' 'until' loop make unwanted
prosessor time on the server (thinking enterprise !!)
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291438
rs.Open "SELECT * FROM Tablename", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:/passord/database.mdb'",

Sonny, replace the "tablename" with your table, and this assumes you are using MDAC 2.5 and have included the ADOVBS.inc file.
0
 

Author Comment

by:MRSONNY
ID: 6291457
mgfranz :

Still the same error :-(
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291465
MRSONNY,

Simple fix

With rs
  .movefirst
  .movelast
  Records = .recordcount
end with
0
 

Author Comment

by:MRSONNY
ID: 6291466
OK !

I hope I've included  all the information in my
first post, pleaz give me a good code whois working....


.....points up too 50
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291469
You have to span the full recordset before you can get an accurate value...
0
 

Author Comment

by:MRSONNY
ID: 6291481

Dave_Greene

<%

Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, adOpenStatic
 
strSQL = "Select * From personer"
 set rs = Conn.Execute(strSQL)

With rs
 .movefirst
 .movelast
 Records = .recordcount
end with

Conn.Close
Set Conn = Nothing

%>


gives error :

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E24)
Rowset does not support fetching backward.
/skryte/skrutemain.asp, line 13
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291489
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

More examples...
------------------------------
Sub CountRecords()
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open table-type Recordset object.
    Set rst = dbs.OpenRecordset("Orders")
        Response.Write rst.RecordCount
    rst.Close
    Set dbs = Nothing
End Sub

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

Set rstPublishers = New ADODB.Recordset
   SQLPublishers = "publishers"
   rstPublishers.Open SQLPublishers, strCnxn, adOpenStatic, , adCmdTable

   intPublisherCount = rstPublishers.RecordCount

   ' get user input
   strCountry = Trim(InputBox("Enter a country to filter on (e.g. USA):"))

   If strCountry <> "" Then
      ' open a filtered Recordset object
      rstPublishers.Filter = "Country ='" & strCountry & "'"

      If rstPublishers.RecordCount = 0 Then
         MsgBox "No publishers from that country."
      Else
         ' print number of records for the original recordset
         ' and the filtered recordset
         strMessage = "Orders in original recordset: " & _
            vbCr & intPublisherCount & vbCr & _
            "Orders in filtered recordset (Country = '" & _
            strCountry & "'): " & vbCr & _
            rstPublishers.RecordCount
         MsgBox strMessage
      End If

   End If

    ' clean up
   rstPublishers.Close
   Cnxn.Close
   Set rstPublishers = Nothing
   Set Cnxn = Nothing
------------------------------
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291495
You need to change your Open and Execute parameters...  don't have them handy have to look them up for you...
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291502
Ok, here we go

Change this

set rs = Conn.Execute(strSQL)

TO

RS.Open strSQL, Conn, adOpenStatic, adLockReadOnly
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291504
I would also rename "Records" to something like RecCount so that you don't run into a keyword...
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291507
Repeat comment Dave.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 18

Expert Comment

by:mgfranz
ID: 6291509
Sonny... your'e driving us crazy here!

This is EASY!  You have a number of working examples, if none of them are working, maybe you should look into why?
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291526
Yes, I posted around the same time as you mgfranz...  I didn't read your post, looks ok, a little hard for a rookie to decypher but both answers are relatively the same, but the big difference when working with access is traversing to the first record then the last to get an accurate count.
0
 
LVL 4

Expert Comment

by:aponcealbuerne
ID: 6291550
I think you should consier my comment
From MSDN

Remarks

Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records
are contained in a dynaset-, snapshot-, or forward-only?type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate
number of records your query will return.
0
 
LVL 4

Expert Comment

by:aponcealbuerne
ID: 6291552
Note
Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291556
I know Dave, but you know what's just around the corner...
"I have a thousand records returned, how can I page them...?"

At least we haven't even got into PageCount() and AbsolutePosition() yet... ;-)


Sonny, you need to read this page; http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjodbrec.asp?frame=true
0
 

Author Comment

by:MRSONNY
ID: 6291569
Ok !

I'll do that, and I'll return her and give the points, if yhat URL help me :-)

...and by the way, thank you for your time (all of you) !!!
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6291570
mgfranz, I hear ya...  It's all yours when it gets to that point... just kidding, I'd try to save ya!

aponcealbuerne's comment is correct also.  I agree, not recommended but...  if you've gotta do it... then we have shown him the way.
0
 

Author Comment

by:MRSONNY
ID: 6291581
Until I got the right script I'll use this :

<%

Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, adLockOptimistic
 
strSQL = "Select * From personer"
 set rs = Conn.Execute(strSQL)

With rs
  .movefirst

 while .eof = FALSE
 .movenext
 siss = siss +1
 wend
 
 Records = siss
end with

Conn.Close
Set Conn = Nothing

%>


....I hope this work !!
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291608
Sonny, I just tested this, it works;

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout =  10
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\passord\database.mdb"
Set rs = Server.CreateObject("ADODB.Recordset")
%>
<%
SQL = "SELECT * FROM Test99 "
     rs.Open SQL, conn, 3, 3
     
     If rs.RecordCount >= 0 then
     Response.Write "We've have the " & rs.RecordCount & " records counted<BR>"    
   
     Else              
     Response.Write "No records found."          
   End If
     
 rs.Close
%>
0
 
LVL 8

Accepted Solution

by:
stefanx earned 60 total points
ID: 6291725
Sheesh - what a discussion so far. Funny that no-one suggested :

...
...
SQL = "SELECT COUNT(*) FROM Personer"
Set R = Conn.Execute(SQL)
RCount = R.Fields(0)
R.Close
...
...

;)
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291756
I don't think Access allows the predicator COUNT(*) stefan, or does it?
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291760
By jove, it does work!

SQL = "SELECT COUNT(*) FROM Test99"
Set R = Conn.Execute(SQL)
RCount = R.Fields(0)
Response.Write RCount
R.Close

Nice one stefan!
0
 
LVL 8

Expert Comment

by:stefanx
ID: 6291901
Access allows almost all of the standard SQL predicators mgranz i.e AVG(),MAX(),MIN() and even GROUP BY and OUTER JOINS. While it's still not the greatest performer and it deviates from ANSI SQL-92 syntax here and there, it is useful when writing code for later migration to a serious DB ;)
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291917
I'm working with a PostGreSQL db right now... it's amazing how different, yet similar the predicators can react, try making this in an Access Query... ;-)

<% SQL = "SELECT Customer.CustomerID AS CustID, Firstname, Lastname, Username, Password, Question, Answer, Email, Age, Gender, Address, City, State, Zip FROM Customer WHERE NOT EXISTS (SELECT  Registration.CustomerID WHERE (Registration.CustomerID = Customer.CustomerID))"
%>
0
 
LVL 8

Expert Comment

by:stefanx
ID: 6291948
In Access, I'd guess :

SQL = "SELECT Customer.CustomerID AS CustID, Firstname, Lastname, Username, Password, Question, Answer,
Email, Age, Gender, Address, City, State, Zip FROM Customer WHERE Customer.CustomerID NOT IN (SELECT  DISTINCT Registration.CustomerID FROM Registration)

I hope we are not confusing the hell out of MTSONNY here ;)
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6291964
LOL... probably... ;-)

[would that work?  Can you do nested statements in Access?]
0
 
LVL 8

Expert Comment

by:stefanx
ID: 6291968
[Yep - Nested queries in Access are quite acceptable]

[...] = whisper ;)
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 6292233
you often get this problem with 97 database.
try this...

<%


Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN, adOpenStatic

strSQL = "Select * From personer"
set rs = Conn.Execute(strSQL)
If Not rs.EOF Then
    rs.moveLast
    rs.MoveNext
    Records = rs.RecordCount
Else
    Records = 0
End If

Conn.Close
Set Conn = Nothing



%>
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6293986
puranik_p, repeat comment...
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6294007
You could sew a sweater with this thread  :)
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6294053
And it started out at 10 points...

[this is really an easy thing to do sonny, rtfm]
0
 
LVL 8

Expert Comment

by:stefanx
ID: 6294164
OK, so how about a final solution for MRSONNY then? :

<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=c:/passord/database.mdb"
Conn.Open DSN
strSQL = "SELECT COUNT(*) FROM personer"
Set RS = Conn.Execute(strSQL)
Records = RS.fields(0)
RS.close
set RS = Nothing
Conn.Close
Set Conn = Nothing
Response.Write "Finally!. There are " & Records & " records! - we hope ;)"
%>
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6294210
No, mime!

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\passord\database.mdb"
Set rs = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM Test99 "
rs.Open SQL, conn, 3, 3
If rs.RecordCount >= 0 then
Response.Write "We have " & rs.RecordCount & " records counted<BR>"    
Else              
Response.Write "No records found."          
End If
rs.Close
%>
0
 

Author Comment

by:MRSONNY
ID: 6295144
WoooW !

You're great stefanx, that opned my mind for programming
against access :oD

Best Regardes


P.S
I hope everyone agree to that stefanx had the
best solution !!!
(..Or else I guess I'll hear from you! )
0
 
LVL 8

Expert Comment

by:stefanx
ID: 6295311
Why thankyou MRSONNY. I really only joined the thread very late and mgfranz's solution is just as good. But I'm glad that the discussion has helped you to see that MS Access is not that bad after all. Sure, it's not the fastest and I wouldn't bet on running a several million record database on it, but it is very rich in features and largely adheres to standard SQL (if any database vendor can decide what that precisely is ;). At the same time it is very easy to use and has a nice interface to get everything going. And isn't simplicity and ease of use one of the primary aims of any software product? I think it's great that you don't need 10 degrees and 50 years of UNIX and C experience to work with it :P

At the end of the day, if one resists the temptation to use Access's specific deviations to SQL, (and the WAY cool VBA embedding in SQL Statements!), it makes for a good platform to upscale to SQL Server, PostgreSQL, Oracle and even DB2 when the need arises. I would thouroughly recommend it for people learning the basics of databases. Conceptually, a relational database is a relational database is a relational database. The others are just higher in performance, capacity and security but typically minus the nice Access interface. (Keep in mind that you can actually use MS Access's interface when working with other databases as well! - for example, you can work on SQL Server and even MySQL directly from MS Access through table linking).

But enough philosophical rambling - thanks for the points ;)
0
 

Author Comment

by:MRSONNY
ID: 6295369
......A last q, How many Users can I Have on the site using
 Access, befor I must use SQL server !!??
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6295508
It's not so much the number of users as it is the number of records, I have seen 100k recordset Access dB run fine, although permormance can deteriorate.  Proper coding, use of Application and caching will help...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
post data with a link 8 49
Classic ASP - cannot get to correct path 17 62
syntax error on ASP ? 6 40
Pass through dll 2 38
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now