CODEWITHSUNDEEP
vb.netms-access
user1943219
user1943219

Reputation: 396

Using VB.NET to obtain macros in MS Access

I'm trying to create a VB.NET button which will connect to an MS Access database and obtain a list of all the macros stored in the access database.

After I have the list I want to display the values in a combobox. I've found something online similar to what i'm trying to do but I cannot find anything which will just give me a list of macros.

Dim userTables As DataTable = Nothing
Dim connection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
Dim source As String
source = TextDBPath.Text
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + source
    Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
connection.Open()
' Get list of user tables
userTables = connection.GetSchema("Tables", restrictions)
connection.Close()
' Add list of table names to listBox
Dim i As Integer
For i = 0 To userTables.Rows.Count - 1 Step i + 1
    cbox.items.add(userTables.Rows(i)(2).ToString())
Next

In the program i'm working on, this was obtained in VB6 by using .Containers("Scripts") with DAO.Database. Any help would be appreciated!

Upvotes: 2

Views: 691

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123839

At first I thought this was going to be a simple matter of executing the query

SELECT [Name] FROM MSysObjects WHERE Type = -32766

but that fails with "no read permission on MSysObjects". OLEDB seems to have access to the system tables locked down pretty tight. I even tried doing a CREATE VIEW listMacros AS SELECT [Name] FROM MSysObjects... followed by a SELECT * FROM listMacros. The VIEW (Query) was created successfully but OLEDB still told me that I wasn't allowed to read [MSysObjects].

Bummer.

However, I did some fooling around with VBA code in Excel and I got this to work...

Sub listAccessMacros()
Dim objAccess As Object  '' Access.Application
Dim i As Long
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Documents and Settings\Administrator\Desktop\Database1.accdb"
For i = 0 To objAccess.CurrentProject.AllMacros.Count - 1
    Debug.Print objAccess.CurrentProject.AllMacros(i).Name
Next
objAccess.CloseCurrentDatabase
objAccess.Quit
Set objAccess = Nothing
End Sub

...so perhaps you can come up with a way to tweak it so it will work in VB.NET. (Sorry, but I don't have ready access to VB.NET myself....)

Upvotes: 3

Related Questions