testing for exisiting table in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Seems straightforward enough, isobject() doesn't work...

I want to create a table in an Access form, but I can't overwrite it if it
persists from a previous instance that I had to kill etc.
I'm sure there's a way to test for its existance, then drop it before
re-creating it if it does, correct?

Thanks
 
Something doesn't sound right. What does your code look like?

You could try simply setting the object to Nothing before recreating.

Set objMyObject = Nothing
 
Hi, Harry.
I'm sure there's a way to test for its existance, then drop it before
re-creating it if it does, correct?
Click to expand...

Yes. Try:

Public Function dropTable(sTableName As String) As Boolean

On Error GoTo ErrHandler

CurrentDb().Execute "DROP TABLE " & sTableName
dropTable = True

Exit Function

ErrHandler:

If (Err.Number = 3376) Then
dropTable = True
Else
MsgBox "Error in dropTable( )." & _
vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description
dropTable = False
End If

Err.Clear

End Function

And then call the function with code such as:

If (dropTable("tblTest")) Then
CurrentDb().Execute "CREATE TABLE tblTest " & _
"(SomeText Text (25), SomeNum Long);", dbFailOnError
Else
MsgBox "Could not drop table."
End If

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
 
Back
Top