Hi

How can I check for existence of a certain table by name in and access
database and if the table exists find the number of records in it?

I guess I can access this information by setting up a dataset for the
database but I am wondering if there is a quick way to find this.

Thanks

Regards

Issuing a count select statement within a try catch loop would handle both?

It would give you the total records if the table exists and error and enter
the catch code if it doesn't?


Rob

"John" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi
>
> How can I check for existence of a certain table by name in and access
> database and if the table exists find the number of records in it?
>
> I guess I can access this information by setting up a dataset for the
> database but I am wondering if there is a quick way to find this.
>
> Thanks
>
> Regards
>
>

"Rob Blackmore" <(E-Mail Removed)> wrote in
news:666859B3-B84E-4278-933F-(E-Mail Removed):

> Issuing a count select statement within a try catch loop would handle
> both?
>
> It would give you the total records if the table exists and error and
> enter the catch code if it doesn't?

A better way would be to use ADO.NET's GetSchema or query the metadata
table directly to check if a table exists.


--
(E-Mail Removed) (Do not e-mail)

John wrote:
> How can I check for existence of a certain table by name in and access
> database and if the table exists find the number of records in it?

Rob Blackmore's approach will certainly work, but if you prefer to check for
the existence of the table without getting an exception if it doesn't exist
(which is definitely my preferred approach), here's a way that will work:

\\\
Dim tableInfo As DataTable
tableInfo = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
String() {Nothing, Nothing, "TableName", "TABLE"})
///

The GetOleDbSchemaTable method will return a datatable containing schema
information. This set of parameters will return information about a table
called "TableName" if one exists. Check the tableInfo.Rows.Count after
executing this; if it contains zero then the table doesn't exist, if it
contains one then it does exist.

Then if it exists, execute the SELECT COUNT statement as mentioned in Rob's
email.

HTH,

--

(O)enone

Hi John,

I never understand this kind of questions. You are making the program and
you don't know the name of the table which you (or a cowerker) would have
given a name.

How can this happen?

Cor

"John" <(E-Mail Removed)> schreef in bericht
news:%(E-Mail Removed)...
> Hi
>
> How can I check for existence of a certain table by name in and access
> database and if the table exists find the number of records in it?
>
> I guess I can access this information by setting up a dataset for the
> database but I am wondering if there is a quick way to find this.
>
> Thanks
>
> Regards
>
>

Cor Ligthert[MVP] wrote:
> I never understand this kind of questions. You are making the program
> and you don't know the name of the table which you (or a cowerker)
> would have given a name. How can this happen?

There are lots of systems that don't have fixed, known database structures.

The system I work on has dozens of different databases working for different
clients. We then offer a range of plug-ins to perform various tasks, each of
which will create tables for its own storage needs. The set of tables
therefore is not fixed, and it can be necessary for one part of the system
to check a table's presence because another part of the system may or may
not be present.

--

(O)enone

Hi Cor

I know the name of the table but it may or may not exist and I need to check
for the existence of the table.

Regards


"Cor Ligthert[MVP]" <(E-Mail Removed)> wrote in message
news:1C1F1FF0-5F25-408D-AF97-(E-Mail Removed)...
> Hi John,
>
> I never understand this kind of questions. You are making the program and
> you don't know the name of the table which you (or a cowerker) would have
> given a name.
>
> How can this happen?
>
> Cor
>
> "John" <(E-Mail Removed)> schreef in bericht
> news:%(E-Mail Removed)...
>> Hi
>>
>> How can I check for existence of a certain table by name in and access
>> database and if the table exists find the number of records in it?
>>
>> I guess I can access this information by setting up a dataset for the
>> database but I am wondering if there is a quick way to find this.
>>
>> Thanks
>>
>> Regards
>>
>>
>

John,

Then I would just create a simple SQL command with

"Select * from MyTable"

Execute that with an executescalar and when I get an error then I know that
the table does not exist.

Cor

On Jan 20, 7:53*pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
> John,
>
> Then I would just create a simple SQL command with
>
> "Select * from MyTable"
>
> Execute that with an executescalar and when I get an error then I know that
> the table does not exist.
>
> Cor

Cor's tip is the most basic and comprehensive one. When you call
something from a non-existed database, an error will occur and you
won't be able to access then you can use try-catch or kinda things to
display a custom error message.

No, that's an improper and silly way to check for a table existence.
What exception will you get? Will it be due to the lack of table existance
or due to some other error?
And don't forget that exceptions are for exceptional situations, not for
known situations: in your case, table might or might not exist. And if it
doesn't then this isn't an exception.
The proper way is to use GetSchema method - as (O)enone suggested and if
available or execute a DDL statement - almost each database will let you
query for a table using some sort of query statement.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


"kimiraikkonen" <(E-Mail Removed)> wrote in message
news:3a1374ba-a2c7-475c-8cd9-(E-Mail Removed)...
On Jan 20, 7:53 pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
> John,
>
> Then I would just create a simple SQL command with
>
> "Select * from MyTable"
>
> Execute that with an executescalar and when I get an error then I know
> that
> the table does not exist.
>
> Cor

Cor's tip is the most basic and comprehensive one. When you call
something from a non-existed database, an error will occur and you
won't be able to access then you can use try-catch or kinda things to
display a custom error message.