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.

"Brian" <(E-Mail Removed)> wrote in message
news:1C83DEC7-0755-431A-B2D7-(E-Mail Removed)
> 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


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

> "Brian" <(E-Mail Removed)> wrote in message
> news:1C83DEC7-0755-431A-B2D7-(E-Mail Removed)
> > 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
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"Brian" <(E-Mail Removed)> wrote in message
news:633268B7-92C1-420D-904D-(E-Mail Removed)
>
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)