• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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
0
Bill Prew
Asked:
Bill Prew
  • 2
1 Solution
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now