Update Field in Linked Table

I'm in testing and there is no ' in the client data path.  That said, thanks for the reminder to include Replace().  

Just executed the UPDATE statement in SQL - no problems.

Try opening up your table as a recordset and updating your field that way.

Nick67,

Can you elaborate - is the "must" regarding the primary key and timestamp fields a must from the perspective of getting this to work?  And must the field be a timestamp data type or a datetime value?

With rare exception, my tables normally include both a primary key and a datetime "DateEntered" field UNLESS the table is never expected to have more than one row (like the AppConfig table in question - it's a configuration table with only one row and (currently) two fields.

(I can add both, I'd just like to understand why I am adding them).

As for the mystery, it's me being more familiar with data access in ASP rather than in vba/Access.  Your bold code is likely what I will go with - testing it soon!

THANKS!

Nick67,

No Joy.
I incorporated your recommended changes but now I get:

Run-time error '3027';
Cannot update. Database or object is read-only.

    'Used for obtaining ClientDataPath
    Dim ClientDataPath As Variant
    Dim DB As DAO.Database
    Dim DBRS As DAO.Recordset
    Dim SQL
    
    'Get path to store client files
    Set DB = CurrentDb
    SQL = "SELECT ClientDataPath FROM AppConfig"
    
    Set DBRS = DB.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    ClientDataPath = DBRS("ClientDataPath")
    
    'Ensure the configuration has a ClientDataPath entered
    If IsNull(ClientDataPath) = True Then   'No path entered, get one
        ClientDataPath = InputBox("No path has been set to store client files." & vbCrLf & "Enter a root path to store client files below." & vbCrLf & "Files will subsequently be stored in a sub folder named after the location ID.")
        'Ensure the path entered includes a \ at the end.
        If Trim(Right(ClientDataPath, 1)) <> "\" Then ClientDataPath = Trim(ClientDataPath) & "\"
        
        'Check if the folder exists and create if necessary.
        If FolderExists(ClientDataPath) = False Then
            MsgBoxAns = MsgBox("Path does not exist. Create it now?", vbYesNo)
            If MsgBoxAns = 6 Then 'Create the path.
                CreateFolder (ClientDataPath)
            Else 'Fail and cancel out of sub.
                MsgBox "Unable to add file.", vbExclamation + vbOKOnly
                Exit Sub
            End If
        Else 'Update the AppConfig table with the new path.
            'SQL = "UPDATE AppConfig SET ClientDataPath = '" & Replace(ClientDataPath, "'", "''") & "'"
            'MsgBox SQL
            'DoCmd.RunSQL SQL
            
            With DBRS
                .Edit
                !ClientDataPath = ClientDataPath
                .Update
            End With
        End If
    End If

Select allOpen in new window

Anthony,

Can you provide an example?  Mental block for me right now...

And must the field be a timestamp data type or a datetime value?
Timestamp data type
You can Google up:
'You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column'

If you don't use dbSeeChanges when declaring a recordset, you are likely to trip across this error message sooner or later

dbSeeChanges on tables without timestamp fields may or may not throw errors
But, if the table has a PK and you want to update it, you'll inevitably need a timestamp field with a timestamp datatype in it.  And you'll need dbSeeChanges  in the recordset declaration.

The timestamp is just a good idea (in most cases).

Nah, it's pretty much a requirement.
How many read-only tables do you have?  That's the only time it isn't.

Note on the timestamp field:

It's really not a timestamp field (it does not contain a date/time).  What it does is turns on row versioning in a SQL table.

JET when working with ODBC sources, will latch onto that field to determine that it has the correct row.

When you update a record in a recordset, what happens is that JET goes out and re-fetches the record to see if it's been changed by someone else.  The best way it can do that is with the timestamp field.

So a unique key and the timestamp field are basically a must as Nick said as is the dbSeeChanges.

Note that if you can't modify the table, you can define a pseudo index on the JET side on the table to satisfy the unique key requirement.  However without the timestamp field, you'll find that many recordsets are not updateable, or will do so slowly.

 You also want to make sure on your key that your not using any field that will be modified by a trigger or SP.  Also, avoid floats and bit fields.  Last, make sure that any bit fields don't allow null.  

 If you don't do this, you will often see "#Deleted" when scrolling back through a recordset as JET is unable to re-fetch the record (it thinks it has changed).

Jim.