0

I like to index the content (tables) and the code (VBA, view queries) of several MS Access files, preferably on a machine that doesn't have MS Access installed. I used OleDb.OleDbConnection to get the names and content of all tables.

Is it possible to use OleDb.OleDbConnection to get the content of all VBA modules and view queries inside the MS Access database as well? I know something like that is possible in SQL server since all stored procedures or views are stored in some system tables, but I'm not sure about MS Access.

Nostromo
  • 1,177
  • 10
  • 28
  • Which version of Access? (ie., is it an `.accdb` or `.mdb`?) also is this [tag:vba] (like you question's title) or [tag:.net] (like it's tagged)? can you show your code? – ashleedawg Jul 14 '18 at 10:24
  • Preferrably both versions of Access (accdb and mdb), I used the ODBC provider for the new Access version, which is working for both versions. – Nostromo Jul 14 '18 at 12:18
  • And my program (the indexer) is written in .NET but it's supposed to extract the VBA modules from the access files. – Nostromo Jul 14 '18 at 12:19

2 Answers2

1

You need the access "application" object to extract things like code modules etc.

Just connecting with the database engine (which is separate from Access) will not get you use of the access object model.

So while VB6, FoxPro, c#, vb.net can use the database engine separate from the application object model, to pull things like forms, reports or code modules, you will need the access.application object model.

Access is a development tool. You can use Access to build applications that work with Oracle, or SQL server. So often, the data engine you use with Access will not be the built in one, but an external data system. So to grab objects of the Access application (and not the data engine), then you need an installed copy of Access for this purpose.

This is really not different then say wanting to grab and work with the power-point object model. However, to be fair, all recent versions of office store “everything” as a zip file. I you rename a word file, excel file, power-point file as .zip extension, then you can simply open up that zip file and browse + see all of the xml, and even code.

However, Access is the only product that continues to store everything in a binary object model, and thus grabbing of those objects for all practical purposes requires the access application object to gain use of such objects. So while most office files can be re-named as a zip file of which then you can browse and extract the xml files inside of that zip container, unfortunately Access is not a “zip” container like most other office files.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

For the Queries part

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=5;

Exclude the WHERE to see the different objects.

You could grab the sql by creating an Access Object in .net

Microsoft.Office.Interop.Access.Dao

See: https://stackoverflow.com/a/1458873/2895831

Microsoft.Office.Interop.Access.Application app = new Application(); 
app.OpenCurrentDatabase(@"[FILENAME]", false,"");
QueryDefs qdefs = app.CurrentDb().QueryDefs;
 foreach (QueryDef qdef in qdefs)
 {
     string qname = qdef.Name;
     string qSql = qdef.SQL;
 }
 app.Quit(AcQuitOption.acQuitSaveNone);

—-

To better understand the tables, you can go into the Nav Pane and unhide the Sys tables.

Alex Hedley
  • 776
  • 10
  • 24