Check if a table is empty (MSAccess Database + Delphi)

Question

I need to findout whether a created table has entries in it or not.

What I need is,

if (TableIsEmpty) then
     do_something
else
     do_something_else;

What I've written for the purpose is :

Function IsTableEmpty:Boolean;
Var
  DataSource : string;
Begin
  DataSource :=
     'Provider=Microsoft.Jet.OLEDB.4.0'+
     ';Data Source=c:\mydb.mdb'+
     ';Persist Security Info=False';

  Form2.ADOConnection1.ConnectionString := DataSource;
  Form2.ADOConnection1.LoginPrompt := False;
  Form2.ADOCommand1.Connection := Form2.ADOConnection1;
  Form2.ADOTable1.ConnectionString := DataSource;
  Form2.ADOTable1.Connection := Form2.ADOConnection1;
  if (Form2.ADOTable1.IsEmpty)then
      result := true
  else
      result := false;
End;

But this function returns true irrespective of status of the table!

EDIT*** Modified Code :

Function IsTableEmpty:Boolean;
Var
  DataSource, cs : string;
Begin
  DataSource :=
     'Provider=Microsoft.Jet.OLEDB.4.0'+
     ';Data Source=c:\Users.mdb'+
     ';Persist Security Info=False';

  Form2.ADOConnection1.ConnectionString := DataSource;
  Form2.ADOConnection1.LoginPrompt := False;
  Form2.ADOCommand1.Connection := Form2.ADOConnection1;
  Form2.ADOTable1.Connection := Form2.ADOConnection1;
  Form2.ADOTable1.TableName := 'userIdentification';
  Form2.ADOTable1.Active := True;
  cs := 'Select * from userIdentification';
  Form2.ADOCommand1.CommandText := cs;
  Form2.ADOCommand1.Execute;
  if Form2.ADOTable1.RecordCount <= 0 then
     result := true
  else
     result := false;
  Form2.ADOConnection1.Close;
End;

This function always returns false!!

Can you issue a SQL select on the table "select count(1) from tablename" and see how many records it returns? — Keith Miller, Oct 14 '13 at 06:24
You missed to set your Adotable to active, so you will always get true. BTW using ADOTable1.ConnectionString is not necessary if you use ADOTable1.Connection — bummi, Oct 14 '13 at 06:31
I need to check if an account exists in the db, so I need to use some extra space if I use select count(1) from tablename ? isn't it ? — Priyabrata, Oct 14 '13 at 06:36
You should use SELECT TOP 1 * if you are not going to actually use what you read. This will prevent you from actually transferring all the data from the table (which may contain millions of records). If you know of the primary key field name in the table, use that one istead of "*" to make it even faster... — HeartWare, Oct 14 '13 at 07:09

score 5 · Accepted Answer · answered Oct 14 '13 at 06:45

5

if Form2.ADOTable1.RecordCount =< 0 then
     do_something
else
     do_something_else;

Run this after a successfully executed select statement

answered Oct 14 '13 at 06:45

Hidden

3,598
4
34
57

I've added the modified code to the main post. It always returns false. – Priyabrata Oct 14 '13 at 06:53
Which value does the RecordCount have when you are in debug modus? – Hidden Oct 14 '13 at 07:00
@Priyabrata could you tell us why your function did not work, but the answer did? – bummi Oct 14 '13 at 07:15
I was running the query wrong.@bummi and some how the isEmpty isn't getting affected, after the query(reason I donot know). – Priyabrata Oct 14 '13 at 07:28
Since you stated `This function always returns false!!` in your edit, I still can't see what's the difference between the answer und your *Modified Code*. – bummi Oct 14 '13 at 10:50

Check if a table is empty (MSAccess Database + Delphi)

1 Answers1