Update Access table through Data Connection

[srcera]

Good Day,

I have a data connection to an Access database that is working great but I would like to be able to update one field in the table. The field is a simple Yes/No and will prevent the query from pulling those records again.

Is it possible to do that through the connection or do I need to look into another way?

I am using Excel 2007 and Access 2003.

Thanks in advance!

 

[MikeDBMan]

Try this. Not sure if it is correct for the versions you are referring to. But worth a quick try. I am updating the field "ProcessingStatus" in the table "TheTable" in the database that is stored in the range "TheFile" which is in the path stored in the named range "ThePath"

Let DelTxt = "UPDATE " & TheTable & " SET ProcessingStatus = '" & TheTxt & "' WHERE [Project ID] = '" & ProjectID & "'"
Application.ScreenUpdating = False
Sheets("intParam").Select
Range("thePath").Select
Let ThePath = ActiveCell.Value
Range("theFile").Select
Let TheFile = ActiveCell.Value
Let ProvTxt = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThePath & Application.PathSeparator & TheFile    '& ";Extended Properties=Excel 12.0 Xml;HDR=YES;"
Set cnn = New ADODB.Connection
With cnn
    .Open ProvTxt
    .Execute DelTxt, lngRecsAffected, adCmdText  'third argument specifies SQl string being passed in - not strictly necessary
End With
cnn.Close

 

[srcera]

I've tried the two methods below but I get an "Operation must use an updateable query" error for both of them.

Method 1:
Set acApp = New ADODB.Connection

acApp.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDatabaseName '& ";Extended Properties=Excel 12.0 Xml;HDR=YES;"
acApp.Execute sSQL
acApp.Close

Set acApp = Nothing

Method 2:
Set acApp = CreateObject("Access.Application")

acApp.Visible = True
acApp.OpenCurrentDatabase (sDatabaseName)
acApp.DoCmd.RunSQL sSQL

Set acApp = Nothing

When I copy sSQL into a new query in Access, it works fine. Any suggestions?

Thanks