Working with Access Dabatabases through VBA Codes in Excel

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 OLEDB:Database 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

"Dennis" wrote
: 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.
:

Most examples you find on how to do this catch the error code. Here is a
link to MSDN with an example
http://support.microsoft.com/default...b;en-us;253857
Paul D

Thanks Paul,

It's good alternative, but I wonder if exist way do it with "New
ADODB.Connection" method.

Please help me!

Dennis.

"PaulD" wrote:

> "Dennis" wrote
> : 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.
> :
>
> Most examples you find on how to do this catch the error code. Here is a
> link to MSDN with an example
> http://support.microsoft.com/default...b;en-us;253857
> Paul D
>
>
>

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 OLEDB:Database 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

Thanks Michel, you're greatfull!

"michelxld" wrote:

>
> 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 OLEDB:Database 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
>
>
> --
> michelxld
> ------------------------------------------------------------------------
> michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
> View this thread: http://www.excelforum.com/showthread...hreadid=397397
>
>