Check Table existence

[applek]

Hi, does anyone have any idea of how to check a table is exist in Microsoft Access? I mean in SQL form?

 

[CCLINT]

You can use ADOX: thread222-427506

Or, you can run an SQL SELECT statement and catch the error which occurs if it doesn't exist:

On Error Resume Next

sqlString = "SELECT COUNT(*) FROM TheTable"

'Your recordset Open statement here (ADO or DAO)

bTableExists = Not CBool(Err)

Err.Clear
On Error goto ErrHandler [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[MichaelRed]

Sometimes, it is useful to know why you ned to 'check' this. If, for example, it is just to delete a temp table before re-creating it, you do not always need to delete the old, as Ms. A. will just write over the existing one.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CCLINT]

> you do not always need to delete the old, as Ms. A. will >just write over the existing one

MichaelRed: Using which method? [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[MichaelRed]

Standard "MakeTable" query, Available from the query grid menu bar. "Select... Into & tblName ...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Schroeder]

If I run a "Make Table" query using DAO's Execute method, I get an error message saying the table already exists. I always put this:

On Error Resume Next
dbDatabase.TableDefs.Delete "tablename"
On Error GoTo Err_Handler

right before running them.

 

[Terpsfan]

Another approach is to access the database schema through ADO. First set a reference to Microsoft ActiveX Data Objects 2.x.

Dim objConn as ADODB.Connection
Dim objRec as ADODB.Recordset

Set objConn = New ADODB.Connection
objConn.ConnectionString = "Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\mydatabasename.mdb"
objConn.Open

Set objRec = objConn.OpenSchema(adSchemaTables)
Do Until objRec.EOF
If objRec!TABLE_NAME = "tblYours"
MsgBox "Table found"
Exit Do
End If
Loop

objRec.Close
Set objRec = Nothing
objConn.Close
Set objConn =Nothing

 

[Terpsfan]

Sorry, left a few important things out...

Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset

Set objConn = New ADODB.Connection
objConn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = D:\AccessDB\MDSports.mdb"
objConn.Open

Set objRec = objConn.OpenSchema(adSchemaTables)
Do Until objRec.EOF
If objRec!TABLE_NAME = "tblNBA" Then
MsgBox "Table found"
Exit Do
End If
objRec.MoveNext
Loop

objRec.Close
Set objRec = Nothing
objConn.Close

 

[MichaelRed]

Schroeder, it should not be an error message, just a warning, which can be turned off with Set Warnings off | on


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Schroeder]

If I run a Make Table query in the Access IDE, it's a warning box which I have, on occasion, disabled via SetWarnings. I'm talking about sending a "SELECT ... INTO tablename" SQL statement to Access via DAO's Execute method. When I do that, I get:

Run-time error '3010'
Table tablename already exists.

 

[MichaelRed]

Sorry, I must have never tried it that way. Oh-well live and learn, I guess.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CCLINT]

Schroeder: What ACCESS does is basically the same logic you would use is VB (this is the VB forum, and not the ACCESS forum, and assumming that the table creation is done via ADOX or DAO):

1. A table needs to be created.

2. Either a check is made first to see if the table already exists, or, an attempt is made to create the table and an error catcher is in place.

3. (Option) If the table already exists, then:
- inform the user, and allow the user to still have the table replaced, continuing then with step 4, and if the user does not want to replace the table, cancel the operation;
- or, if informing the user is not desired, just continue with the next step, not displaying the error message if you decide to still have the table replaced.

4. If the user decides to replace the table, or, you have decided in code that this would be done automatically,

- then delete the table using the Delete method of TableDefs in DAO;

- or, in DAO or ADO, use a "Drop Table" SQL statement with the Connection (or Command) object's Execute method;

- or, in ADOX use the Delete method of the catalog's tables collection.


I believe even creating an Delete Table query in MS ACCESS, saving it, and calling via the Execute method of ADO or DAO will not even work.

So, the only place where this seems to work (an MS table being automatically over written) is when running the query from the query window with-in the MS ACCESS application, or creating an MS ACCESS object in VB and calling the DoCmd that way, meaning the MS ACCESS run time files need to be on that same computer.
But I would consider that (deleting the table with MS ACCESS application methods) just as much of an automatic process as when you would writen the same code, as mentioned above, in a VB application.

It is not the native JET or ADO/DAO, or the MDB its self, asking the user if the table should be overwritten, when trying to do so when the table already exists, and if so desired, then continueing with the operation, but the application which is handling this, passing the necessary sql commands ("Drop Table", "Create Table&quot to JET via DAO/ADO.



[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}