MySysObjects Table Type

[fibayne]

Hi Im trying to make the tables visible from a list box and found a thread with this, which looks like it is what I need unfortunately I get a syntax error, I have tried amending the code but my knowledge is fairly limited, could anyone hlp me ??

thanks in advance
cheers
Fi


SELECT MSysObjects.Name FROM MSysObjects WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4)<>"Msys") AND (MSysObjects.Type) = 1 ORDER BY MSysObjects.Name;

 

[namliam]

We cannot smell what it is you want changed...

What is it you want done???

Also:
The use of MSysObjects is NOT supported by M$, which means that it may not work in the new version.

 

[fibayne]

hi.. what i tried to do was put this code in the row source of the list box I would like to use to view the tables in my DB, I have it working (from another good thread ...) where it shows the reports on my db which I can double click on to open them up, that code is

SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;

so i thought if I could find out what 'Type' Tables are i could make it do the same ??? does that make any sense ?

thanks for your help

fi

 

[namliam]

Like I said the use of MsysObjects is not supported by M$ so you maybe shouldnt be using it.

changing the -32764 to 1 should list all the tables. But you allready did that... So what is your problem?

For a better alternative lookup "Tabledefs" in the access help to find a good sample of listing all the tables the "proper M$ way"

 

[jdraw]

hi.. what i tried to do was put this code in the row source of the list box I would like to use to view the tables in my DB, I have it working (from another good thread ...) where it shows the reports on my db which I can double click on to open them up, that code is

SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;

so i thought if I could find out what 'Type' Tables are i could make it do the same ??? does that make any sense ?

thanks for your help

fi

Try Type = 1 for Tables

 

[raskew]

Hi -

Local tables are 1, linked tables are 6.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type) In (1,6)) AND ((Left([Name],4))<>'MSys') AND ((Left([Name],1))<>'~'))
ORDER BY MSysObjects.Name;

Bob

 

[fibayne]

Hi NAMLIAM apologies but what is 'M$' ?? I am using MS Office 97 and a novice !! will look at what you suggested to see if that gets me there

I changed Type -32764 to Type 1 and did get the tables to show but it also listed what looked like some system tables also, so it was pretty close but I wouldnt know how to alter the code to make only the DB tables show, making any more sense ?

cheers

Fi

 

[namliam]

M$ = commonly used acronym for Microsoft... I thought it was anyway

Try the SQL supplied by Bob, but I wouldnt use the Msystables... If it breaks, you are stuck... while tabledefs should keep right on working...

 

[raskew]

Try the code in Post 6. You are getting msys tables because you haven't specifically excluded them, e.g.

AND ((Left([Name],4))<>'MSys')

Bob

 

[fibayne]

Hi Bob...that worked a treat thank you very very much..i can feel this may lead to another question when i try to do the next part of what I am trying to do !!!

cheers really appreciated

Fi

 

[fibayne]

Hi Namliam...and thanks to you also.. M$ may have not reached Gibraltar yet

 

[fibayne]

Hi Again...to have the list show only one table, in this case tbl_Contacts is it possible to exclude all but this one from the list ??

cheers

Fi

 

[namliam]

Really I must urge you one more time... Please dont use the Msys tables provided by M$... Look into the Tabledefs/Querydefs/etc objects and use them.

If not onlly for this, but they are good for so much more too.

 

[fibayne]

Hi Namliam..looked at this in access help but due to being a novice really dont get it sorry will keep at it and perhaps the penny will drop !
cheers Fi

 

[namliam]

On the top there is "examples" there is a clear cut example there which currently works for the Northwind database.

With very little change you can use it for your own...

 

[fibayne]

Hi...all i can get from help is one topic 'Access 2007 reserved words and symbols' when you go into this it list all the reserved words but not any examples...what did you type into 'Help' to see examples.... cheers Fi

 

[namliam]

You have to get into "code"

Type "Tabledefs" and hit F1, click example and choose the TableDef Object

You will get this example

Sub CreateTableDefX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create a new TableDef object.
    Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

    With tdfNew
        ' Create fields and append them to the new TableDef 
        ' object. This must be done before appending the 
        ' TableDef object to the TableDefs collection of the 
        ' Northwind database.
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)

        Debug.Print "Properties of new TableDef object " & _
            "before appending to collection:"

        ' Enumerate Properties collection of new TableDef 
        ' object.
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "  " & _
              prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop

        ' Append the new TableDef object to the Northwind 
        ' database.
        dbsNorthwind.TableDefs.Append tdfNew

        Debug.Print "Properties of new TableDef object " & _
            "after appending to collection:"

        ' Enumerate Properties collection of new TableDef 
        ' object.
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "  " & _
              prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop

    End With

    ' Delete new TableDef object since this is a 
    ' demonstration.
    dbsNorthwind.TableDefs.Delete "Contacts"

    dbsNorthwind.Close

End Sub

This should be very easy to addapt to what you need

 

[fibayne]

ahhh...will see how I get on with this looks daunting but a challenge..thanks again for your help will let you know how I get on ..thanks for your patience..cheers Fi

 

[namliam]

Can you believe this??? I pasted the WRONG example ! ! ! !

The previous sample is how to create tables in code... that is a challenge yes...

Below code is to list tables and their properties... much less code and much less complicated...

Sub TableDefX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim tdfLoop As TableDef
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create new TableDef object, append Field objects 
    ' to its Fields collection, and append TableDef 
    ' object to the TableDefs collection of the 
    ' Database object.
    Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")
    tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate)
    dbsNorthwind.TableDefs.Append tdfNew

    With dbsNorthwind
        Debug.Print .TableDefs.Count & _
            " TableDefs in " & .Name

[COLOR="Red"]        ' Enumerate TableDefs collection.
        For Each tdfLoop In .TableDefs
            Debug.Print "  " & tdfLoop.Name
        Next tdfLoop[/COLOR]
        With tdfNew
            Debug.Print "Properties of " & .Name

            ' Enumerate Properties collection of new
            ' TableDef object, only printing properties
            ' with non-empty values.
            For Each prpLoop In .Properties
                Debug.Print "  " & prpLoop.Name & " - " & _
                    IIf(prpLoop = "", "[empty]", prpLoop)
            Next prpLoop

        End With

        ' Delete new TableDef since this is a 
        ' demonstration.
        .TableDefs.Delete tdfNew.Name
        .Close
    End With

End Sub

You need to do the Dims and stuff, but the red part is basicaly what you are intrested in

 

[fibayne]

oops ...i would never have worked that one out !!