Hey blip,
Since I'm not a C++ programmer, I'm a little limited there, but I'm a VBA programmer within MS Access.
One of the MAIN rules that you are violating here is that when you open a query, by default it is in a datasheet view within MS Access. there is an method within DAO library that is called CreateQueryDef(). The method take two parameters, 1) Name of the query you're creating, 2) MS Access SQL Statement. (Please note that the first parameter needs to be a name that is unique within the MS Access Database.)
This is where you are getting your error from.
I have a Function that is written in VBA that checks the existance of all MS Access Object types. It takes two parameters. 1) Object type as String. 2) Object Name as String.
Code below:
Code:
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
Dim db As Database
Dim tbl As TableDef
Dim qry As QueryDef
Dim i As Integer
Set db = CurrentDb()
ObjectExists = False
If strObjectType = "Table" Then
For Each tbl In db.TableDefs
If tbl.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next tbl
ElseIf strObjectType = "Query" Then
For Each qry In db.QueryDefs
If qry.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next qry
ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next i
ElseIf strObjectType = "Macro" Then
For i = 0 To db.Containers("Scripts").Documents.Count - 1
If db.Containers("Scripts").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next i
Else
MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
End If
End Function
Then you would need to use this function when creating your modified Query. Code example below:
Code:
Dim MyDB as DAO.Database
set MyDB = CurrentDB()
If ObjectExists("Query", "QUERYNAME") Then
DoCmd.DeleteObject acQuery, "QUERYNAME"
End if
strSQL = "<<MS Access SQL Select Statement Go Here>>"
Set qry = MyDB.CreateQueryDef("<<QueryName Go Here>>", strSQL)
Hope this helps, If this is solved, then mark it as solved.
Thanks,
Joe P.