Using Seek on linked table

Seek only works with local database.  To use seek on a linked table, you must open the database that contains the linked table, then operate on that (the table is then "local").  See below for example.

Jim.

Function GetUserPreferences(lngUserID As Long, strObjectName As String) As Variant

  Dim wrk As Workspace
  Dim dbCurrent As Database
  Dim dbRemote As Database
  Dim tdfAttached As TableDef
  Dim strPath As String
  Dim rst1 As Recordset
  Dim rst1Field As Field

'
' First, get the path to the MDB for the attached table.
'
  Set wrk = DBEngine.Workspaces(0)
  Set dbCurrent = wrk.Databases(0)
  Set tdfAttached = dbCurrent.TableDefs("tblUserPreferences")
 
  strPath = tdfAttached.Connect
  strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
 
  Set dbRemote = wrk.OpenDatabase(strPath, False, False)
 
'
'  Find the peference for the user
'
  Set rst1 = dbRemote.OpenRecordset("tblUserPreferences", DB_OPEN_TABLE)
  rst1.index = "PrimaryKey"
  rst1.Seek "=", lngUserID, strObjectName

  If rst1.NoMatch Then
    GetUserPreferences = Null
  Else
    GetUserPreferences = rst1![Value]
  End If

  rst1.Close
  Set rst1 = Nothing
  dbRemote.Close
  Set dbRemote = Nothing
  Set tdfAttached = Nothing
  Set dbCurrent = Nothing
  Set wrk = Nothing


End Function

sorry but I really dont understand any of this.  Can you use my attachement and enter the actual names and paths that I gave in it and where do I put all this.  Thanks

Change you code to use FindFirst.
Syntax is a little different but not difficult. Study the on-line help for examples.

/gustav

Your combo21_AfterUpdate code needs to look like this:

  Dim wrk As Workspace
  Dim dbCurrent As DAO.Database
  Dim dbRemote As DAO.Database
  Dim tdfAttached As DAO.TableDef
  Dim strPath As String
  Dim rstCust As DAO.Recordset

'
' First, get the path to the MDB for the attached table.
'
  Set wrk = DBEngine.Workspaces(0)
  Set dbCurrent = wrk.Databases(0)
  Set tdfAttached = dbCurrent.TableDefs("customer")
 
  strPath = tdfAttached.Connect
  strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
 
  Set dbRemote = wrk.OpenDatabase(strPath, False, False)

'
'  Find the customer
'
  Set rstCust = dbRemote.OpenRecordset("customer", DB_OPEN_TABLE)
  rstCust.index = "cus_no"

<rest of the procedure follows and then end with what's below>

rstCust.Close
Set rstCust = Nothing
dbRemote.Close
Set dbRemote = Nothing
Set tdfAttached = Nothing
Set dbCurrent = Nothing
Set wrk = Nothing


However with that said, it would be better if you opened the DB when the form opened and then closed it when the form closed rather then opening/closing each time you do a search, but the above will work.

Jim.

Jim (JDettman)- copied and pasted code in and tried and got the error "User-defined type not defined".  see attached
Access-Link-Table-Error.docx

Make it DAO.Workspace

and with a code window open, under tools/references, make sure a reference is set for Microsoft Data Access Objects (DAO) lib.

3.61 was the last, but 3.60 will work as well.

With A2010, the default data lib is ADO.

Jim.

"With A2010, the default data lib is ADO."

Not here, it ain't!!  It doesn't even feature in the references unless you select it.

Jim (JDettman) thanks for your patience and this part did work and thought I could take it from here but this same form calculates a discount having to use the customer table, the items table, and a disc table (discount).  So really not sure how to handle so I am attaching the code for advice.  Plus is there any easier way to do this with less code or is this the best way?  Look forward to hearing from you.
Access-Link-Table-Code.docx

@peter,

<<Not here, it ain't!!  It doesn't even feature in the references unless you select it.>>

 Right you are...I had forgotten that they switched that back in 2007 when they did the new ACE format (shows you how much I use A2007/2010 - zip).

 He must have had ADO selected for some reason though or he would not have gotten the error.

Jim.

Jim - got syntax error see attached
Access-Link-Table-SyntaxErr.docx

The message box line at the bottom needs to be:

   MsgBox "Unexpected Error", vbCritical + vbOKOnly


Jim.

Well, the lazy dog would use FindFirst and skip all the direct-backend stuff which rarely is needed.

/gustav

gustav - after seeing the other solution can you show me exactly what you mean by using the above code

Jim okay but it pops the error box everytime I select an item and once I click ok on the error box then the form completes

<<Well, the lazy dog would use FindFirst and skip all the direct-backend stuff which rarely is needed.>>

  Yes and the lazy dog might end up with a poorly performing app depending on the size of the tables.

  I would assume that because someone took the time to use seek in the first place performance was a concern.

 And really, the changes we are talking about here are minor.  It's not more then a half dozen lines of code that need to be added to get a split design using seek to work.

 The downside however is that you are opening another connection to the BE DB, so you've just cut your user limit to 128 users.  But I've never seen that to be a problem yet.

Jim.

<<Jim okay but it pops the error box everytime I select an item and once I click ok on the error box then the form completes>>

 Then it's hitting an error some where.  You didn't have error checking before, so anything bad happening was being ignored.

 Put a STOP at the top of the procedure, execute it and the code window will popup.  Press F8, which will execute the code line by line each time you press it.

 When you jump to the error handler, then the line that was just executed is causing the problem.  Why it's a problem at present I can't tell you.  You can change the line to:

MsgBox "Unexpected Error " & err.number & " - " & err.description, vbCritical + vbOKOnly

so that it will give you the error and description of the error.

Jim.

Jim- guess its not pulling correctly because it errors at near the end where it says: If [luqty2] > 0 Then
Assume this again is due to linked tables because I use DLookup fields on the form (see attached) and assume I cannot do this.  Surprise the error doesnt happen on the code where it has luqty2 requery above it.  So does this mean changing my form also because I do several lookup fields on the form inorder to do a field for stating the price which is shown in the second screen shot.  Notice I have four of each.  Told you I was amature
Access-Link-Table-DLookup.docx

Jim, Seek is sometimes used just because it is there, not for a specific performance issue.  FindFirst will almost always perform well if good indexes are defined.  I don't think we ever use Seek in our applications - we don't find that it's worth the extra code complexity.

Cheers,
Armen

Armen,

<<Jim, Seek is sometimes used just because it is there, not for a specific performance issue.>>

 I have not found that to be the case.  Over the many years I've been answering questions or in the apps that I've seen, I've never seen seek used as a run of mill type thing.  More often then not, folks don't even know about it.

 In fact I can't think of a time when I've seen it used that it wasn't done to address a performance problem.

<<FindFirst will almost always perform well if good indexes are defined.   I don't think we ever use Seek in our applications - we don't find that it's worth the extra code complexity.>>

 Hum...we I find it well worth it. I use it whenever I have a critical lookup function.  For example, I store user preferences for a number of things throughout my apps and the code that fetches and sets those uses seek.   Postal code lookup is another place where I use it.

Seek is at least an order of a magnitude faster then findfirst and in my book, it's well worth the little extra code, which is by no means rocket science. You just need to open the remote DB and set a reference for it and use that in place of CurrentDB().  And a line to set the index, and then some cleanup.  Not a big deal.

Jim.

Of course Seek is faster, but often is doesn't make any difference to the user.
I have never used Seek on a backend database except for the fun(!) - but databases are rarely above 100 MB.

How to use FindFirst: Look up the on-line help for some examples.

/gustav

ok guys I have to agree with Jim because the sizes are huge, theusers are 10 strong and speed is essential.  Jim- anything on that dlookup

Jim - disregard last posting thats my fault and have corrected it and seems to work fine.  I am new to this site so once I submit this I will accept yours as a solution.

Thanks for all your help

HFSCI, glad you got it working.  The value of a forum like this is that you get multiple experienced opinions to choose from!  :)

Cheers,
Armen

<<The value of a forum like this is that you get multiple experienced opinions to choose from!  :)>>

 One of the pleasures of always coming back here.  I know I've expanded my horizons over the years!

 Hope everyone has a great day.

Jim.