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

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



%>
MRSONNYAsked:
Who is Participating?
 
stefanxConnect With a Mentor Commented:
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
 
jekl2000Commented:
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
 
fritz_the_blankCommented:
adOpenStatic does not support record count! Use adOpenStatic instead.

Fritz the Blank
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
John844Commented:
set the recordset cursor location to be client side and open a keyset recordset
0
 
fritz_the_blankCommented:
Whoops, typo. Please ignore my previous comment.

Fritz the Blank
0
 
John844Commented:
use
rs.open strSQL,Conn,3,1

0
 
mgfranzCommented:
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
 
fritz_the_blankCommented:
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
 
MRSONNYAuthor Commented:
*************

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
 
John844Commented:
you need to add
Set rs = Server.CreateObject("ADODB.Recordset")
0
 
mgfranzCommented:
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
 
fritz_the_blankCommented:
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
 
MRSONNYAuthor Commented:
fritz_the_blank  :

Still returning '-1'
0
 
aponcealbuerneCommented:
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
 
MRSONNYAuthor Commented:

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
 
MRSONNYAuthor Commented:
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
 
mgfranzCommented:
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
 
MRSONNYAuthor Commented:
mgfranz :

Still the same error :-(
0
 
Dave_GreeneCommented:
MRSONNY,

Simple fix

With rs
  .movefirst
  .movelast
  Records = .recordcount
end with
0
 
MRSONNYAuthor Commented:
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
 
Dave_GreeneCommented:
You have to span the full recordset before you can get an accurate value...
0
 
MRSONNYAuthor Commented:

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
 
mgfranzCommented:
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
 
Dave_GreeneCommented:
You need to change your Open and Execute parameters...  don't have them handy have to look them up for you...
0
 
Dave_GreeneCommented:
Ok, here we go

Change this

set rs = Conn.Execute(strSQL)

TO

RS.Open strSQL, Conn, adOpenStatic, adLockReadOnly
0
 
Dave_GreeneCommented:
I would also rename "Records" to something like RecCount so that you don't run into a keyword...
0
 
mgfranzCommented:
Repeat comment Dave.
0
 
mgfranzCommented:
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
 
Dave_GreeneCommented:
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
 
aponcealbuerneCommented:
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
 
aponcealbuerneCommented:
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
 
mgfranzCommented:
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
 
MRSONNYAuthor Commented:
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
 
Dave_GreeneCommented:
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
 
MRSONNYAuthor Commented:
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
 
mgfranzCommented:
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
 
mgfranzCommented:
I don't think Access allows the predicator COUNT(*) stefan, or does it?
0
 
mgfranzCommented:
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
 
stefanxCommented:
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
 
mgfranzCommented:
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
 
stefanxCommented:
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
 
mgfranzCommented:
LOL... probably... ;-)

[would that work?  Can you do nested statements in Access?]
0
 
stefanxCommented:
[Yep - Nested queries in Access are quite acceptable]

[...] = whisper ;)
0
 
puranik_pCommented:
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
 
mgfranzCommented:
puranik_p, repeat comment...
0
 
Dave_GreeneCommented:
You could sew a sweater with this thread  :)
0
 
mgfranzCommented:
And it started out at 10 points...

[this is really an easy thing to do sonny, rtfm]
0
 
stefanxCommented:
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
 
mgfranzCommented:
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
 
MRSONNYAuthor Commented:
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
 
stefanxCommented:
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
 
MRSONNYAuthor Commented:
......A last q, How many Users can I Have on the site using
 Access, befor I must use SQL server !!??
0
 
mgfranzCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.