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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
mark2150Commented:
RS.Edit
RS!Field = New Value
RS.Update

M
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
SiM99Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.