Docmd.SetWarnings False but automatically answer No

[Guest]

After setting the values of the respective variables, I am running this code to copy a table from a patch file to a live database:

DoCmd.SetWarnings False
DoCmd.CopyObject FilePathData, tblName, acTable, tblName
DoCmd.SetWarnings True

However, I need a way to ensure that I do not overwrite the table if it already exists, for obvious reasons. If I do not run DoCmd.SetWarnings False, the user sees the Yes/No dialog and may answer "Yes" (bad news if they already have the table and it is populated with data).

If I run DoCmd.SetWarnings False, the code AUTOMATICALLY overwrites the existing table. What I need is SetWarnings False with an option to force a "No" response.

Alternatively, I need a way to check for the existence of the table in the target DB so that I can run an If...Then statement to determine whether I run the copy statement at all.

 

[Dirk Goldgar]

After setting the values of the respective variables, I am running
this code to copy a table from a patch file to a live database:

DoCmd.SetWarnings False
DoCmd.CopyObject FilePathData, tblName, acTable, tblName
DoCmd.SetWarnings True

However, I need a way to ensure that I do not overwrite the table if
it already exists, for obvious reasons. If I do not run
DoCmd.SetWarnings False, the user sees the Yes/No dialog and may
answer "Yes" (bad news if they already have the table and it is
populated with data).

If I run DoCmd.SetWarnings False, the code AUTOMATICALLY overwrites
the existing table. What I need is SetWarnings False with an option
to force a "No" response.

Alternatively, I need a way to check for the existence of the table
in the target DB so that I can run an If...Then statement to
determine whether I run the copy statement at all.

How about something like this:

' Requires a reference set to the DAO Object Library.

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim lngErrNo As Long
Dim strErrDesc As String

Set db = DBEngine.OpenDatabase(FilePathData)
On Error Resume Next
Set td = db.TableDefs(tblName)
With Err
lngErrNo = .Number
strErrDesc = .Description
End With
Set td = Nothing
db.Close
Set db = Nothing
On Error GoTo 0 ' or your normal error-handler

Select Case lngErrNo

Case 3265
' All's well, the table wasn't found
DoCmd.CopyObject FilePathData, tblName, acTable, tblName

Case 0
' The table already exists, so don't copy it.

Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number & " Checking for Table"

End Select

 

[Guest]

Thanks. It looks like we are simply attempting to trigger & trapthe error that is generated when we try to enumerate a table that does not exist. I have not had time to implement yet, but it looks like it will work.

Just one followup question just in case anyone knows of a slightly simpler fix: is there a cognate Access statement to the batch file "If Exist" that can simply query for the existence of an objec without trying to trigger an error code? In the meantime, I will get this one in place.

Thanks again.

 

[Dirk Goldgar]

Just one followup question just in case anyone knows of a slightly
simpler fix: is there a cognate Access statement to the batch file
"If Exist" that can simply query for the existence of an objec
without trying to trigger an error code? In the meantime, I will get
this one in place.

No. There are a variety of ways to write functions that do this,
including the approach I showed there; another is to query the system
MSysObjects table in the database in question. However, there is no
built-in statement or function to do this.