Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Determine if Query or Table Exists 5

Status
Not open for further replies.

ActMod

Technical User
Aug 25, 2003
45
US
I have the following line in a VBA program:

rs.Open "Select * from " & QryTable1

QryTable1 is a string variable that should equal the name of a Query of Table in my Database.

Obviously, I get an error whenever QryTable1 does not match a Query or Table in my Database.

My question is can I precede the above line with a line or two that tests whether or not "QryTable1" actually exists in my Database? I would like to set the variable QryTableTEST = 1 whenever "QryTable1" does not exist.

Thank you for any help.

Jim
 
Check out for a dB with a sample function.

Use it like:

If ObjectExists("Table",QryTable1) or ObjectExists("Query",QryTable1) then
rs.Open "Select * from " & QryTable1
End If

As an alternative you could trap the error and deal with it that way.

hth

Ben

----------------------------------------------
Ben O'Hara "Where are all the stupid people from...
...And how'd they get so dumb?"
rockband.gif
NoFX-The Decline
----------------------------------------------
 
Jim:

I use a function (courtesy of one of the Tek-Tips gurus) to test whether an object exists. Here is the code:

Public Function ObjectExists(ObjType As Integer, ObjName As String) As Boolean

Dim strTemp As String
Dim dbTemp As Database
Dim strContainer As String

On Error Resume Next

Set dbTemp = CurrentDb

Select Case ObjType
Case acTable
strTemp = dbTemp.TableDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acQuery
strTemp = dbTemp.QueryDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acMacro, acModule, acForm, acReport
Select Case ObjType
Case acMacro
strContainer = "Scripts"
Case acModule
strContainer = "Modules"
Case acForm
strContainer = "Forms"
Case acReport
strContainer = "Reports"
End Select
strTemp = dbTemp.Containers(strContainer).Documents(ObjName).Name
ObjectExists = (Err.Number = 0)
End Select

Set dbTemp = Nothing

End Function

It's called from a conditional like:

If ObjectExists("QryTable1") Then
rs.Open "Select * from " & QryTable1
Else
Whatever action you need to take
End If

Hope that helps.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 
Thanks to both Ben and Larry. But I am still having problems. I am currently using ADODB in Access 2002, which does not seem to recognize either QueryDefs or TableDefs. Is that because I am not using DAO?

Jim
 
Jim:

Could be. I am using the function in A2K apps, but I have also set the DAO 3.x reference.

Try setting that reference and see if that allows the function to run.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 
Larry,

Yes, that is what I needed to do. Thanks very much.

Jim
 
Thanks Larry, your example was perfect for what I was looking for!

JR
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top