List of form from external database 1

[Aqif]

Hi

I am trying to get a list of forms from an external database. I have already written a code to get list of tables which looks like this

Dim extDB As DAO.Database
Dim Tdf As DAO.TableDef
Dim frm As Form
Dim strMsg As String
Dim strTN As String

Set extDB = DBEngine.Workspaces(0).OpenDatabase("c:\MY_Data.mdb")

For Each Tdf In extDB.TableDefs

If Tdf.Name Like "MSys*" Then
Else
strTN = Tdf.Name
strMsg = strMsg & vbCrLf & Left(Tdf.Name & " ", 25) & " table - " & Nz(extDB.TableDefs(strTN).Properties("Description"), " ")
End If

Next


Me.Text1 = strMsg


I want same sort of code to give me list of form and its description. It appears that there is not propoerty like DB.Forms like DB.TableDfes

Any ideas?


Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[Remou]

Hi
Have you seen this: thread181-880255?

 

[TheAceMan1]

How are ya Aqif . . . . .

Not sure about the description yet, but you can return the forms & tables with following SQL. Note replace [purple]Type[/purple] with 6 for tables & -32768 for forms:

[blue]   SELECT MSysObjects.Type, MSysObjects.Name
   FROM MSysObjects In "[purple][b]Your Path & Filename Here[/b][/purple]"
   WHERE (((MSysObjects.Type)=[purple][b]Type[/b][/purple]));[/blue]

See Ya! . . . . . .

 

[Aqif]

Tried to look at the MSys Tables solution but contains no field for description.

Any ideas?


Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[TheAceMan1]

Aqif . . . . .

After looking on and off in my library, found what need (with description).

Question is, how do you want to view this (listbox, memo field) what?

See Ya! . . . . . .

 

[Aqif]

Hi,

I just want to put values in a table so that I can generate report out of them. So the table will have fillowing fields

Object Name Type Description
FrmMain Form Main menu

Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[TheAceMan1]

OK Aqif . . . .

Putting it together now.

Usless you say different gonna clear the table of type form first, unless you just want to append!

See Ya! . . . . . .

 

[TheAceMan1]

Aqif . . . here we go . . .

In the table, for the [blue]Description field[/blue], set the [blue]Allow Zero Length property[/blue] to [purple]Yes[/purple] (important).

Next, in a module in the module window, copy/paste the following code ([blue]You![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub GetExtForms()
   Dim db As DAO.Database, SQL As String
   Dim con As DAO.Container, doc As DAO.Document
   Dim frmName As String, Describe As String
   
   Set db = OpenDatabase("[purple][b]External DB Path & FileName[/b][/purple]")
   Set con = db.Containers("Forms")
   
   If con.Documents.Count <> 0 Then [green]'Any Reports?[/green]
      [green]'Remove Type Forms from table[/green]
      SQL = "DELETE Type " & _
            "FROM [purple][b]TableName[/b][/purple]" & _
            " WHERE (Type='Form');"
      DoCmd.RunSQL SQL
      
      On Error GoTo PadErr [green]'Trap no description[/green]
      
      [green]'Append to table[/green]
      For Each doc In con.Documents
         frmName = doc.Name
         Describe = doc.Properties("Description")
PadRtn:
         SQL = "INSERT INTO [purple][b]TableName[/b][/purple] (ObjectName, Type, Description) " & _
               "SELECT '" & frmName & "','Form','" & Describe & "';"
         DoCmd.RunSQL SQL
      Next
   End If
   
   Set con = Nothing
   Set db = Nothing
   Exit Sub

PadErr: [green]'Set for no description[/green]
   Describe = ""
   Resume PadRtn

End Sub[/blue]

You can call the routine from anywhere you like!

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[Aqif]

Hi,

Working flawless. Very impressed and thankful. Have a star



Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.