Update Field in Linked Table

My database is stored on a SQL server and has a Config table (AppConfig).  The access front end runs from a network share.  I want to store a path for files in afield that table.
I need to check (via VBA code) if that field is null and if so, prompt to enter a path.  I have this.
I then need to store that path back in the config table so that you're not prompted every time.
When I attempt to update the AppConfig table (currently using a DoCmd.RunSQL "UPDATE..." , I get an error:
Run-time error '3073':
Operation must use an updateable query.


Code is as follows:
    '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)
    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.
            DoCmd.RunSQL "UPDATE AppConfig SET ClientDataPath = '" & ClientDataPath & "'"
        End If
    End If

Open in new window


(CreateFolder and FolderExists are custom functions not included above)

(I'm open to other methods of updating this outside of the DoCmd.RunSQL option... just not thinking of them at the moment/don't know them, so your suggestions are welcome and appreciated).
LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you tried running it as a Query (as opposed to in VBA code)? Do you still have the same problem?

If not, then save it as a Query and run the query instead?

If you do still have a problem, then the problem is not your code - maybe it's security or read-only or something else. Maybe the ClientDataPath has an ' in it.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
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.
0
Anthony BerenguelCommented:
Try opening up your table as a recordset and updating your field that way.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
My database is stored on a SQL server
Then you MUST
1. have a primary key in the table
2. have a timestamp field in the table

Why you are using SQL to update when you have the recordset on the go already is a mystery.
Set DBRS = DB.OpenRecordset(SQL, dbOpenDynaset)
Add in the required dbSeeChanges and use it!
Set DBRS = DB.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
With DBRS
    .Edit
     !ClientDataPath = ClientDataPath
    .Update
End With
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
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
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!
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
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

Open in new window

0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Anthony,

Can you provide an example?  Mental block for me right now...
0
Nick67Commented:
Can you elaborate - is the "must" regarding the primary key and timestamp fields a must from the perspective of getting this to work?

This one is easy to test.
Create a table in SQL Server without a primary key.
Link to it in Access
IF Access asks you to define a unique field (which it may do if there is no PK) DO NOT do so.
Complete the linking
Try to update fields in that table.
You will discover they are read-only.
Access will refuse to update a linked table that Access does not have a unique index defined for.
Try deleting the AppConfig table if it is a linked table, and recreate it in Access either after you have added a Primary key, or told Access what field to use as a proxy for a PK
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
So the primary key is the important one.  The timestamp is just a good idea (in most cases).

It worked when I added a primary key and updated the linked table.

Thanks!
0
Nick67Commented:
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
0
Nick67Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
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 Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.