How to check for the existence of a SQL Server 2005 table and set an Access VBA variable to True if the table exists and to false if the table does not exist?

I am working with an Access 2003 ADP type application. I use Access as the front end and SQL Server 2005 as the back end database.

How would you write an Access VBA Statement to check for the existence of a SQL Server table and if the table exists, set an Access string variable named strSecurity to TRUE in the Access VBA code and set the strSecurity variable to FALSE if the table does not exist?

Assume the table is named tblSecurity.
Who is Participating?
lluddenConnect With a Mentor Commented:
The SQL command you would use is:
Dim rs As RecordSet
Dim sql AS String

sql = "SELECT count(*) AS Qty FROM sys.objects o WHERE = 'testTable' AND TYPE = 'U'"
Set rs = Application.CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
If rs.Fields("Qty") = 0 Then
    strSecurity = "False"
    strSecurity = "True"
End If

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.