Error on adocon.OpenShcema

[BluffMeAllIn]

Hello All,

I am starting to work on a new project in my position and figured it would be a great time for me to take advantage of learning and building my use of ADO programming. I came accross it alot in my previous position with a .net web application but didn't get much a chance outside of support to develop using it.

I had never used it in Access programming through VBA but figured it would be a good time to learn the ins and outs. Currently this is just something to give me a feel for it as still working on requirements and determining data to capture for the users in the project.

In short right now looking to capture the table names from the database and store them in a table, anyone familiar with the VBA I'm sure will see what I am trying to do:

Option Compare Database

Private adocon As Object
Private strConnString As String

Public Sub tblTableAlias_Refresh()
On Error GoTo TARef_Err
    'Dim tdf As TableDef
    'Dim dbs As DAO.Database
    'Dim strTblName As String
    Dim strSQL As String
    'Dim tblExist As Boolean

    '*NOTE: Will change later once split database
    'Set dbs = CurrentDb

    Dim rsSchema As Object, rsTableAlias As Object
    Set rsSchema = CreateObject("ADODB.Recordset")

    If adocon Is Nothing Then
        CreateAnonymousConnection
    End If

    rsSchema = adocon.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

    With rsSchema
        .MoveFirst
        Do While Not .EOF
            If .Fields("TABLE_TYPE") = "TABLE" Then
                If Left(UCase(.Fields("TABLE_NAME")), 8) <> "SYSADMIN" Then
                    strSQL = "Select * From sysadmin_tblTableAlias Where TableName = '" & .Fields("TABLE_NAME") & "'"
                    Call OpenRecordSet(rsTableAlias, strSQL)
                    If rsTableAlias.EOF Then
                        rsTableAlias.AddNew
                        rsTableAlias.Fields("TableName") = rsSchema.Fields("TABLE_NAME")
                        rsTableAlias.Fields("Alias") = ""
                        rsTableAlias.Update
                    End If
                    Set rsTableAlias = Nothing
                End If
            End If
        Loop
        .MoveNext
    End With
TARef_Exit:
    Exit Sub

TARef_Err:
    MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & "From: TableAlias_Refresh " _
          & vbCrLf & vbCrLf & "Description: " & Err.Description, vbInformation, CurrentDb.Properties("strTitle")
    Resume TARef_Exit
End Sub


Private Sub OpenRecordSet(ByRef rs, ByRef sql)
On Error GoTo openrs_err
    If adocon = Nothing Then
        CreateAnonymousConnection
    End If
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql
openrs_exit:
    Exit Sub
openrs_err:
    MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & "From: TableAlias_Refresh " _
          & vbCrLf & vbCrLf & "Description: " & Err.Description, vbInformation, CurrentDb.Properties("strTitle")
    Resume openrs_exit
End Sub


Private Sub CreateAnonymousConnection()
On Error GoTo cac_err
    Set adocon = CreateObject("ADODB.Connection")
    'strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & [CurrentProject].[Path] & "\" & CurrentDb.Name
    'strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name
    With adocon
        '.Provider = "Microsoft.ACE.OLEDB.12.0"
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        '.Open [CurrentProject].[Path] & "\GN JWSOHS Tracking_be.accdb"
        .Open [CurrentProject].[Path] & "\GN JWSOHS Tracking_be.mdb"
    'adocon.Open strConnString
    End With
cac_exit:
    Exit Sub
cac_err:
    MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & "From: TableAlias_Refresh " _
          & vbCrLf & vbCrLf & "Description: " & Err.Description, vbInformation, CurrentDb.Properties("strTitle")
    Resume cac_exit
End Sub

I am getting the MVB Error on the rsSchema = adocon.OpenSchema(....):
Run-time error '3251':

Object or provider is not capable of performing requested operation.


I have tried both the ACE and Jet providers (saving a copy of my backend database in the mdb format to test the Jet provider.

Any assistance would be greatly appreciated, I could have an error that sticks out like a sore thumb but with some searching I have done this openschema would be the way to use ado to get the list of table names. I have previously done something similar using DAO to go through the tabledefs etc. but am looking to go through this project using ADO completely if at all possible. Reason 1 for this I want the experience, and Reason 2 is that this is essentially an initial project that could eventually move to a web application and I think having it coded using ADO now would make it more easily portable over to a .net web application.

I did notice someone having a thread back in 2009 with pretty much the exact same error trying to do the openschema but seems they didn't really get any input regarding, I certainly hope to get some.

Thanks,
Dave

[ItsMe]

I don't use ado much. It seems you may be having an issue with you connection object. What does that code look like? You should probably be using ADODB.Connection and ADODB.Recordset

So, I would recommend taking a look at what CreateAnonymousConnection is doing

[BluffMeAllIn]

Essentially per the code (I haved copied it again below removing the old line comments and resetting back to the ACE provider) the CreateAnonymousConnection is simply creating a connection object into the backend database:

Private Sub CreateAnonymousConnection()
On Error GoTo cac_err

    Set adocon = CreateObject("ADODB.Connection")
    With adocon
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open [CurrentProject].[Path] & "\GN JWSOHS Tracking_be.accdb"
    End With

cac_exit:
    Exit Sub

cac_err:
    MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & "From: TableAlias_Refresh " _
          & vbCrLf & vbCrLf & "Description: " & Err.Description, vbInformation, CurrentDb.Properties("strTitle")
    Resume cac_exit
End Sub

The connection object is being created and the backend being opened, i can see via the lock file that gets created but when it goes to do the OpenSchema its throwing the error, yet this is the method I found per all researching to access the objects of the database to in this case loop this and pull the table names.

I have done this before from a DAO standpoint using linked tables and tabledefs etc but as had indicated in OP am trying to do this project without linking the tables but instead completely through ADO so it will be easier to port into a .net application down the road.

[ItsMe]

Sorry about that. I did not scroll down enough and did not see the procedure. So the error code and the fact it was bugging where it did raised suspicion.

What about OpenSchema? Does it show in intelisense? Is there a way you can see if that and adSchemaTables is in the scope of the object? I am just putting ideas out there. Not sure why it does not like that line. So maybe revisit the DB engine. Maybe double check the version. Look in the available reference list/library.

[BluffMeAllIn]

Thanks for jogging my memory ItsMe, I figured my intelisense just wasn't working correctly because the ADODB.Connection was being created in my createobject but turns out my problem seems to have been that I was missing references in my project for the ADO controls. I have these now added and the intellisense is working and I can even skip creating a generic object and create the adodb objects themselves and even set them using the new keyword. Much appreciate the assistance, sometimes it tends to be the simplest things that are thus so easy to overlook and cause the greatest pains lol.

Dave

[ItsMe]

...... I can even skip creating a generic object and create the adodb objects themselves and even set them using the new keyword.

Dave

Glad to hear it is working for you now. After I posted I was looking at the creation of the object too. It seems as though you have it working using early binding now. You may want to keep an eye on this in case you need to go back to late binding. I think this may be why your code was not working before. It was trying to late bind to a version that it did not recognize. This version was defined in your sub procedure.

I am not expert in late binding but post up if you get stuck again. Good Luck