I am trying to capture (in a drop down box) the list of tables contained within a database. I want the user to select one - that name is passed to a variable and used in SQL to return data within excel.
The error I am getting is: Record(s) cannot be read, no read permission on MSysObjects.
Sub SetAccessVis()
Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.application.9")
If Not appAccess.GetOption("show hidden objects") Then
appAccess.SetOption "Show Hidden Objects", True
End If
appAccess.Quit
End Sub
The main chunk of code(except error handler) is as follows:-
Sub DataDropdownTblNames()
Dim cnt As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim stCon As String
Dim vaNo As Variant
Dim SqlStr, SqlStr1, SqlStr2 As String
Dim TblName As String
Dim Quotes, CLb, CRb As String
Dim fld As ADODB.Field
On Error GoTo Error_Handling
CLb = Chr$(91)
CRb = Chr$(93)
Quotes = Chr$(34)
SetAccessVis 'call above procedure
'SQL string that calls the data from the table selected by dropdown box
SqlStr = "SELECT MSysObjects.Name "
SqlStr = SqlStr & "FROM MSysObjects "
SqlStr = SqlStr & "WHERE (((MSysObjects.Name) Like " & Quotes & "ipdcwait at *final" & Quotes & "Or (MSysObjects.Name) Like " & Quotes & "OPWAIT * final" & Quotes & ") AND ((MSysObjects.Type)=1));"
Set cnt = New ADODB.Connection
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=G:\Finance and IM&T\IM&T\Information Dept\General Data Area\MONITOR\ACCESS\Access from April 2002\Waiting Lists.mdb;" _
& "Persist Security Info=true"
cnt.ConnectionString = stCon
cnt.Open
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnt
.CommandText = SqlStr
.CommandType = adCmdText
End With
Set rst = cmd.Execute
rst.Open (cmd)
rst.Open (SqlStr)
Do While Not rst.EOF
ComboBox1.AddItem rst
Loop
I barking up the wrong tree? The reason I wanted to used this dropdown within excel was to eliminate the user from having to open Access. Can anyone help/explain what I am doing wrong?
Many thanks
The error I am getting is: Record(s) cannot be read, no read permission on MSysObjects.
Sub SetAccessVis()
Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.application.9")
If Not appAccess.GetOption("show hidden objects") Then
appAccess.SetOption "Show Hidden Objects", True
End If
appAccess.Quit
End Sub
The main chunk of code(except error handler) is as follows:-
Sub DataDropdownTblNames()
Dim cnt As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim stCon As String
Dim vaNo As Variant
Dim SqlStr, SqlStr1, SqlStr2 As String
Dim TblName As String
Dim Quotes, CLb, CRb As String
Dim fld As ADODB.Field
On Error GoTo Error_Handling
CLb = Chr$(91)
CRb = Chr$(93)
Quotes = Chr$(34)
SetAccessVis 'call above procedure
'SQL string that calls the data from the table selected by dropdown box
SqlStr = "SELECT MSysObjects.Name "
SqlStr = SqlStr & "FROM MSysObjects "
SqlStr = SqlStr & "WHERE (((MSysObjects.Name) Like " & Quotes & "ipdcwait at *final" & Quotes & "Or (MSysObjects.Name) Like " & Quotes & "OPWAIT * final" & Quotes & ") AND ((MSysObjects.Type)=1));"
Set cnt = New ADODB.Connection
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=G:\Finance and IM&T\IM&T\Information Dept\General Data Area\MONITOR\ACCESS\Access from April 2002\Waiting Lists.mdb;" _
& "Persist Security Info=true"
cnt.ConnectionString = stCon
cnt.Open
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnt
.CommandText = SqlStr
.CommandType = adCmdText
End With
Set rst = cmd.Execute
rst.Open (cmd)
rst.Open (SqlStr)
Do While Not rst.EOF
ComboBox1.AddItem rst
Loop
I barking up the wrong tree? The reason I wanted to used this dropdown within excel was to eliminate the user from having to open Access. Can anyone help/explain what I am doing wrong?
Many thanks
