Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

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).
0
Lee W, MVP
Asked:
Lee W, MVP
4 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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)PresidentCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now