Prev: Selling the boss on a "publish to the web" Access app?
Next: Can AlbertCo's tables be accessed?
From: Roger on 26 Jul 2010 18:34
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 ?
From: Douglas J. Steele on 26 Jul 2010 19:02
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(a)natpro.com> wrote in message
news:80f8be63-766d-4127-9e47-04d34af6b8cf(a)g19g2000yqc.googlegroups.com...
> 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 ?

From: Roger on 26 Jul 2010 19:44
On Jul 26, 5:02 pm, "Douglas J. Steele"
<NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> Try replacing
>
> CurrentDb.Execute strSql
>
> with
>
> CurrentDb.Execute strSql, dbFailOnError
>
> --
> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/djsteele
> Co-author: "Access 2010 Solutions", published by Wiley
> (no e-mails, please!)
>
> "Roger" <lesperan...(a)natpro.com> wrote in message
>
> news:80f8be63-766d-4127-9e47-04d34af6b8cf(a)g19g2000yqc.googlegroups.com...
>
>
>
> > 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 ?- Hide quoted text -
>
> - Show quoted text -

nope, same message box
From: The Frog on 27 Jul 2010 08:10
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
From: Roger on 27 Jul 2010 09:55
On Jul 27, 6:10 am, The Frog <mr.frog.to....(a)googlemail.com> wrote:
> 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

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
 |  Next  |  Last
Pages: 1 2
Prev: Selling the boss on a "publish to the web" Access app?
Next: Can AlbertCo's tables be accessed?