By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Easiest Way to Find a Table

P: n/a
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
May 17 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Mike Charney" <no*****@everything.net> wrote in news:ueGag.77538$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.OpenSchema
(adSchemaTables, Array(Empty, Empty, TableName)).BOF
End Function

But some mght want to use

sp_tables

or sp_tables_ex

--
Lyle Fairfield
May 17 '06 #2

P: n/a

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...
"Mike Charney" <no*****@everything.net> wrote in
news:ueGag.77538$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.OpenSchema
(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
May 17 '06 #3

P: n/a
"Mike Charney" <no*****@everything.net> wrote in
news:p0*******************@newssvr29.news.prodigy. net:

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...
"Mike Charney" <no*****@everything.net> wrote in
news:ueGag.77538$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.OpenSchema
(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.OpenSchema
(adSchemaTables, Array(Empty, Empty, "tblimport")).BOF 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.OpenSchema
(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
May 17 '06 #4

P: n/a

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...
"Mike Charney" <no*****@everything.net> wrote in
news:p0*******************@newssvr29.news.prodigy. net:

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...
"Mike Charney" <no*****@everything.net> wrote in
news:ueGag.77538$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.OpenSchema
(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.OpenSchema
(adSchemaTables, Array(Empty, Empty, "tblimport")).BOF 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.OpenSchema
(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
May 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.