Update Access table through Data Connection

srcera

Board Regular
Joined
May 22, 2006
Messages
95
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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MikeDBMan

Well-known Member
Joined
Nov 10, 2010
Messages
589
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"

Code:
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

Board Regular
Joined
May 22, 2006
Messages
95
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top