Recordset (table) exist?

[Guest]

I am trying to open a recordset using the following SQL statement and would
like to display a message if the table (record Source) IGFY07Q1 does not
exist. How do I test for its existence before invoking the OpenrecordSet
command?

Set rst = db.OpenRecordset(stSQL)

stSQL = "Select IGFY07Q1.[territory number], IGFY07Q1.Measure,
IGFY07Q1.revenue, IGFY07Q1.Goal" _
'& " From IGFY07Q1" _
'& " Where Left(IGFY07Q1.[territory number], 10) = ""1-2-7-21-2"" And
Right(IGFY07Q1.[territory number], 2) <> ""-0"";"

 

[Douglas J. Steele]

Create a TableExists function. Something like the following will work:

Function TableExists(TableName As String) As Boolean
' Returns True if TableName exists, False otherwise
On Error Resume Next

TableExists = (CurrentDb.TableDefs(TableName).Name = TableName)

End Function

Then, change your code to

If TableExists("IGFY07Q1") Then
Set rst = db.OpenRecordset(stSQL)
End If

Just a comment, though. I'm guessing you've got multiple tables for
essentially the same thing, and that you're storing data in the name of the
table (I'm guess FY07Q1 stands for Q1 of fiscal year 2007). That's a bad
idea. You should only have a single table to each entity, with appropriate
attribution on the table to ensure you can retrieve the data you want. If
you design correctly, you'd never have a situation where a specific table
doesn't exist. You could, of course, have a situation where the specific
data you want doesn't exist in the table, but that's a lot easier to deal
with.

 

[Guest]

Doug,

Worked like a charm

Thanks

Create a TableExists function. Something like the following will work:

Function TableExists(TableName As String) As Boolean
' Returns True if TableName exists, False otherwise
On Error Resume Next

TableExists = (CurrentDb.TableDefs(TableName).Name = TableName)

End Function

Then, change your code to

If TableExists("IGFY07Q1") Then
Set rst = db.OpenRecordset(stSQL)
End If

Just a comment, though. I'm guessing you've got multiple tables for
essentially the same thing, and that you're storing data in the name of the
table (I'm guess FY07Q1 stands for Q1 of fiscal year 2007). That's a bad
idea. You should only have a single table to each entity, with appropriate
attribution on the table to ensure you can retrieve the data you want. If
you design correctly, you'd never have a situation where a specific table
doesn't exist. You could, of course, have a situation where the specific
data you want doesn't exist in the table, but that's a lot easier to deal
with.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to open a recordset using the following SQL statement and would
like to display a message if the table (record Source) IGFY07Q1 does not
exist. How do I test for its existence before invoking the OpenrecordSet
command?

Set rst = db.OpenRecordset(stSQL)

stSQL = "Select IGFY07Q1.[territory number], IGFY07Q1.Measure,
IGFY07Q1.revenue, IGFY07Q1.Goal" _
'& " From IGFY07Q1" _
'& " Where Left(IGFY07Q1.[territory number], 10) = ""1-2-7-21-2"" And
Right(IGFY07Q1.[territory number], 2) <> ""-0"";"