I really need help.

I do have an Access application that that I use to create reports as well as graphs. But bofore the graphs or reports are created, I have wo populate a lot of qury populating as in the example below.

For example:

Private Function PopulateQry(theContract As String)

Dim txtSQL As String
Dim strSQL As String
'Dim rst As ADODB.Recordset
'Dim booATSGenericQueryExist As Boolean

'Set a reference for Microsoft ADO Ext. 2.1
'for DDL and security.
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command

On Error GoTo PopulateQryErr

' Test the sql statement first
'boolSQLGood = IsQueryGood
'If boolSQLGood Then

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection


' contral to logging Invoices.
strSQL = "SELECT ContractInvoices.ContractNumber, ContractInvoices.BillPeriodEnd, " & _
"Sum(ContractInvoices.InvoiceValue) AS SumOfInvoiceValue " & _
"FROM contractInvoices GROUP BY ContractInvoices.ContractNumber, " & _
"ContractInvoices.BillPeriodEnd HAVING " & _
"(((ContractInvoices.ContractNumber)='" & theContract & "'));"

If TypeOfQuery("QryContractInvoices") = "View" Then
Set cmd = cat.Views("QryContractInvoices").Command
cmd.CommandText = strSQL
Set cat.Views("QryContractInvoices").Command = cmd
cat.Views.Refresh

ElseIf TypeOfQuery("QryContractInvoices") = "Procedure" Then
Set cmd = cat.Procedures("QryContractInvoices").Command
cmd.CommandText = strSQL
Set cat.Procedures("QryContractInvoices").Command = cmd
cat.Procedures.Refresh

End If

Set cat = Nothing
Set cmd = Nothing

'End If

Exit_This:
On Error GoTo 0
Exit Function

PopulateQryErr:

gblPrintMessage = "Error Number = " & Err.Number & " - " & Err.Description & "."
LogErrorMessage "PopulateQry()", gblPrintMessage, 3

Set cat = Nothing
Set cmd = Nothing

Resume Exit_This

End Function

When I run the application on my development machine and some other macines, the applicatiion run well. But on some machines, I do run into problems especially where I have to use ADOX. The error is

Error: -2147467262 No such interface supported

Is it an MDAC issue, an JET issue, or waht is it?

Can someone help me please; I have run out of options.

Wango

Here is a module I use. I have not tested it on a target machine yet, however, I am confident it will work as advertised.




Sub myCreateQuery(strSQL As String, strQueryName As String)
'Test JIT Query creation
'The idea is to create the query for only as long as we need it
'Then delete it on closing the form

Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strTrimmed As String
Set cat = New ADOX.Catalog

'Open the catalog
cat.ActiveConnection = CurrentProject.Connection

Set cmd = New ADODB.Command

strTrimmed = Len(strSQL)

strTrimmed = Left(strSQL, (strTrimmed - 1))

Debug.Print strTrimmed

strTrimmed = strTrimmed & " With OwnerAccess Option;"

cmd.CommandText = strTrimmed

cat.Views.Append strQueryName, cmd

Set cat = Nothing


End Sub
Sub DestroyQuery(strQueryName As String)
'On close we want to destroy the query

On Error GoTo ErrorHandler_destroyQuery

Dim cat As ADOX.Catalog


Set cat = New ADOX.Catalog

cat.ActiveConnection = CurrentProject.Connection

cat.Views.Delete (strQueryName)

cat.Procedures.Delete (strQueryName)

Set cat = Nothing

Exit Sub

ErrorHandler_destroyQuery:

MsgBox Err.Number

If Err.Number = 3265 Then

Resume Next

End If



End Sub