SQL passthrough Stored Procedure Better way than dlookup? (1 Viewer)

[ozinm]

Can anyone suggest a better way to do this?

I've got a stored procedure in SQL that creates a copy of a record in our contact database and returns a table with a single row to return the new Primary key for the new record.

Background

In access I've created a general passthrough query called "SQLCall" that connects to the sql database.

I've created a general procedure to use this query to call SP's and get data:

Public Sub SQLExec(ByVal sSQL As String _
    , Optional bRetRecords As Boolean = False _
    , Optional bExecute As Boolean = True _
    , Optional sQueryName As String = "SQLCall" _
    , Optional vConnect As Variant)
    
' vConnect      : Optional connection string
' bRetRecords   : Optional set return records on / off. Default: on
' sQueryName    : Optional set alternate location for query storage

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sConnect As String

    Set db = CurrentDb
    Set qdf = db.QueryDefs(sQueryName)
    If IsMissing(vConnect) Then
        sConnect = qdf.Connect
    Else
        sConnect = CStr(vConnect)
    End If
    
    qdf.SQL = sSQL
    qdf.Connect = sConnect
    qdf.ReturnsRecords = bRetRecords
    If bExecute Then qdf.Execute

    GoTo ErrorExit
ErrorHandler:
    Select Case Err.Number
    Case Else
        MsgBox cErrorIntro & vbCrLf _
        & vbCrLf & Err.Number _
        & vbCrLf & Err.Description _
        & vbCrLf & Err.Source _
        , vbCritical + vbOKOnly, "Error"
    End Select
    Resume Next
ErrorExit:
End Sub

Problem
The code I'm using to create a record is:

TempSQL = "spContactCopy " & Nz(ContactID, "0")
SQLExec TempSQL, True, False
TempNewID = DLookup("ContactID", "SQLCall")
        
'If a Contact ID was returned, jump to the new record
If Not IsNull(TempNewID) Then
      DoCmd.OpenForm "frmContact", acNormal, , "ContactID = " & CStr(TempNewID), acFormEdit
End If

Unfortunately, this seems to run the stored procedure 3 times as I end up with 3 copies of the contact on the database.
I've checked the stored procedure and can confirm that when it's called from sql it only creates a single copy.
I'm guessing that it's something to do with using dlookup.

Could someone suggest a better way of doing this?

 

[ozinm]

Hi All,
If this would be better posted in another area please let me know.
Tar.