Retrieve no. of forms, tables, etc. from Access DBs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I have an Excel 97 sheet that lists the paths to no less than 25000 Access
97 databases. For every one of them I have to display the number of tables,
reports, forms and queries in subsequent cells on the sheet.

I am able to access the databases with code in the Excel workbook like:

' init
lngRij = 1

While Worksheets("Sheet1").Cells(lngRij, 2).Value <> ""
strMdbPad = Worksheets("Sheet1").Cells(lngRij, 2).Value
Set objAc = GetObject(strMdbPad)
Debug.Print objAc.CurrentDb.TableDefs(0).Name ' just checking...
Set objAc = Nothing
lngRij = lngRij + 1
Wend

But.
When I open a database with startup code, this code runs. Is there a way to
avoid this?

And.
How can I get the desired information from password protected databases or
databases that require a login?

Many thanks,

Cooz
 
Sorry - I don't understand your question. You say...
When I open a database with startup code, this code runs. Is there a way to
avoid this?
Click to expand...

Please clarify what you mean by "When I open a database with startup code"
(which database and how do you try and open it) and what is the code that
runs?

BW
 
Hi BeWyched,

When you open for example Northwind for the first time, a startup screen
-splashscreen?- appears - displaying a lighthouse if I'm right, plus a
checkbox with which the user can indicate that he doesn't want to see the
screen ever again. There are, I believe, two ways to make screens like this
one appear. The first one is by means of black magic, the second one is by
means of code. Startup code that is... runs automatically when you open a
database.

Any of the 25000 database in the list may contain code that runs
automatically. If so, when I run the below example in Excel, code execution
is transferred from Excel to Access, more often than not requiring some user
input before continuing. In other words: my code stops --> I do not get the
info I want. How do I bypass startup code?

"how do you try and open it": Set objAc = GetObject(strMdbPad) actually
opens the db. This is Office 97; Office 2003 may -should!- show different
behavior.

Note: of course I know the code I stated is far from finished - I should
consult the Containers collection and such - but these few lines already
popped up this issue. Hence my visit to MS Communities.

Cooz
 
Hi again,

You may stop looking and thinking...
Microsoft has a handy tool available that just does the thing I want: the
amazing Access 2003 Conversion Toolkit. It's free, and it does the trick.

Thank you all and BeWyched in particular for your time.

Cooz
 
No probs Cooz.

By the way, if what you've found doesn't work try this which uses
workspaces, rather than opening the databases in the way you have tried. The
second and third parameters in the CreateWorspace function will take your
UserID's and Passwords respectively:

Dim wrkJet, objAcc, prpAcc
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set objAcc = wrkJet.OpenDatabase("path_to_the_database", True)

Debug.Print "Database properties for " & objAcc.Name

On Error Resume Next
For Each prpAcc In objAcc.Properties
MsgBox " " & prpLoop.Name & " = " & prpLoop
Next prpLoop

Cheers.

BW
 
Back
Top