Interesting SQL commands

[Cicada]

Today I see a PostgreSQL SQL book and find some interesting commands below.
Is there any similar SQL commands under ADO??

1. Meta-Command
\d_\dt List All TAbles
\dv List All View
...... List All Query (equal to View??)
...... List All Users

2. Frequency Statistic
Select n_distinct, most_common_vals, most_common_freqs FROM .....
==> these 3 terms are system terms. Result is showing N-th data/value
occuring with top frequency in a table's field.

Besides, please help:
3. There is a System table in MS Access "MsysObjects".
I can open it in MS Access easily, without any password needed, but not
in my application.
How to solve it?

4. I have searched through Google with keywords SQL ADO or SQL "OLE DB", I
feel very strange that there is only a few data for the former, and None for
the latter.
Even though their ultimate data engine may be ODBC or Jet, their SQL
language in User-Level should be defined by themselves.
(Otherwise, there will not be so many SQL language around MS
applications :> )
Could you introduce me any tutorial and handbook web site for it??

 

[Pieter Wijnen]

For all Tables
Create a new Query ( MSysObjects is hidden - leave it so - can be changed
through tools/options/show hidden objects)
Hit cancel for the table selection
View SQL
Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
This will give you all the tables (native, Linked, ODBC)

For Queries (Views)
SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM MSysQueries
B WHERE B.ObjectId =A.Id)

To get all the users you'll need to use VB Code:

Sub CreateUserTable()
Dim Ws As DAO.WorkSpace
Dim Db As DAO.Database
Dim Usr As DAO.User

Set Ws = DAO.DbEngine.Workspaces(0)
Set Db = CurrentDb
Db.Execute "DROP Table USysUsers"
Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
Primary"

For Each Usr In Ws.Users
Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name & "')"
Next ' Usr

Set Db = Nothing
Set Ws = Nothing
End Sub

now you can use (to show users - including "internal users" - Engine,
Creator)
SELECT UserName FROM USysUsers

For MSSQL, Oracle(8i +) you can query:
INFORMATION_SCHEMA.TABLES etc

Pieter

 

[Pieter Wijnen]

For all Tables
Create a new Query ( MSysObjects is hidden - leave it so - can be changed
through tools/options/show hidden objects)
Hit cancel for the table selection
View SQL
Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
This will give you all the tables (native, Linked, ODBC)

For Queries (Views)
SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM MSysQueries
B WHERE B.ObjectId =A.Id)

To get all the users you'll need to use VB Code:

Sub CreateUserTable()
Dim Ws As DAO.WorkSpace
Dim Db As DAO.Database
Dim Usr As DAO.User

Set Ws = DAO.DbEngine.Workspaces(0)
Set Db = CurrentDb
Db.Execute "DROP Table USysUsers"
Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
Primary"

For Each Usr In Ws.Users
Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name & "')"
Next ' Usr

Set Db = Nothing
Set Ws = Nothing
End Sub

now you can use (to show users - including "internal users" - Engine,
Creator)
SELECT UserName FROM USysUsers

For MSSQL, Oracle(8i +) you can query:
INFORMATION_SCHEMA.TABLES etc

Pieter

Today I see a PostgreSQL SQL book and find some interesting commands
below.
Is there any similar SQL commands under ADO??

1. Meta-Command
\d_\dt List All TAbles
\dv List All View
...... List All Query (equal to View??)
...... List All Users

2. Frequency Statistic
Select n_distinct, most_common_vals, most_common_freqs FROM .....
==> these 3 terms are system terms. Result is showing N-th data/value
occuring with top frequency in a table's field.

Besides, please help:
3. There is a System table in MS Access "MsysObjects".
I can open it in MS Access easily, without any password needed, but not
in my application.
How to solve it?

4. I have searched through Google with keywords SQL ADO or SQL "OLE DB", I
feel very strange that there is only a few data for the former, and None
for the latter.
Even though their ultimate data engine may be ODBC or Jet, their SQL
language in User-Level should be defined by themselves.
(Otherwise, there will not be so many SQL language around MS
applications :> )
Could you introduce me any tutorial and handbook web site for it??

--

 

[david epsom dot com dot au]

Use the syscmd to find the workgroup database, then
open the user list table in the workgroup database

(david)

"Pieter Wijnen"

 

[John Vinson]

Today I see a PostgreSQL SQL book and find some interesting commands below.
Is there any similar SQL commands under ADO??

Not really... but Tools... Analyze... Documentor will show you all
this information and more (too much information actually, you should
check the options before printing!)

John W. Vinson[MVP]

 

[Cicada]

As I state in Point 3, I can view (not hidden) MsysObjects table in both
Access & my application (ADO-based),
but I can just OPEN it in Access, but not in my application, and error
message is "No authorization ro read". (but I have not set any
authorization level myself)
(This application is not not programmed by me, but a standard database
application very similar to Access).

How to solve it 1st??


"Pieter Wijnen"

 

[Pieter Wijnen]

Oh yeah I forgot about that, silly me
you can query MSysAccounts in the system.mdw (workGroup file), to find the
users (do it myself - I must admit)

For Users:
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP = 0

For Groups (Roles)
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP <>0

For User/Roles (Membership):
SELECT G.Name AS GroupName, U.Name AS UserName
FROM (MSysAccounts U INNER JOIN MSysGroups UG ON U.SID = UG.UserSID) INNER
JOIN MSysAccounts G ON UG.GroupSID = G.SID
WHERE U.FGroup=0 AND G.FGroup<>0

Pieter

 

[Pieter Wijnen]

Oh yeah I forgot about that, silly me
you can query MSysAccounts in the system.mdw (workGroup file), to find the
users (do it myself - I must admit)

For Users:
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP = 0

For Groups (Roles)
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP <>0

For User/Roles (Membership):
SELECT G.Name AS GroupName, U.Name AS UserName
FROM (MSysAccounts U INNER JOIN MSysGroups UG ON U.SID = UG.UserSID) INNER
JOIN MSysAccounts G ON UG.GroupSID = G.SID
WHERE U.FGroup=0 AND G.FGroup<>0

Pieter

As I state in Point 3, I can view (not hidden) MsysObjects table in both
Access & my application (ADO-based),
but I can just OPEN it in Access, but not in my application, and error
message is "No authorization ro read". (but I have not set any
authorization level myself)
(This application is not not programmed by me, but a standard database
application very similar to Access).

How to solve it 1st??


"Pieter Wijnen"

--

 

[Cicada]

Thanks very much for all of your great help!
I have learned a great lesson.

"Pieter Wijnen"

 

[Pieter Wijnen]

always nice to help the inquisitive kind

Pieter

Thanks very much for all of your great help!
I have learned a great lesson.

"Pieter Wijnen"

 

[Guest]

In your application, are you explicitly setting the workgroup
and user name? Try that.

(david)