Dao - Ado Modify Defined Query

[vandewinkle]

My ERP software is dictating that I use ADO connections. I have been able to convert all my code except when I need to modify a querydef. In DAO it was like this:

Sub ReplaceQuery()

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
dbs.QueryDefs.Refresh

For Each qdf In dbs.QueryDefs
If qdf.Name = "qryMyQuery" Then dbs.QueryDefs.Delete qdf.Name
Next qdf

strSQL = "SELECT * FROM MYDATA;"

Set qdf = dbs.CreateQueryDef("qryMyQuery", strSQL)

DoCmd.OpenQuery qdf.Name, acViewNormal, acReadOnly

Set dbs = Nothing

End Sub

I have simplified my sql statment for demo purposes only, but I must be able to modify a current query def in access 2000 using vba.

I have looked everywhere for weeks with no solution. If anyone can point me in the right direction, I would very much appreciate it.

[ADezii]

My ERP software is dictating that I use ADO connections. I have been able to convert all my code except when I need to modify a querydef. In DAO it was like this:

Sub ReplaceQuery()

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
dbs.QueryDefs.Refresh

For Each qdf In dbs.QueryDefs
If qdf.Name = "qryMyQuery" Then dbs.QueryDefs.Delete qdf.Name
Next qdf

strSQL = "SELECT * FROM MYDATA;"

Set qdf = dbs.CreateQueryDef("qryMyQuery", strSQL)

DoCmd.OpenQuery qdf.Name, acViewNormal, acReadOnly

Set dbs = Nothing

End Sub

I have simplified my sql statment for demo purposes only, but I must be able to modify a current query def in access 2000 using vba.

I have looked everywhere for weeks with no solution. If anyone can point me in the right direction, I would very much appreciate it.

You can avoid any References to DAO or ADO Object Libraries simply by creating a Base Query consisting of all possible Fields, then modifying the SQL Property of the Query then Opening it. I'm not sure if this will suit your purposes, but hopefully it was worth mentioning. A provided a simple code segment for you to view:

Dim strNewSQL As String
 
strNewSQL = "Select [FirstName], [LastName], [Title] From Employees;"
 
CurrentDb.QueryDefs("qryEmployees").SQL = strNewSQL
 
DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly
 

[vandewinkle]

You can avoid any References to DAO or ADO Object Libraries simply by creating a Base Query consisting of all possible Fields, then modifying the SQL Property of the Query then Opening it. I'm not sure if this will suit your purposes, but hopefully it was worth mentioning. A provided a simple code segment for you to view:

Dim strNewSQL As String
 
strNewSQL = "Select [FirstName], [LastName], [Title] From Employees;"
 
CurrentDb.QueryDefs("qryEmployees").SQL = strNewSQL
 
DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly
 

It was very much worth mentioning. My sincere thanks to you. I overlooked the simple solution. Shame on me. Again, thank you.