Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: -2147467262, "No such Interface supported" & ADOX

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •