Catalog of Import/Export specs.

All,

I want to catalog the names of all Import and Export specifications in all ACCESS databases on my disk. I can enumerate all mdb files with:

    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\"
        .SearchSubFolders = True
        .fileName = "*.mdb"
        ' Search for them.
        If .Execute() > 0 Then
            Dim i As Integer
            ' Look at all databases.
            For i = 1 To .FoundFiles.Count
                    ' Found one! Get its data.
                    CollectSpecifications .FoundFiles(i)
            Next i
        Else
            MsgBox "There were no database files found."
        End If
    End With

In "CollectSpecifications" I want to enumerate all hidden/system tables and, in each import specification table, find the names of all specifications?

TIA

Paul
LVL 16
PaulCaswellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
This query will give you the specifications in a particular database:

SELECT SpecName FROM MSysIMEXSpecs IN 'C:\MyDb.mdb'
0
PaulCaswellAuthor Commented:
Thanks! Its the 'enumerate tables' bit and the integration with VB bit I could make most use of. I'm asking because I dont have the time, not because I dont know how.

Paul
0
shanesuebsahakarnCommented:
Ah - are you familiar with recordsets? Do you just want to retrieve the names of all tables? What do you want to do with them once you've got them - do you need to just print them out or insert them as new records into a table?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PaulCaswellAuthor Commented:
I am familliar with Recordsets but, again, some working VBscript code that enumerates the tables, executes the queries and pulls the name out of the correct record would be worth the points.

Printing them out would be fine.

Paul
0
shanesuebsahakarnCommented:
In this code example, I assume that the name of the mdb is in a variable called strFile.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn=New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\myDb.mdb;User Id=admin;Password="
Set rst=cnn.Execute "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name"
While Not rst.EOF
   response.write rst!Name
   rst.MoveNext
Wend
rst.Close
cnn.Close

This assumes you are trying to output to a webpage or somesuch.
0
PaulCaswellAuthor Commented:
' Finds imports and exports in Access databases.
Public Sub FindPorts()
    Set cnn = New ADODB.Connection
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Temp\"
        .SearchSubFolders = True
        .fileName = "*.mdb"
        ' Search for them.
        If .Execute() > 0 Then
            Dim i As Integer
            ' Look at all databases.
            For i = 1 To .FoundFiles.Count
                ' Found one! Get its data.
                Debug.Print .FoundFiles(i)
                cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & .FoundFiles(i)
                Set rst = cnn.Execute("SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name")
                While Not rst.EOF
                   Debug.Print rst!Name
                   rst.MoveNext
                Wend
                rst.Close
                cnn.Close
            Next i
        Else
            MsgBox "There were no host files found."
        End If
    End With

End Sub

Two problems:

1. It seems to enumerate all tables in the database, not the "MsysObjects" table entries.
2. ON the second database it finds it generates a Run-time error 80040e09: Record(s) cannot be read; no read permission on MsysObjects. I DO have access to this database and this table! I can open the database and look at the table without problem.

Any ideas?

Paul
0
shanesuebsahakarnCommented:
Did you just want the MSys tables? If so, you just need to change this line:
Set rst = cnn.Execute("SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name")

to:
Set rst = cnn.Execute("SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) = 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name")
0
PaulCaswellAuthor Commented:
I just want the names of the imports/exports so I expect I actually only need MSysIMEXSpecs.SpecName.

This is now listing all the Msys... tables but not giving me the names of the imports/exports.

I'm still getting the Run-time erropr on the second database.

Paul
0
PaulCaswellAuthor Commented:
Points will be doubled if I can also enumerate the names of all the source code modules and the names of all the functions/procedures inside them.

Paul
0
shanesuebsahakarnCommented:
To get the names of the specs, change the Set rst line to my original query:
Set rst=cnn.Execute("SELECT SpecName FROM MSysIMEXSpecs")

and the print line to:
Debug.Print rst!SpecName

To retrieve object names, the SELECT statement needs to be:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND
(MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PaulCaswellAuthor Commented:
Nearly there!! I've now got:

' Finds imports and exports in Access databases.
Public Sub FindPorts()
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Temp\"
        .SearchSubFolders = True
        .fileName = "*.mdb"
        ' Search for them.
        If .Execute() > 0 Then
            Dim i As Integer
            ' Look at all databases.
            For i = 1 To .FoundFiles.Count
                ' Found one! Get its data.
                Set cnn = New ADODB.Connection
                On Error GoTo noDatabase
                cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & .FoundFiles(i)
                On Error GoTo noTable
                Set rst = cnn.Execute("SELECT SpecName FROM MSysIMEXSpecs")
                While Not rst.EOF
                   Debug.Print .FoundFiles(i) & "," & rst!SpecName
                   rst.MoveNext
                Wend
                rst.Close
noTable:
                cnn.Close
noDatabase:
            Next i
        Else
            MsgBox "There were no host files found."
        End If
    End With

End Sub

But the cnn.Open errors when I try to open a Database that I dont have permissions for. Is there any way I can catch this like the others?

Paul
0
shanesuebsahakarnCommented:
You can trap for the error:

Sub FindPorts()
On Error Goto FindPorts_Error

<your code goes here>

Exit Sub

FIndPorts_Error:
If Err.Number=<error number for no permissions> Then
   MsgBox "Tried to open a secured db."
   Resume noDatabase
Else
   MsgBox Err.Number & " " & Err.Description
End If
End Sub
0
PaulCaswellAuthor Commented:
But I've already got a:

                On Error GoTo noDatabase

before the cnn.Open call. The error code is 80004005. It must be a special one. It seems not to be trapped by the 'On Error Goto' stuff.

Paul
0
shanesuebsahakarnCommented:
Try swappin around the on error and cnn.Open lines - does that do it?
0
PaulCaswellAuthor Commented:
>>Try swappin around the on error and cnn.Open lines - does that do it?
Same thing!! Error 80004005 when It gets to a passworded database.

Paul
0
PaulCaswellAuthor Commented:
I feel so close but not quite a cigar yet.

Perhaps there is a way I can pre-empt this error? Can anyone think of one? I need to be able to tell if a database will require a password to log on.

Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.