ADO Connection question

[AlexanderBB]

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]

you can certainly do it with DAO using Querydefs. for ADO I think you might need ADOX

 

[AlexanderBB]

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

 

[Jon von der Heyden]

Check out the section in here called 'Modifying a Query'.

 

[Firefly2012]

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]

Simple DAO example:

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]

DAO is so yesterday!

 

[RoryA]

No, it really isn't, especially not if you are using Access.

 

[Jon von der Heyden]

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.
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.

 

[AlexanderBB]

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?it.

Sure! This is the creation phase of that 'different SQL statement".