checking a table for records

I am working in VB 5.0 using some access tables through the data control.  I need to check to see if there are records in a table from a command button on a form.  The data control for the table I want to check is on a different form.  Can anyone help me with this?
jakemailAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SiM99Connect With a Mentor Commented:
Use this function (or use some of it or modify ot etc. :)


Private Function IsRecords() as Integer
dim db As Database
dim rst As Recordset

Set db = OpenDatabase("c:\Database.mdb")
Set rst = db.OpenRecordset("SELECT * FROM TableName")

On Error GoTo No_Records
    rst.MoveFirst
    'There's some records, if there aren't then the program will skip this part (give an error) and go to No_Records below
    rst.MoveLast
    IsRecords = rst.RecordCount
   
    Exit Sub 'Do Not Process the no_records code
   
No_Records:
    IsRecords = 0
'There are no records
End Sub
0
 
mark2150Commented:
Easy way is to just SQL it.

Public db               As Database     'Generic Database
Public rs               As Recordset    'Generic Recordset
Public sql              As String       'Generic SQL

Set db = DBEngine.Workspaces(0).OpenDatabase("MyData.Mdb")
Set rs = db.OpenRecordset(sql)

Do while not RS.EOF
   .... do what you need to a record
   RS.Movenext
Loop

This avoids the whole data control mess...

M
0
 
mark2150Commented:
Oops! Left out the SQL!

SQL = "SELECT * FROM MyTable WHERE SomeCondition=..."

M
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mark2150Commented:
RS.Edit
RS!Field = New Value
RS.Update

M
0
 
jakemailAuthor Commented:
mark2150,

I'm trying to follow your code but am not sure if this is checking for records in the table.  If it is can you walk me through this.  I'm not following it.  What I'm trying to do is tell if there are records in the spefic table.  If there is I will do a invoke a set of code.  If there aren't any records I will invoke a different set of code.  Thanks for your help.
0
 
mark2150Commented:
open the record set as we both have shown. Then do an

IF RS.EOF THEN
  ... no records
ELSE
  ... at least one record
END IF

M
0
 
SiM99Commented:
yes, mark2150, I stand corrected... I only just found out that you can use rst.EOF today (didnt know about it before) as I myself am making a database front end. I used to think it was only for use when accessing files..

I still think that code worked pretty good though, for not knowing about the EOF property :)

0
 
mark2150Commented:
Hey if it works it must be right. But EOF will work faster in most cases without overhead of arming error trap

M
0
 
SiM99Commented:
I used EOF, and still had to do some error trapping.... hmm, ill have another look at what i've done :|
0
All Courses

From novice to tech pro — start learning today.