Working with Access Dabatabases through VBA Codes in Excel

[Dennis]

I'm working with Access Databases through VBA codes from Excel, but I have a
problem when typing "DROP TABLE MYTable;" (In the case that table doesn't
exist)

Then, how know if MYTable exists, before writing the instruction DROP TABLE.

I write them my example:

Dim cnn As New ADODB.Connection
Set cnn = New Connection
dbName = ("C:\Data\MYDataBase1.mdb")
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeWrite
.Properties("Jet OLEDBatabase Password") = "abc"
.Open dbName
End With

'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "DROP TABLE MYTABLE;"

'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn

'close connection
cnn.Close
Set cnn = Nothing
Set rs = Nothing

 

[michelxld]

Hello Dennis , Hello Paul

you may try this macro to know if a table exist


Dim cnn As New ADODB.Connection
Dim rsT As ADODB.Recordset
Dim Verif As Boolean
Dim dbName As String

Set cnn = New Connection
dbName = ("C:\Data\MYDataBase1.mdb")
With cnn
..Provider = "Microsoft.Jet.OLEDB.4.0"
..Mode = adModeWrite
..Properties("Jet OLEDBatabase Password") = "abc"
..Open dbName
End With

Set rsT = cnn.OpenSchema(adSchemaTables)

Verif = False
While Not rsT.EOF
If rsT.Fields("TABLE_NAME") = "MYTABLE" Then Verif = True
rsT.MoveNext
Wend

If Verif = False Then
MsgBox "The Table does not Exist ."
Else
MsgBox "the table exist"
End If

cnn.Close
Set cnn = Nothing
Set rsT = Nothing


Regards ,
michel

 

[Dennis]

Thanks Michel, you're greatfull!