Turn on System Objects visible in Access using Excel VBA 1

[SarahMH]

Can anyone help please?

I am trying to populate a combo box within excel that lists the tables in an access database(the selected table name goes into a variable that is contained in a SQL string). I need to have the tools/options/"system objects" option button selected in Access database for the query to run. I would rather hard code this than the end user having access/visability of these objects.

Does anyone know the syntax to toggle on and off the 'system objects' option using vba in excel? or is this impossible?

Thanks for your time

 

[jksmi]

Hi, you need to add access as a reference to get the code below to work - also you could save the current setting and reset before you close...

Function SetAccessVis() As Boolean
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

testVis = appAccess.GetOption("Show Hidden Objects")

appAccess.Quit
End Function

Hope this helps, Jamie