Error on adocon.OpenSchema (1 Viewer)

[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.

Thanks,
bluff

 

[BluffMeAllIn]

just a bump on the message as has had a few views throughout the day but hoping someone out there would have some ideas as to what my issue might be.

 

[BluffMeAllIn]

hmmmm still nothing, I have come accross this similar issue on probably three different forums and in all cases no suggestions or assistance seemed to have been provided.Is there no one out there with any thoughts or ideas on why the openschema does not appear to be working correctly, I know I am relatively new but has to be someone on the forum that has something of value that I could look at. Perhaps there are some specific learning resources for using ADO in access that would be of assistance, I am really hoping to do this from an ADO aspect but as it seems perhaps little assistance exists per lack of response I might just go back to DAO and have to recode the bloody thing when it comes time to migrate into a web application.Anyone?????????????

 

[BluffMeAllIn]

ok, well joined up to another forum since hadn't gotton any response at all here and got response enough there to jog my noggon a little and check a couple of simple items I seemed to have overlooked.

basically i was missing a reference. Take it easy all.