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

[MarvinM]

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:

Dim dbs As DAO.Database 
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:

Set dbs = DBEngine.OpenDatabase("\\Server\MyBackEnd\Data\Task_be.accdb")

This doesn't work. What am I doing wrong? Thanks.

[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.

[zmbd]

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...

'>>>CODE OMITTED:\
on error goto zerrortrap:
'
Set zdb = CurrentDb
'(...)
For Each ztdf In zdb.TableDefs
    If ztdf.Attributes = dbAttachedTable Then
        ztdf.Connect = ";DATABASE=" & zpath & ";TABLE=" & ztdf.SourceTableName
        ztdf.RefreshLink
        If Err.Number > 0 Then Err.Clear
    End If
Next
'(...)
 

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.

[MarvinM]

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.

[zmbd]

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.

[MarvinM]

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:

Dim strRec As String
Dim db As Database
Dim rs As DAO.Recordset
 
strRec = RecNum.Value
 
Set db = CurrentDb
Set rs = db.OpenRecordset("tblStatus")
 
With rs
    .FindFirst "Rec_Num = " & strRec & " And Stat_Cd = 'O'"
    .Edit
    !Current_Ind = False
    .Update
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!

[NeoPa]

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.