ADO Connection question

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,594
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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,594
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
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
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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,693
Office Version
365
Platform
Windows
DAO is so yesterday! :p
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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,693
Office Version
365
Platform
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.
 

Forum statistics

Threads
1,081,526
Messages
5,359,280
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top