[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Catalog of Import/Export specs.

Posted on 2004-11-25
16
Medium Priority
?
246 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:PaulCaswell
  • 9
  • 7
16 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12674124
This query will give you the specifications in a particular database:

SELECT SpecName FROM MSysIMEXSpecs IN 'C:\MyDb.mdb'
0
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12674175
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12674200
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Author Comment

by:PaulCaswell
ID: 12674237
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12674340
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
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12675054
' 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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12675082
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
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12675157
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
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12675174
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12675270
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
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12679966
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12680348
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
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12680375
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12680419
Try swappin around the on error and cnn.Open lines - does that do it?
0
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12680504
>>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
 
LVL 16

Author Comment

by:PaulCaswell
ID: 12763412
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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