Solved

Accessing VBA code in Access database via ADOX in VBSCRIPT

Posted on 2014-09-05
3
460 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 84
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 54

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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 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