ADO Connection question

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,721
Hi,

I have a connection between Excel and Access. From Excel, I query Access and use the Recordset method to put the data into a sheet.

Can I use this to somehow Update or Edit an actual query that's in Access ?

Please advise yes or no. I think its No !

Thanks, ABB
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,916
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
you can certainly do it with DAO using Querydefs. for ADO I think you might need ADOX
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,721
Thanks rorya. Had a bit of a Google but not found anything concrete yet. Lots of near misses.... confusion over an Update Query and/or updating a query, as in delete and make new (which is what I want),

Has anyone any examples ?

Thanks, ABB
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638

ADVERTISEMENT

Can I ask why you would want to amend a query on the actual database rather than alternatively executing a different SQL statement on the database?

EDIT: Just to be clear, I don't use Access so this could be a completely normal thing to want to do. I'm just interested on the thinking behind it.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,916
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Simple DAO example:
Code:
Sub ChangeQuery()
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strDocsPath As String
    
    strDocsPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
    Set db = DBEngine.OpenDatabase(strDocsPath & "\database1.accdb")
    Set qd = db.QueryDefs("Query1")
    
    qd.Sql = "SELECT Field1 From Table1"
    db.Close
End Sub
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

DAO is so yesterday! :p
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,916
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, it really isn't, especially not if you are using Access.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
No, it really isn't, especially not if you are using Access.

Well I'll have to take your word on it because you are smarter than me. :biggrin:
I haven't used it in ages though. I used ADOX a while ago for stored queries in Access. Quite sure you directed me to the link then too.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,659
Members
418,146
Latest member
Shnn028

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