Solved

Using Seek on linked table

Posted on 2012-03-13
25
490 Views
Last Modified: 2012-03-14
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
0
Comment
Question by:HFSCI
  • 10
  • 9
  • 3
  • +2
25 Comments
 
LVL 57
ID: 37714765
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
 

Author Comment

by:HFSCI
ID: 37715037
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37715221
Change you code to use FindFirst.
Syntax is a little different but not difficult. Study the on-line help for examples.

/gustav
0
 
LVL 57
ID: 37715323
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
 

Author Comment

by:HFSCI
ID: 37715406
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
 
LVL 57
ID: 37715527
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37715771
"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
 

Author Comment

by:HFSCI
ID: 37715989
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 37717090
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
 
LVL 57
ID: 37717363
@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
 

Author Comment

by:HFSCI
ID: 37719681
Jim - got syntax error see attached
Access-Link-Table-SyntaxErr.docx
0
 
LVL 57
ID: 37719752
The message box line at the bottom needs to be:

   MsgBox "Unexpected Error", vbCritical + vbOKOnly


Jim.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37719781
Well, the lazy dog would use FindFirst and skip all the direct-backend stuff which rarely is needed.

/gustav
0
 

Author Comment

by:HFSCI
ID: 37719852
gustav - after seeing the other solution can you show me exactly what you mean by using the above code
0
 

Author Comment

by:HFSCI
ID: 37719917
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
 
LVL 57
ID: 37719944
<<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
 
LVL 57
ID: 37719980
<<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
 

Author Comment

by:HFSCI
ID: 37720141
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
 
LVL 9
ID: 37720276
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
 
LVL 57
ID: 37720373
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37720452
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
 

Author Comment

by:HFSCI
ID: 37720512
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
 

Author Comment

by:HFSCI
ID: 37720678
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
 
LVL 9
ID: 37721093
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
 
LVL 57
ID: 37721197
<<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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now