By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

How do I update a BE table with a UNC path?

P: 3
Access 2007, split database, 9 users.

I have been able to link the BE tables with the Linked Table Manager. I use the UNC path because everyone has different drive mappings.

I can open my linked tables as a RecordSet and AddNew and Update records. However, I cannot Seek and Edit the linked tables. I understand that I need to open the BE tables directly in order to do this. (I have been told this is incorrect, but can't see how. As per the Microsoft Developer Reference, "You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.")
Now, I can do a connection like this, and it works for me:

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database 
  2. Set dbs = DBEngine.OpenDatabase("R:\Data\Task_be.accdb")
But, as I said, everyone has different drive mappings. Therefore, I need to use the Network address instead of a drive letter. I tried this:

Expand|Select|Wrap|Line Numbers
  1. Set dbs = DBEngine.OpenDatabase("\\Server\MyBackEnd\Data\Task_be.accdb")
This doesn't work. What am I doing wrong? Thanks.
Dec 12 '13 #1

✓ answered by NeoPa

May I suggest that it's probably the .Seek() method itself, rather than the table structure and design, that can more easily be dropped and replaced with another approach.

Using .Seek() is not the easiest approach anyway, and only works for local tables anyway. If you use the .Find...() methods instead you should find it a lot more straightforward.

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,186
May I suggest that it's probably the .Seek() method itself, rather than the table structure and design, that can more easily be dropped and replaced with another approach.

Using .Seek() is not the easiest approach anyway, and only works for local tables anyway. If you use the .Find...() methods instead you should find it a lot more straightforward.
Dec 12 '13 #2

zmbd
Expert Mod 5K+
P: 5,287
This will take care of remapping your linked tables:

The following is part of a much larger section of code.
This is part of my "universal" backend reconnect code that I use in my splash-screen/form.
It first checks for the exsistance of the backend in the last known location (simple len(dir()) type check) if that holds true then I test that I can read from the table for version information, if there's a connection error, then I run the following code...

If the LKL of the BE fails, then I open a folder picker that is a variation on a theme as found here: Select a File or Folder using the FileDialog Object build a string with the path and pass it to the following code...

Expand|Select|Wrap|Line Numbers
  1. '>>>CODE OMITTED:\
  2. on error goto zerrortrap:
  3. '
  4. Set zdb = CurrentDb
  5. '(...)
  6. For Each ztdf In zdb.TableDefs
  7.     If ztdf.Attributes = dbAttachedTable Then
  8.         ztdf.Connect = ";DATABASE=" & zpath & ";TABLE=" & ztdf.SourceTableName
  9.         ztdf.RefreshLink
  10.         If Err.Number > 0 Then Err.Clear
  11.     End If
  12. Next
  13. '(...)
  14.  
I have a few error checks involved so that if any of the connections fail a message is generated and the local frontend has a table where all of this is recorded for troubleshooting.

and there you have it.

As for the .seek()... that is a much larger issue... use the .find() instead.
Dec 12 '13 #3

P: 3
Thank you NeoPa. I will give .Find a try. It sounds like the right thing for me.
As for the error checking, zmbd, I have been a foolish novice in this area. I need to add all kinds of checks. Once I get my update code working, I'll put in a splash screen and some basic checking. Thanks for the sage advice.
Dec 12 '13 #4

zmbd
Expert Mod 5K+
P: 5,287
Marvin, sometimes, I don't usually enable my error checks until I'm well into the alpha and often just before I go beta as I often need the compiler to choke at the offending line in order to fix.
Dec 12 '13 #5

P: 3
You guys were right. I first tried using Database.Execute "UPDATE SQL statement string" as suggested on another board. I kept getting errors in my SQL statement. Then I did a test on a small table with Recordset.FindFirst and it works. Here's a snippet:

Expand|Select|Wrap|Line Numbers
  1. Dim strRec As String
  2. Dim db As Database
  3. Dim rs As DAO.Recordset
  4.  
  5. strRec = RecNum.Value
  6.  
  7. Set db = CurrentDb
  8. Set rs = db.OpenRecordset("tblStatus")
  9.  
  10. With rs
  11.     .FindFirst "Rec_Num = " & strRec & " And Stat_Cd = 'O'"
  12.     .Edit
  13.     !Current_Ind = False
  14.     .Update
  15. End With
This finds one record with a Status Code of 'O' and turns off the Current Indicator flag. What I want to do next is change that condition to <>'A' where it may have more than one record to change. Then between .Edit and .Update, I'll put in a Do While loop that will .FindNext and change the flag for every record. (This is not a mass operation. If there is ever more than one to change it will only be 2 or 3. I just want to have a catch all.)

So to wrap up this thread for anyone who finds it in the future... The solution to the initial question is NOT to do the update through the UNC path as I was trying to do, but to re-examine the way that the update is done. It is possible to make updates by way of a recordset to a linked table. Use the FindFirst method instead of Seek.

Thank you, Experts!
Dec 12 '13 #6

NeoPa
Expert Mod 15k+
P: 31,186
Very pleased to hear that everything worked out with a little helpful advice. Z's comments about error catching is also well worth following. It's very easy to miss logic errors if you trap the code errors.
Dec 13 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.