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;

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.

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

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"

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

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

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

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...

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

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

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

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

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.

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

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...

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....:o cheers Fi

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

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

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

' Enumerate TableDefs collection.
For Each tdfLoop In .TableDefs
Debug.Print " " & tdfLoop.Name
Next tdfLoop
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

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

It is real easy tho... You just need the Dims, the sets, the with and the red bit... just copy paste :)