Easiest Way to Find a Table

[Mike Charney]

What is the easiest and simplest way to find out if a table exists in an
Access Data Project? (SQL Svr 2000 Standard using MS-Access 2003 for
connection.)

Mike Charney
m charney at dunlap hospital dot org

[Lyle Fairfield]

"Mike Charney" <no*****@everyt hing.net> wrote in news:ueGag.7753 8$H71.48613
@newssvr13.news .prodigy.com:

What is the easiest and simplest way to find out if a table exists in an
Access Data Project? (SQL Svr 2000 Standard using MS-Access 2003 for
connection.)

Mike Charney
m charney at dunlap hospital dot org

Maybe:

Public Function DoesTableExist( ByVal TableName As String) As Boolean
DoesTableExist = Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, TableName)).BOF
End Function

But some mght want to use

sp_tables

or sp_tables_ex

--
Lyle Fairfield

[Mike Charney]

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:Xn******** *************** **********@216. 221.81.119...

"Mike Charney" <no*****@everyt hing.net> wrote in
news:ueGag.7753 8$H71.48613
@newssvr13.news .prodigy.com:

What is the easiest and simplest way to find out if a table exists in an
Access Data Project? (SQL Svr 2000 Standard using MS-Access 2003 for
connection.)

Mike Charney
m charney at dunlap hospital dot org

Maybe:

Public Function DoesTableExist( ByVal TableName As String) As Boolean
DoesTableExist = Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, TableName)).BOF
End Function

But some mght want to use

sp_tables

or sp_tables_ex

--
Lyle Fairfield

I am not real good with VB or VBA so I don't understand everything that is
going on in that statement. I do see that it is a separate module and I
really did not want to call or jump to another module and then back.

Is there a way to run an IF statement that checks for the table tblimport?

Mike

[Lyle Fairfield]

"Mike Charney" <no*****@everyt hing.net> wrote in
news:p0******** ***********@new ssvr29.news.pro digy.net:

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:Xn******** *************** **********@216. 221.81.119...

"Mike Charney" <no*****@everyt hing.net> wrote in
news:ueGag.7753 8$H71.48613
@newssvr13.news .prodigy.com:

What is the easiest and simplest way to find out if a table exists
in an Access Data Project? (SQL Svr 2000 Standard using MS-Access
2003 for connection.)

Mike Charney
m charney at dunlap hospital dot org
Maybe:

Public Function DoesTableExist( ByVal TableName As String) As Boolean
DoesTableExist = Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, TableName)).BOF
End Function

But some mght want to use

sp_tables

or sp_tables_ex

--
Lyle Fairfield

I am not real good with VB or VBA so I don't understand everything
that is going on in that statement. I do see that it is a separate
module and I really did not want to call or jump to another module and
then back.

It's in whatever module you want to put it in. It is a separate function,
but you could easily extract the operating parts as something like:

If Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, "tblimport")).B OF Then

(this is all one line!)
Is there a way to run an IF statement that checks for the table
tblimport?

as above ... but the notion behind modular programming is that you put

Public Function DoesTableExist( ByVal TableName As String) As Boolean
DoesTableExist = Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, TableName)).BOF
'(this is all one line!)
End Function

in any standard module, including the one where you want to use it, and
then you can use it anytime anywhere in your project as simply

If DoesTableExist( "tblimport" ) Then

--
Lyle Fairfield

[Mike Charney]

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:Xn******** *************** **********@216. 221.81.119...

"Mike Charney" <no*****@everyt hing.net> wrote in
news:p0******** ***********@new ssvr29.news.pro digy.net:

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:Xn******** *************** **********@216. 221.81.119...

"Mike Charney" <no*****@everyt hing.net> wrote in
news:ueGag.7753 8$H71.48613
@newssvr13.news .prodigy.com:

What is the easiest and simplest way to find out if a table exists
in an Access Data Project? (SQL Svr 2000 Standard using MS-Access
2003 for connection.)

Mike Charney
m charney at dunlap hospital dot org

Maybe:

Public Function DoesTableExist( ByVal TableName As String) As Boolean
DoesTableExist = Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, TableName)).BOF
End Function

But some mght want to use

sp_tables

or sp_tables_ex

--
Lyle Fairfield

I am not real good with VB or VBA so I don't understand everything
that is going on in that statement. I do see that it is a separate
module and I really did not want to call or jump to another module and
then back.

It's in whatever module you want to put it in. It is a separate function,
but you could easily extract the operating parts as something like:

If Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, "tblimport")).B OF Then

(this is all one line!)

Is there a way to run an IF statement that checks for the table
tblimport?

as above ... but the notion behind modular programming is that you put

Public Function DoesTableExist( ByVal TableName As String) As Boolean
DoesTableExist = Not CurrentProject. Connection.Open Schema
(adSchemaTables , Array(Empty, Empty, TableName)).BOF
'(this is all one line!)
End Function

in any standard module, including the one where you want to use it, and
then you can use it anytime anywhere in your project as simply

If DoesTableExist( "tblimport" ) Then

--
Lyle Fairfield

I understand now. I used the code that you listed and it worked great.

Thanks

Mike