Using Seek on linked table

I built a database program in Access 2003 and we are now using 2010 and I need to expand it from single user to multi-user (front end and back end)  I have a database on the server as the backend and a database on workstations for the frontend and all the tables in the front end are linked to the backend database.  The attached code worked fine until made linked tables and need to find a way to make happen.  The attached is just one of the many I use this procedure on and hopefully with your help get this one to work and then I can apply to the others.
Sorry but please keep it simple and explain where to put what because I am a true amature at this.
Thanks
Access-Link-Table.docx
HFSCIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
0
HFSCIAuthor Commented:
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
0
Gustav BrockCIOCommented:
Change you code to use FindFirst.
Syntax is a little different but not difficult. Study the on-line help for examples.

/gustav
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
HFSCIAuthor Commented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
peter57rCommented:
"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.
0
HFSCIAuthor Commented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Give this a go.

Jim.

Private Sub qitemno_AfterUpdate()


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

On Error goto qitemno_AfterUpdate_Error

'
' 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("items")
 
  strPath = tdfAttached.Connect
  strPath = Right$(strPath, Len(strPath) - InStr(strPath, "="))
 
  Set dbRemote = wrk.OpenDatabase(strPath, False, False)

'
'  Find the item
'

cat1 = 0
cat4 = 0
Dim vcat1 As String
Dim vcat2 As String
Dim vcat3 As String
Dim vcat4 As String
Dim vprice As Currency

Set rstItem = dbRemote.OpenRecordset("items", DB_OPEN_TABLE)
rstItem.Index = "item_no"

rstItem.Seek "=", Me![qitemno]
If rstItem.nomatch Then
    MsgBox "Item NOT Found"
Else
qitemdesc = rstItem!Item_Desc
qavgcost = rstItem!avg_cost
Price = rstItem!Price
End If


If rstItem!Price = 0 Then cat4 = rstItem!avg_cost * 6.4 Else cat4 = rstItem!Price

vcat1 = [Cus_no] & [qitemno]
vcat2 = [Cus_no] & rstItem!prod_cat
vcat3 = [cus_type] & [qitemno]
vcat4 = [cus_type] & rstItem!prod_cat

cavg = qavgcost

rstItem.Close

Set rstdisc = dbRemote.OpenRecordset("disc", DB_OPEN_TABLE)
rstdisc.Index = "filler"
rstdisc.Seek "=", vcat4
If rstdisc.nomatch Then
    ccat4 = ""
    Else
    ccat4 = vcat4
End If

rstdisc.Seek "=", vcat3
If rstdisc.nomatch Then
    ccat3 = ""
    Else
    ccat3 = vcat3
End If

rstdisc.Seek "=", vcat2
If rstdisc.nomatch Then
    ccat2 = ""
    Else
    ccat2 = vcat2
End If
   
rstdisc.Seek "=", vcat1
If rstdisc.nomatch Then
    ccat1 = ""
    Else
    ccat1 = vcat1
End If

lubasis.Requery
lupercent.Requery
lupercent2.Requery
lupercent3.Requery
lupercent4.Requery
luqty2.Requery
luqty3.Requery
luqty4.Requery



If [ccat1] = "" And [ccat2] = "" And [ccat3] = "" And [ccat4] = "" And [Price] <> 0 Then
    NoPrice.Visible = True
    Else
    NoPrice.Visible = False
End If
If [luqty2] > 0 Then
    addprice.Visible = True
    Else
    addprice.Visible = False
End If

qitemno_AfterUpdate_Exit:
 
On Error resume next

If not rstItem is nothing then
   rstItem.Close
  Set rstItem = Nothing
End if

If not rstdisc is nothing then
   rstdisc.Close
  Set rstdisc = Nothing
End if

If not dbRemote is nothing then
   dbRemote.Close
  Set dbRemote = Nothing
End if

Set tdfAttached = Nothing
Set dbCurrent = Nothing
Set wrk = Nothing

Exit sub

qitemno_AfterUpdate_Error:
   Msgbox “unexpected error”
   Resume qitemno_AfterUpdate_Exit

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@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.
0
HFSCIAuthor Commented:
Jim - got syntax error see attached
Access-Link-Table-SyntaxErr.docx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The message box line at the bottom needs to be:

   MsgBox "Unexpected Error", vbCritical + vbOKOnly


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

/gustav
0
HFSCIAuthor Commented:
gustav - after seeing the other solution can you show me exactly what you mean by using the above code
0
HFSCIAuthor Commented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
HFSCIAuthor Commented:
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
0
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Gustav BrockCIOCommented:
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
0
HFSCIAuthor Commented:
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
0
HFSCIAuthor Commented:
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
0
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
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
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.