MS Access - Macro - Condition Test - If A Table Exists

[rmhindley]

How can I do a condition test in a macro to know if a table exists. I
believe I need to build my own module that searches the database and provides
a true or false return. If so, what is the code?

 

[louisjohnphillips]

How can I do a condition test in a macro to know if a table exists.  I
believe I need to build my own module that searches the database and provides
a true or false return. If so, what is the code?

This would return a 0 if MyTableName does not exist in the database
and a 1 if it does, no?

existsTable = DCount("MyTableName", "MSysObjects",
"[Name]='MyTableName'")

 

[rmhindley]

Needed to determine if I should run a DeleteObject step that would delete the
table

 

[rmhindley]

Hi: I appreciate your response. I tried the statement below in the condition
column of the row and it worked if the table was there, but did nothing if
the table did not exist. Basically, the Macro gave no response message or
anything. It simply stopped.

DCount("Field1","Table1")

I would love to use a function like DCount in the condition column of the
row without having to create a custom module. Any thoughts or recommendations?

Mike

How can I do a condition test in a macro to know if a table exists. I
believe I need to build my own module that searches the database and provides
a true or false return. If so, what is the code?

This would return a 0 if MyTableName does not exist in the database
and a 1 if it does, no?

existsTable = DCount("MyTableName", "MSysObjects",
"[Name]='MyTableName'")

 

[louisjohnphillips]

Hi:  I appreciate your response. I tried the statement below in the condition
column of the row and it worked if the table was there, but did nothing if
the table did not exist.  Basically, the Macro gave no response messageor
anything.  It simply stopped.

DCount("Field1","Table1")

I would love to use a function like DCount in the condition column of the
row without having to create a custom module. Any thoughts or recommendations?

Mike

This would return a 0 if MyTableName does not exist in the database
and a 1 if it does, no?

existsTable = DCount("MyTableName", "MSysObjects",
"[Name]='MyTableName'")- Hide quoted text -

- Show quoted text -

Oops. I forgot about how DCount reacts when the it should return 0.

One workaround might be

on error resume next

existsTable = DCount("MyTableName",
"MSysObjects","[Name]='MyTableName'")

thus trapping the error.

 

[rmhindley]

Hi

Again, thank you for your response. I reposted my question, this time being
a bit more specific on what I need. See the new post. Add to it if you have
any other thoughts after reading it. Figured I might get some other
approaches as well.

Have a great day. Greatly appreciate your feedback.

Mike

Hi: I appreciate your response. I tried the statement below in the condition
column of the row and it worked if the table was there, but did nothing if
the table did not exist. Basically, the Macro gave no response message or
anything. It simply stopped.

DCount("Field1","Table1")

I would love to use a function like DCount in the condition column of the
row without having to create a custom module. Any thoughts or recommendations?

Mike

On Aug 16, 8:41 am, rmhindley <[email protected]>
wrote:
How can I do a condition test in a macro to know if a table exists. I
believe I need to build my own module that searches the database and provides
a true or false return. If so, what is the code?

This would return a 0 if MyTableName does not exist in the database
and a 1 if it does, no?

existsTable = DCount("MyTableName", "MSysObjects",
"[Name]='MyTableName'")- Hide quoted text -

- Show quoted text -

Oops. I forgot about how DCount reacts when the it should return 0.

One workaround might be

on error resume next

existsTable = DCount("MyTableName",
"MSysObjects","[Name]='MyTableName'")

thus trapping the error.

 

[louisjohnphillips]

Hi

Again, thank you for your response. I reposted my question, this time being
a bit more specific on what I need. See the new post.  Add to it if youhave
any other thoughts after reading it. Figured I might get some other
approaches as well.

Have a great day. Greatly appreciate your feedback.

Mike  

Hi:  I appreciate your response. I tried the statement below in thecondition
column of the row and it worked if the table was there, but did nothing if
the table did not exist.  Basically, the Macro gave no response message or
anything.  It simply stopped.
DCount("Field1","Table1")
I would love to use a function like DCount in the condition column ofthe
row without having to create a custom module. Any thoughts or recommendations?
Mike
:
On Aug 16, 8:41 am, rmhindley <[email protected]>
wrote:
How can I do a condition test in a macro to know if a table exists.  I
believe I need to build my own module that searches the database and provides
a true or false return. If so, what is the code?
This would return a 0 if MyTableName does not exist in the database
and a 1 if it does, no?
existsTable = DCount("MyTableName", "MSysObjects",
"[Name]='MyTableName'")- Hide quoted text -
- Show quoted text -

Oops.  I forgot about how DCount reacts when the it should return 0.

One workaround might be

on error resume next

existsTable = DCount("MyTableName",
"MSysObjects","[Name]='MyTableName'")

thus trapping the error.- Hide quoted text -

- Show quoted text -

An alternative is to use a globally visible function. It counts the
number of occurrences of the tablename in MSysObjects.

I have included the restriction that it must be a user table or a
linked user table--"type in (1,6)"


Option Compare Database
Option Explicit

Public Function IfExistsTable(sTable As String) As Boolean

Dim rs As ADODB.Recordset
Dim sql As String
Dim nTableCount As Integer

sql = "SELECT count(*) FROM MSysObjects where Type in ( 1, 6 ) and
Name = "

sql = sql & Chr(39) & sTable & Chr(39)

Set rs = New ADODB.Recordset

nTableCount = 0
rs.Open sql, CurrentProject.AccessConnection, adOpenUnspecified,
adLockUnspecified, -1

While Not rs.EOF
nTableCount = rs(0).Value
Wend

rs.Close

IfExistsTable = IIf(nTableCount = 0, False, True)

End Function

 

[Douglas J. Steele]

That will raise an error, since "MyTableName" doesn't exist in table
MSysObjects. It would need to be:

existsTable = DCount("*", "MSysObjects", "[Name]='MyTableName'")

Of course, that's a little risky, since you could have a query or module
named "MyTableName". Safer would be

existsTable = DCount("*", "MSysObjects", "[Name]='MyTableName' And Type IN
(1, 4, 6)")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

How can I do a condition test in a macro to know if a table exists. I
believe I need to build my own module that searches the database and
provides
a true or false return. If so, what is the code?

This would return a 0 if MyTableName does not exist in the database
and a 1 if it does, no?

existsTable = DCount("MyTableName", "MSysObjects",
"[Name]='MyTableName'")

 

[James A. Fortune]

How can I do a condition test in a macro to know if a table exists. I
believe I need to build my own module that searches the database and provides
a true or false return. If so, what is the code?

Try:

http://groups.google.com/group/comp.databases.ms-access/msg/f27d235232287848

Relying on MSysObjects is not wise since it is undocumented and may
change structure or name from version to version of Access without warning.

For forms I use:

http://groups.google.com/group/comp.databases.ms-access/msg/dfc22f9250f9465e

James A. Fortune
(e-mail address removed)

 

[Douglas J. Steele]

Relying on MSysObjects is not wise since it is undocumented and may change
structure or name from version to version of Access without warning.

While I understand what you're saying, I think Michka's comments are
appropriate
http://www.trigeminal.com/usenet/usenet017.asp

 

[James A. Fortune]

While I understand what you're saying, I think Michka's comments are
appropriate
http://www.trigeminal.com/usenet/usenet017.asp

Microsoft is not bound by any of Michka's claims on that page. While it
is wise to maintain the level of backward-compatibility that Microsoft
does, I try not to count on it more than necessary. Those who use
Michka's guidelines on the safety of future compatibility assume
unnecessary risk, and for little benefit, IMO. However, I have nothing
against them taking that risk. I simply choose not to do likewise.
Even a blog or KB article stating that Microsoft has no plans to change
current table or field names but may add new fields would not convince
me that relying on the MSys tables is the right and proper way. YMMV.

James A. Fortune
(e-mail address removed)

Emotion, not anger. -- Bruce Lee (Enter the Dragon)