hi,
what is the best way (and the shortest ) to see if the table is exiting in the database
omer
Printable View
hi,
what is the best way (and the shortest ) to see if the table is exiting in the database
omer
Which database??Quote:
Originally Posted by omerg84
regular database in access,with tables
The easiest way would be to check it using the QueryQuote:
Originally Posted by omerg84
PHP Code:Select * From MSysObjects Where [NAME] = 'TABLENAME'
does it works????
If it wouldn't have been working then I wouldn't have posted it.Quote:
Originally Posted by omerg84
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 doneYou will need to add a reference to Microsoft ActiveX Objects 2.x Library.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
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)
Can you show us what you did?Quote:
Originally Posted by 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
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 ...