Solved

Update Field in Linked Table

Posted on 2014-12-02
12
430 Views
Last Modified: 2014-12-02
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
Comment
Question by:Lee W, MVP
12 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 25 total points
ID: 40476472
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
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40476488
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
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40476492
Try opening up your table as a recordset and updating your field that way.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 475 total points
ID: 40476493
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
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40476530
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
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40476550
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40476553
Anthony,

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

Assisted Solution

by:Nick67
Nick67 earned 475 total points
ID: 40476557
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
 
LVL 95

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 0 total points
ID: 40476604
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40476620
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40476639
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
 
LVL 57
ID: 40476708
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now