if table exist

[adil]

Hi ,
does any knows how can i check the existence of a table in a ms access
database ?i need a sql statement that provide this function.
Thanks .

[DFS]

Re: if table exist

adil wrote:[color=blue]
> Hi ,
> does any knows how can i check the existence of a table in a ms access
> database ?i need a sql statement that provide this function.
> Thanks .[/color]

Create a new module. Add

Public Function ifTableExists(t blName as String) as Boolean

ifTableExists = False
If DCount("[Name]","MSysObjects" ,"[Name] = '" & tblName & "'") = 1 then
ifTableExists = True
Endif

End Function


It might be pretty slow in a query, depending on the # of rows.

[Lyle Fairfield]

Re: if table exist

"DFS" <nospam@dfs_.co m> wrote in news:M8ZWf.593$ 68.204
@bignews7.bells outh.net:
[color=blue]
> adil wrote:[color=green]
>> Hi ,
>> does any knows how can i check the existence of a table in a ms access
>> database ?i need a sql statement that provide this function.
>> Thanks .[/color]
>
> Create a new module. Add
>
> Public Function ifTableExists(t blName as String) as Boolean
>
> ifTableExists = False
> If DCount("[Name]","MSysObjects" ,"[Name] = '" & tblName & "'") = 1 then
> ifTableExists = True
> Endif
>
> End Function
>
>
> It might be pretty slow in a query, depending on the # of rows.[/color]

What if there's another non-table object named tblName?

--
Lyle Fairfield

[DFS]

Re: if table exist

Lyle Fairfield wrote:[color=blue]
> "DFS" <nospam@dfs_.co m> wrote in news:M8ZWf.593$ 68.204
> @bignews7.bells outh.net:
>[color=green]
>> adil wrote:[color=darkred]
>>> Hi ,
>>> does any knows how can i check the existence of a table in a ms
>>> access database ?i need a sql statement that provide this function.
>>> Thanks .[/color]
>>
>> Create a new module. Add
>>
>> Public Function ifTableExists(t blName as String) as Boolean
>>
>> ifTableExists = False
>> If DCount("[Name]","MSysObjects" ,"[Name] = '" & tblName & "'") = 1
>> then ifTableExists = True
>> Endif
>>
>> End Function
>>
>>
>> It might be pretty slow in a query, depending on the # of rows.[/color]
>
> What if there's another non-table object named tblName?[/color]


Good catch. Newbies do in fact name both their forms and tables 'Contacts'
or 'Recipes', etc.

So, let's make it a bit more spiffy, shall we?


Public Function ifTableExists(t blName as String, tblType as String) as
String

if tblType <> "local" and tblType <> "attached" and tblType <> "ODBC" then
ifTableExists = "Verify table type"
endif

ifTableExists = "not found"

dim objType as double
if tblType = "local" then
objType = -1
elseif tblType = "attached" then
objType = 6
elseif tblType = "ODBC" then
objType = 4
endif

If DCount("[Name]","MSysObjects" ,"[Name] = '" & tblName & "' and [Type] =
" & objType & "") = 1 then
ifTableExists = "found " & tblType & " table"
Endif


End Function


Now it's twice as much work on the user, but somewhat more robust.

Whaddaya think? There are probably more gotchas to be found, but this will
cover the most common table types.

[Lyle Fairfield]

Re: if table exist

"DFS" <nospam@dfs_.co m> wrote in
news:4p_Wf.604$ 68.49@bignews7. bellsouth.net:
[color=blue]
> Good catch. Newbies do in fact name both their forms and tables
> 'Contacts' or 'Recipes', etc.[/color]

Actually Microsoft will do this. And if it does it then it must be right!
Right?
Wasn't this strongly implied in the group recently?

--
Lyle Fairfield

[DFS]

Re: if table exist

Lyle Fairfield wrote:[color=blue]
> "DFS" <nospam@dfs_.co m> wrote in
> news:4p_Wf.604$ 68.49@bignews7. bellsouth.net:
>[color=green]
>> Good catch. Newbies do in fact name both their forms and tables
>> 'Contacts' or 'Recipes', etc.[/color]
>
> Actually Microsoft will do this. And if it does it then it must be
> right! Right?[/color]

Do they? Where?

It's not necessarily right if MS does it, but [at least in this case and in
the [field name = control name scenario] it won't hurt anything either.


[color=blue]
> Wasn't this strongly implied in the group recently?[/color]

Enough with the petty retribution ;-)

[salad]

Re: if table exist

adil wrote:[color=blue]
> Hi ,
> does any knows how can i check the existence of a table in a ms access
> database ?i need a sql statement that provide this function.
> Thanks .
>
>[/color]
In a code module you can a function like the following

Function TDE(strTable As String) As Boolean
Dim tdf As TableDef
On Error GoTo 0
On Error Resume Next
Set tdf = CurrentDb.Table Defs(strTable)
TDE = (Err.Number = 0)
End Function

You can call this function, in a query, like this
TableExists : TDE("Junk")

If you are calling this from an external app, you'll need to read your
documentation.

[Lyle Fairfield]

Re: if table exist

"adil" <adil_abdelali@ hotmail.com> wrote in news:492vtpFmhl 2dU1
@news.dfncis.de :
[color=blue]
> Hi ,
> does any knows how can i check the existence of a table in a ms access
> database ?i need a sql statement that provide this function.[/color]

No, you don't.

--
Lyle Fairfield

[David W. Fenton]

Re: if table exist

salad <oil@vinegar.co m> wrote in
news:a3%Wf.6643 $HW2.4999@newsr ead3.news.pas.e arthlink.net:
[color=blue]
> Function TDE(strTable As String) As Boolean
> Dim tdf As TableDef
> On Error GoTo 0
> On Error Resume Next
> Set tdf = CurrentDb.Table Defs(strTable)
> TDE = (Err.Number = 0)
> End Function[/color]

I would only ever implement this error-raising approach with an
actual error handler that traps for the specific error raised when a
tabledef is not found. Give that you're actually testing here for a
specific error, it seems to me that you oughtn't just return FALSE
on any error, no matter what it is.

I'd also be sure to close the tdf and set it to Nothing before my
code exits.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[salad]

Re: if table exist

David W. Fenton wrote:
[color=blue]
> salad <oil@vinegar.co m> wrote in
> news:a3%Wf.6643 $HW2.4999@newsr ead3.news.pas.e arthlink.net:
>
>[color=green]
>>Function TDE(strTable As String) As Boolean
>> Dim tdf As TableDef
>> On Error GoTo 0
>> On Error Resume Next
>> Set tdf = CurrentDb.Table Defs(strTable)
>> TDE = (Err.Number = 0)
>>End Function[/color]
>
>
> I would only ever implement this error-raising approach with an
> actual error handler that traps for the specific error raised when a
> tabledef is not found. Give that you're actually testing here for a
> specific error, it seems to me that you oughtn't just return FALSE
> on any error, no matter what it is.
>
> I'd also be sure to close the tdf and set it to Nothing before my
> code exits.
>[/color]
Ok. Err 3265.
TDE = (Not Err.Number = 3265)

[David W. Fenton]

Re: if table exist

salad <oil@vinegar.co m> wrote in
news:Xt1Xf.9638 $x94.487@newsre ad1.news.pas.ea rthlink.net:
[color=blue]
> David W. Fenton wrote:
>[color=green]
>> salad <oil@vinegar.co m> wrote in
>> news:a3%Wf.6643 $HW2.4999@newsr ead3.news.pas.e arthlink.net:
>>
>>[color=darkred]
>>>Function TDE(strTable As String) As Boolean
>>> Dim tdf As TableDef
>>> On Error GoTo 0
>>> On Error Resume Next
>>> Set tdf = CurrentDb.Table Defs(strTable)
>>> TDE = (Err.Number = 0)
>>>End Function[/color]
>>
>>
>> I would only ever implement this error-raising approach with an
>> actual error handler that traps for the specific error raised
>> when a tabledef is not found. Give that you're actually testing
>> here for a specific error, it seems to me that you oughtn't just
>> return FALSE on any error, no matter what it is.
>>
>> I'd also be sure to close the tdf and set it to Nothing before my
>> code exits.
>>[/color]
> Ok. Err 3265.
> TDE = (Not Err.Number = 3265)
>[/color]

No, that's not a satisfactory way of doing it, as it will still eat
any other error that happens.

I am just fundamentally opposed to On Error Resume Next under these
circumstances. I think it's a terrible coding practice, especially
if you don't have an On Error GoTo 0 after it (you can't depend on
scope being honored when you user Resume Next).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[adil]

Re: if table exist

Thanks ,
it does! you have to check the system tabel of the database "MSysObject s".
in java you can do it like that:

public boolean tabelExist (String table){

Statement s = null;
ResultSet rs= null;
Connection con=DatabaseCon nection.makeCon ();
s=DatabaseConne ction.makeStat( con);
String sql="Select Name from MSysObjects Where Name ='"+table+"'" ;
try {

rs=s.executeQue ry(sql);
if(rs.next())re turn true;



} catch (SQLException e) {

e.printStackTra ce();
}
return false;

}

[Lyle Fairfield]

Re: if table exist

"adil" <adil_abdelali@ hotmail.com> wrote in
news:497vrqFn5i p5U1@news.dfnci s.de:
[color=blue]
> Thanks ,
> it does! you have to check the system tabel of the database
> "MSysObject s". in java you can do it like that:
>
> public boolean tabelExist (String table){
>
> Statement s = null;
> ResultSet rs= null;
> Connection con=DatabaseCon nection.makeCon ();
> s=DatabaseConne ction.makeStat( con);
> String sql="Select Name from MSysObjects Where Name ='"+table+"'" ;
> try {
>
> rs=s.executeQue ry(sql);
> if(rs.next())re turn true;
>
>
>
> } catch (SQLException e) {
>
> e.printStackTra ce();
> }
> return false;
>
> }[/color]

yeah yeah sure sure whatever

--
Lyle Fairfield

[Randy Harris]

Re: if table exist

David W. Fenton wrote:[color=blue]
> salad <oil@vinegar.co m> wrote in
> news:Xt1Xf.9638 $x94.487@newsre ad1.news.pas.ea rthlink.net:
>[color=green]
>> David W. Fenton wrote:
>>[color=darkred]
>>> salad <oil@vinegar.co m> wrote in
>>> news:a3%Wf.6643 $HW2.4999@newsr ead3.news.pas.e arthlink.net:
>>>
>>>
>>>> Function TDE(strTable As String) As Boolean
>>>> Dim tdf As TableDef
>>>> On Error GoTo 0
>>>> On Error Resume Next
>>>> Set tdf = CurrentDb.Table Defs(strTable)
>>>> TDE = (Err.Number = 0)
>>>> End Function
>>>
>>> I would only ever implement this error-raising approach with an
>>> actual error handler that traps for the specific error raised
>>> when a tabledef is not found. Give that you're actually testing
>>> here for a specific error, it seems to me that you oughtn't just
>>> return FALSE on any error, no matter what it is.
>>>
>>> I'd also be sure to close the tdf and set it to Nothing before my
>>> code exits.
>>>[/color]
>> Ok. Err 3265.
>> TDE = (Not Err.Number = 3265)
>>[/color]
>
> No, that's not a satisfactory way of doing it, as it will still eat
> any other error that happens.
>
> I am just fundamentally opposed to On Error Resume Next under these
> circumstances. I think it's a terrible coding practice, especially
> if you don't have an On Error GoTo 0 after it (you can't depend on
> scope being honored when you user Resume Next).
>[/color]

IMO - there is still room for debate on the merit of using On Error
Resume Next, but I will totally agree with David about not letting it
get out of scope. If it is used, never, ever, let a procedure exit
without On Error GoTo 0. (Lesson learned long ago, the hard way)

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.