BluffMeAllIn
New member
- Local time
- Today, 17:39
- Joined
- Dec 2, 2013
- Messages
- 5
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:
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
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:
Code:
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