• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

What is the command to check for a table and then give the user a message if it is not.

I would like my database to check for table crexport and if it is not there I need it to display instructions (msgbox) for the user to follow. If it is there I will have it "do something". Thanks for any help.
0
marlind
Asked:
marlind
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can use a function like this

Function TableExists(TableName as string) as boolean
dim db as database, tbl as tabledef
set db as currentdb()

tableexists=false

          For Each tbl In db.TableDefs
               If tbl.Name = TableName Then
                    tableExists = True
                    Exit Function
               End If
          Next tbl

End Function
0
 
shanesuebsahakarnCommented:
You need a function for this - this will do it:

Function ExistsTable(strTableName As String) As String
ExistsTable=Not IsNull(DLookup("[Name]","MSysObjects","[Name]='" & strTableName & "' AND Type=6"))
End Function

You can now do:

If ExistsTable("MyTable")=False Then
   MsgBox "Do something!"
End If
0
 
shanesuebsahakarnCommented:
This line:
Function ExistsTable(strTableName As String) As String

should obviously read:
Function ExistsTable(strTableName As String) As Boolean

:-)
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
marlindAuthor Commented:
shanesuebsahakarn,
I have this
Function ExistsTable(strTableName As String) As Boolean
ExistsTable = Not IsNull(DLookup("[Name]", "MSysObjects", "[Name]='" & strTableName & "' AND Type=6"))
End Function
I have this on a command button
If ExistsTable("CRExport") = False Then
   MsgBox "Do something!", vbCritical, "Quality Control"
The message box appears even if the table is there. I would like a frmuserselection to open if the table is there, and the msgbox to appear if the table is not there.  I played around with the if statement put couldn't get my form to come up. I must be missing something.
0
 
NestorioCommented:
On Shane's answer,

I think type for tables is = 1, then:

Function ExistsTable(strTableName As String) As Boolean
ExistsTable=Not IsNull(DLookup("[Name]","MSysObjects","[Name]='" & strTableName & "' AND Type=1"))
End Function
0
 
shanesuebsahakarnCommented:
My apologies, the code should be:

Function ExistsTable(strTableName As String) As Boolean
ExistsTable = Not IsNull(DLookup("[Name]", "MSysObjects", "[Name]='" & strTableName & "' AND Type=1"))
End Function
0
 
shanesuebsahakarnCommented:
Nestorio beat me to it :)
0
 
marlindAuthor Commented:
shanesuebsahakarn,
Perfect. Now I have to figure out what my do something instructions should be. Thanks for the help.
0
 
Rey Obrero (Capricorn1)Commented:
marlind
did you try my post?

If TableExists("CRExport")=False Then
  MsgBox "Do something"
else
  Docmd.OpenForm "frmuserselection"
end if
0
 
marlindAuthor Commented:
It may work too but I chose shanesuebsahakarn answer first. Thank you for giving the replying to my question.
0
 
Rey Obrero (Capricorn1)Commented:
marlind
It will work definitely, and sad to say that i posted first and never got any chance.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now