Accessing VBA code in Access database via ADOX in VBSCRIPT

So, I have been able to access several object types in an Access database via ADOX from VBSCRIPT.  My question is, there are also some VBA functions and subroutines in the Access database, and I would like to be able to access them as well.  Is this possible with ADOX from VBSCRIPT, and if so can someone pass along an example?

Here's the basic test code I used to access some of the objects.

Set adox = CreateObject("ADOX.Catalog") 
Set Conn = CreateObject("ADODB.Connection") 
Conn.open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=drops.accdb"
adox.activeConnection = Conn 

For Each t In adox.Tables
  Wscript.Echo "TABLE," & t.Name & "," & t.Type
  For Each c in t.columns 
	 Wscript.Echo "COLUMN," & c.Name & "," & c.Type
  Next 
Next

For Each p in adox.Procedures	
  Wscript.Echo "PROC," & p.Name & "," & Replace(p.Command.CommandText,chr(13),chr(13))
Next

For Each v in adox.Views
  Wscript.Echo "VIEW," & v.Name & "," & Replace(v.Command.CommandText,chr(13),chr(13))
Next

Conn.close
Set Conn = nothing 
Set adox = nothing 

Open in new window

~bp
LVL 62
Bill PrewAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not that I'm aware of. ADOX deals mainly with the database, not UI or code.

You could use DAO to get the names of the modules:
    Dim dbs    As DAO.Database
    Set dbs = DAO.OpenDatabase("Path to your Database")
    Dim cnt    As DAO.Container
    Set cnt = dbs.Containers("Modules")

    Dim doc    As DAO.Document
    For Each doc In cnt.Documents
        Debug.Print doc.Name
    Next doc

Open in new window

0
Bill PrewAuthor Commented:
Thanks Scott, I'll have to keep researching.  I really need to get at the actual VBA code and update it. I have hundreds of Access databases that I need to make a small modification to one of the common functions.

~bp
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm almost certain you can't do that with ADOX. I believe the only way would be through the Access.Application object, in the Modules collection. That collection contains all of Modules in the app, and you can cycle through them and make changes. The Module object exposes all of the necessary functionality - like Find, InsertLine, etc etc. Basically, you'd use it like this:

Dim acc As New Access.Application
acc.OpenCurrentDatabase("Path to your db")
Dim mdl as Access.Module
Set mdl = acc.Modules("ModuleName")

From there, you could work with the module via the various properties and methods of the Module object.

I assume that you could do this in VBScript, using the CreateObject syntax. I'd certainly do the development in Access, and then move it over to VBScript as needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.