look for table

[omerg84]

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

[Shuja Ali]

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

Which database??

[omerg84]

regular database in access,with tables

[Shuja Ali]

regular database in access,with tables

The easiest way would be to check it using the Query

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

[omerg84]

does it works????

[Shuja Ali]

does it works????

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

[omerg84]

i need example of using this statment
thanks,omer

[Shuja Ali]

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

[omerg84]

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

[omerg84]

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

[Shuja Ali]

Here is a sample of how this can be done

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?

[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)

[Shuja Ali]

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?

[omerg84]

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

[Shuja Ali]

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

[JacquesLebrun]

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:

'-------------------------------------------------
' 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

[omerg84]

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

[JacquesLebrun]

With my function:

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