Check for a table

[Guest]

Can you programatically check a database to see if a specific table exists in
your database?

I have a number of cost centers and there are several different versions
existing, depending when they came on-line.

What I would like to do is write (or borrow) some code that will check a
database for a specific table.

If it doesn't exist, I would like to:
1) Add the table
2) Add 2 fields for Group and Branch
3) Populate both fields with 5.0 (I assume this is an update query)

Any help getting started would be appreciated. Thanks

 

[Jeff Boyce]

I may be reading too much into your description...

It sounds like you have a separate database for each "cost center". If so,
this is exactly the design you'd need to use ... for a spreadsheet-based
solution! In Access, you won't get the best use of the
relationally-oriented features and functions if you feed it "sheet" data.

Just in case you do have separate dbs, consider creating a single db, and
using one additional field in any relevant table(s), that field holding the
CostCenterID.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

In

Can you programatically check a database to see if a specific table
exists in your database?

I have a number of cost centers and there are several different
versions existing, depending when they came on-line.

What I would like to do is write (or borrow) some code that will
check a database for a specific table.

If it doesn't exist, I would like to:
1) Add the table
2) Add 2 fields for Group and Branch
3) Populate both fields with 5.0 (I assume this is an update query)

Any help getting started would be appreciated. Thanks

Check if table exists:

Function fncTableExists(TableName As String) As Boolean
On Error Resume Next
fncTableExists = _
(TableName = CurrentDb.TableDefs(TableName).Name)
End Function

Add table and create fields (an example of one way):

CurrentDb.Execute _
"CREATE TABLE MyTable (Group DOUBLE, Branch DOUBLE)", _
dbFailOnError

Add a record with values of 5.0, 5.0:

CurrentDb.Execute _
"INSERT INTO MyTable ([Group], Branch) VALUES (5.0, 5.0)", _
dbFailOnError

Note that the Group field must be enclosed in brackets because it's a
reserved word in SQL. You would do better to use a different name for
this field.

 

[Guest]

Thanks. I'll give this a try.....
--
JT

In

Can you programatically check a database to see if a specific table
exists in your database?

I have a number of cost centers and there are several different
versions existing, depending when they came on-line.

What I would like to do is write (or borrow) some code that will
check a database for a specific table.

If it doesn't exist, I would like to:
1) Add the table
2) Add 2 fields for Group and Branch
3) Populate both fields with 5.0 (I assume this is an update query)

Any help getting started would be appreciated. Thanks

Check if table exists:

Function fncTableExists(TableName As String) As Boolean
On Error Resume Next
fncTableExists = _
(TableName = CurrentDb.TableDefs(TableName).Name)
End Function

Add table and create fields (an example of one way):

CurrentDb.Execute _
"CREATE TABLE MyTable (Group DOUBLE, Branch DOUBLE)", _
dbFailOnError

Add a record with values of 5.0, 5.0:

CurrentDb.Execute _
"INSERT INTO MyTable ([Group], Branch) VALUES (5.0, 5.0)", _
dbFailOnError

Note that the Group field must be enclosed in brackets because it's a
reserved word in SQL. You would do better to use a different name for
this field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)