look for table

hi,
what is the best way (and the shortest ) to see if the table is exiting in the database
omer

Quote:

---

Originally Posted by omerg84

hi,
what is the best way (and the shortest ) to see if the table is exiting in the database
omer

---

Which database??

regular database in access,with tables

Quote:

---

Originally Posted by omerg84

regular database in access,with tables

---

The easiest way would be to check it using the Query

PHP Code:

---

Select * From MSysObjects Where [NAME] = 'TABLENAME' 


---

does it works????

Quote:

---

Originally Posted by omerg84

does it works????

---

If it wouldn't have been working then I wouldn't have posted it.

i need example of using this statment
thanks,omer

Open a recordset using the above posted query and check if the recordset returns any records.

anyone can give me an example how to open recordset with the quary:Select * From MSysObjects Where [NAME] = 'TABLENAME'
in vb
omer

another problem,i can't read from msysobjects beacuse i have security problem..how can i allow the program to read from msysobjects?
thanks
omer

Here is a sample of how this can be done

VB Code:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myMDB.mdb"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * From MSYSOBJECTS Where [NAME] = 'myTable'"
If rs.EOF Then
   Msgbox "Table dooes not Exist"
Else
   MsgBox "Table Exists"
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

You will need to add a reference to Microsoft ActiveX Objects 2.x Library.

What is the error message that you are getting?

the error is that i don't have promision to read from msysobject
(i did the code with regular dao and it works...not with adodb)

Quote:

---

Originally Posted by omerg84

the error is that i don't have promision to read from msysobject
(i did the code with regular dao and it works...not with adodb)

---

Can you show us what you did?

i opened a record with the quary like:
"Select * From MSYSOBJECTS Where [NAME] = 'myTable'"
and it shows an error that i don't have promission to read from MSYSOBJECTS
how can i solve it?
omer

That is not the code that I had asked for. How are you doing it in VB? What is the connectionstring, etc??

I use a different trick by checking the success / failure of an special SQL statement that only returns one row. I am aware that any database error will also show up as a failure. The advantage is that I don't rely on database schema permissions:

VB Code:

'-------------------------------------------------
' Check if a table or field exists in the database
'-------------------------------------------------
Public Function TableFieldExist(Table As String, Field As String)
 
    Dim SqlCmd As String
    
    On Error Resume Next
    
    If Field = "" Then
        SqlCmd = "select count(*) from " & Table
    Else
        SqlCmd = "select count(" & Field & ") from " & Table
    End If
    
    ' Verifier si la table ou le champ existe
    Set rsTemp = dbsdata.Execute(SqlCmd)
    If Err.Number = 0 Then
        rsTemp.CloseRecordset
        Set rsTemp = Nothing
        TableFieldExist = True
    Else
        TableFieldExist = False
    End If
    
End Function

thanks but i need example for how to check the exsiting of table,not field

With my function:

VB Code:

' Check if a table exists
if TableFieldExist("Customers", "") then ...
 
' Check if a field exists
if TableFieldExist("Customers", "CustName") then ...