Checking for an existing table

[cindyray]

I am trying to determine whether a table exists in Access when a form loads. I've put the FileSystemObject in the form initialize event. I don't know how to reference the table though.
My code is:
Private Sub Form_Initialize()
Dim strSql As String
Dim strFile As String

Dim objFS As FileSystemObject
Set objFS = CreateObject("FileSystemObject&quot
strFile = "S:\Weddings\Weddings.mdb Temp"
If objFS.FileExists(strFile) Then
strSql = "DROP TABLE Temp"
adoConn.Execute strSql
Else
MsgBox "no temp file exists"
End If

End Sub

When I remove Temp and point it directly at the file, it works, but when I add the table, it goes on to give me the error message.
I haven't been able to find documentation that has an example for a table in a database, so I am thinking that I may be doing this incorrectly.

Any help is greatly appreciated.

Thanks

Cindy

 

[Spellman]

Hi cindy. try something like this....

dim currentdatabase as dao.database
dim found as boolean

found = false
Set CurrentDatabase = Workspaces(0).OpenDatabase("S:\Weddings\Weddings.mdb Temp&quot
For i = 0 To CurrentDatabase.TableDefs.Count - 1
If UCase$(CurrentDatabase.TableDefs(i).Name) = UCase$("Temp&quot Then
found = True
Exit For
End If
Next i

Hope this is helpful. Mark

The key to immortality is to make a big impression in this life!!

 

[cindyray]

Mark,

Thanks. I really appreciate the help.

Cindy

 

[cindyray]

I'm using ado, so that didn't work. After alot more searching through the help file, I tried this routine.

Public Sub CheckTable()
Dim cat1 As New Catalog
Dim i As Integer
Set cat1.ActiveConnection = adoConn
For i = 0 To cat1.Tables.Count - 1
If cat1.Tables(i).Name = "Temp" Then
Debug.Print cat1.Tables(i).Name
adoConn.Execute "DROP TABLE Temp"
End If
i = i + 1
Next i
End Sub

This doesn't find the temp table. It finds every other table in the db, but not the temp.

Thanks for the help.

Cindy

 

[cindyray]

Now I feel like I'm talking to myself.
I figured it out. I just took out "i = i + 1" and it found the table. I don't know what inspired me to increment i instead of letting the loop do it.

Thanks again for the help.

 

[petermeachem]

What you could have done is

On error resume next
adoConn.Execute "DROP TABLE Temp"

Then if it wsn't there, it would just error through. And if it was, it wouldn't be soon. If you see what I mean. Peter Meachem
peter@accuflight.com

 

[cindyray]

Peter

Wow..thanks. Simple, yet effective and less coding.

 

[petermeachem]

I generally have a subroutine around for just that purpose

Sub DropTables(cTable as string)
On error resume next
adoConn.Execute "DROP TABLE " & ctable
End Sub

Peter Meachem
peter@accuflight.com