Q:

excel vba writing to specific field and value in Access

I am building an excel application that is able to list out tools based on a user's input. From this input, they are able to click on tool (the tools in this case are different excel files) which then opens the new tool. Since I am running this app on a remote server and many people will have access to these tools, I would like to create a update the field isLocked in the Access database which contains all the tools information (other fields for the tool include name, path, project number). So my question is, how can I look at the tool that user opened and then go into the access database and change the field isLocked to YES? I think I'm ultimately looking for a specific type of SQL statement...
Thanks for your help.

A:

I'm assuming you can add this to the code which open the Excel file "tool" the user selects.  Other assumptions include: tblExcelTools as the name of the table; a numeric primary key field, tool_id; a combo box control named cboToolChooser and it's bound field is tool_id.
Dim strSql AS String
strSql = "UPDATE tblExcelTools" & vbCrLf & _
    "SET isLocked = -1" & vbCrLf & _
    "WHERE tool_id = " & Me.cboToolChooser
Debug.Print strSql ' look in Immediate window (Ctrl+g) if problems '
CurrentDb.Execute strSql, dbFailonerror

You should add an error handler to deal with any problems dbFailonerror turns up.
I think something roughly similar to that could work to satisfy your immediate goal --- set isLocked to -1 (True) when the user opens the tool.  However, I got no clue about how you're gonna set isLocked back to False when the user has finished with the tool.  
Edit: I suggested that code sample thinking you would run it from Access, but you're doing this from Excel.  I don't use Excel with user form stuff.  Maybe that code could be simply adapted for ADO.  Open your ADO connection to the Access db, then use: cn.Execute strSql

