?
Solved

Accessing VBA code in Access database via ADOX in VBSCRIPT

Posted on 2014-09-05
3
Medium Priority
?
473 Views
Last Modified: 2014-10-26
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
Comment
Question by:Bill Prew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 85
ID: 40307443
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
 
LVL 56

Author Comment

by:Bill Prew
ID: 40307734
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40307763
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question