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
'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.
Else 'Fail and cancel out of sub.
MsgBox "Unable to add file.", vbExclamation + vbOKOnly
Else 'Update the AppConfig table with the new path.
DoCmd.RunSQL "UPDATE AppConfig SET ClientDataPath = '" & ClientDataPath & "'"
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).