I've got a function that relinks my tables from the development server
to the production server (sql server 2005)
some of the tables / views are third party, and have no PK, so to
improve access performance I create a PK using this function
Private Sub createIndex(strTable As String, strFields As String)
Dim strSql As String

On Error GoTo fErr
strSql = "CREATE INDEX PK ON [" & strTable & "] (" & strFields &
") WITH PRIMARY;"
CurrentDb.Execute strSql
fExit:
Exit Sub

fErr:
If (Err.Number <> 3283) Then
MsgBox "createIndex " & strSql, True
Resume fExit
Else
Resume Next
End If
End Sub

In access97, if the sql server table has a PK, the refreshLink()
function will create it in access, and the currentdb.execute() will
error out with error 3283 and resume

in access2003, instead of erroring out to fErr, it gives me a popup
box, "runtime error 3283, primary key already exists", "end or debug"

if I replace "on error goto ferr" with "on error resume next", the
same message box is displayed

if I do
dim db as dao.recordset
set db = currentDB
db.execute ...

same message box

any idea why it pops up a message box ?
any workaround ?

Try replacing

CurrentDb.Execute strSql

with

CurrentDb.Execute strSql, dbFailOnError


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)



"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Jul 26, 5:02*pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
nope, same message box

How about checking the primary keys first?

SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
TABLE_SCHEMA,
TABLE_NAME

This will give you the PK's from SQL Server. You can then apply
whatever logic you want after that...

Cheers

The Frog

On Jul 27, 6:10*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
good idea, but I checked in access using
Private Function pkExists(strTable As String) As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

On Error GoTo fErr
pkExists = False
Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
For Each idx In tdf.Indexes
If (idx.Primary) Then
pkExists = True
Exit For
End If
Next

fExit:
On Error Resume Next
Set idx = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Function

fErr:
errorLog "pkExists " & strTable
Resume fExit
End Function

On Jul 27, 7:55*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
changing the options to 'break on unhandled errors' instead of 'all
errors' solved the problem

"Douglas J. Steele" <NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote in
news:i2l45k$qpj$1 (AT) news (DOT) eternal-september.org:

But don't just do that without adding an error handler!

Or, Google for my SQLRun function that is an error-handled wrapper
around that command designed to be a dropin replacement for
DoCmd.RunSQL().

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/