Open For Seek using a SQL Server table

Question

I have some code that I picked up for a function called OpenForSeek that takes a table name as a string parameter and opens a linked table so that the seek function will work in it.  However, it assumes that it is a linked Access table.  We are in the process of converting our tables to a SQL Server backend, and I have several procedures that use the Seek function.  Is there a way I can change this code to find a SQL Server table instead or does a similar function exist to do the same thing using a SQL Server backend?  The code I have is below:

'************ Code Start ***************

'This code was originally written by Michel Walsh.

'It is not to be altered or distributed,

'except as part of an application.

'You are free to use it in any application,

'provided the copyright notice is left unchanged.

'

'Code Courtesy of

'Michel Walsh

Public Function OpenForSeek(pstrTableName As String) As Recordset

  ' Comments  :

  ' Parameters: pstrTableName -

  ' Returns   : Recordset -

  ' Modified  :

  '

  ' --------------------------------------------------

  ' Assume MS-ACCESS table

  'TVCodeTools ErrorEnablerStart

  On Error GoTo PROC_ERR

  'TVCodeTools ErrorEnablerEnd

  Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _

                  (Mid(CurrentDb().TableDefs(pstrTableName).Connect, _

                  11), False, False, "").OpenRecordset(pstrTableName, _

                  dbOpenTable)

  'TVCodeTools ErrorHandlerStart

PROC_EXIT:

  Exit Function

PROC_ERR:

  MsgBox Err.Description

  Resume PROC_EXIT

  'TVCodeTools ErrorHandlerEnd

End Function

'************ Code End ***************

Answer 1

Most of the time, I'm using it to determine whether a record exists and create one if one doesn't exist (such as creating a person record for the primary contact for a company or a tracking record for an order) or update it if it does exist.  In some cases, I use it in reverse to delete a record if no matching record exists for it in the recordset (such as removing an outstanding discrepancy record from a table when it is resolved and drops off the discrepancy reports).  The way the discrepancy system is handled now opens the discrepancy table one time so I can loop through the recordset for each report and add or delete records as needed.  If I have to open a separate recordset for each record in each report, it's going to be a serious PITA.

One possibility would be to create stored procedures in the SQL Server database that accomplish these specific functions, and then calling the stored procedure and passing it parameters with the key values it needs to do its job.  For example, you could have a stored procedure to create or update a contact, which you would call with the data values for the contact.  Then the sproc would do the work of either creating or updating the contact, and if necessary pass back the primary key of the record it created.  You can call stored procedures by way of pass-through queries, or by means of an ADO Command object.

Alternatively, you could have your own Access VBA function to open a recordset on just a single record and do what you want.

Or you could open a recordset on the whole table and use .FindFirst to locate the record you want.  That won't be as fast as .Seek was, though, and will probably have other negative effects.  Still, it might be good enough for you.

Answer 2

Most of the time, I'm using it to determine whether a record exists and create one if one doesn't exist (such as creating a person record for the primary contact for a company or a tracking record for an order) or update it if it does exist.  In some cases, I use it in reverse to delete a record if no matching record exists for it in the recordset (such as removing an outstanding discrepancy record from a table when it is resolved and drops off the discrepancy reports).  The way the discrepancy system is handled now opens the discrepancy table one time so I can loop through the recordset for each report and add or delete records as needed.  If I have to open a separate recordset for each record in each report, it's going to be a serious PITA.

Answer 3

That's a nice bit of code by Michel, but unfortunately it can't be used with SQL Server.  The Seek method doesn't apply to ODBC databases.  Without knowing exactly how you are making use of the Seek method in your application, I can't suggest the best workaround.  In general, the basic solution is either to open a recordset  on a query that identifies the specific record you want, or else use .FindFirst to locate the record within a recordset containing multiple records.