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
Solved

Accessing VBA code in Access database via ADOX in VBSCRIPT

Posted on 2014-09-05
3
459 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
  • 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 53

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

839 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