Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Interesting SQL commands

Discussion in 'Information Technology' started by Cicada, Jul 28, 2006.

  1. Cicada

    Cicada
    Expand Collapse
    Guest

    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??
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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


    "Cicada" <patwwh@hotmail.com> wrote in message
    news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    > 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??
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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


    "Cicada" <patwwh@hotmail.com> wrote in message
    news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    > 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??
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

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

    (david)

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    wrote in message news:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    >
    > "Cicada" <patwwh@hotmail.com> wrote in message
    > news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    >> 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??
    >>

    >
    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 27 Jul 2006 05:56:07 +0800, "Cicada" <patwwh@hotmail.com>
    wrote:

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

    Cicada
    Expand Collapse
    Guest

    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"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    >
    > "Cicada" <patwwh@hotmail.com> wrote in message
    > news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    >> 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??
    >>

    >
    >
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Cicada" <patwwh@hotmail.com> wrote in message
    news:%23eo0JjUsGHA.4784@TK2MSFTNGP04.phx.gbl...
    > 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"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gbl...
    >> 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
    >>
    >>
    >> "Cicada" <patwwh@hotmail.com> wrote in message
    >> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    >>> 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??
    >>>

    >>
    >>

    >
    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Cicada" <patwwh@hotmail.com> wrote in message
    news:%23eo0JjUsGHA.4784@TK2MSFTNGP04.phx.gbl...
    > 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"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gbl...
    >> 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
    >>
    >>
    >> "Cicada" <patwwh@hotmail.com> wrote in message
    >> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    >>> 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??
    >>>

    >>
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  10. Cicada

    Cicada
    Expand Collapse
    Guest

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

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    ¼¶¼g©ó¶l¥ó·s»D:eGKKiyVsGHA.356@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > "Cicada" <patwwh@hotmail.com> wrote in message
    > news:%23eo0JjUsGHA.4784@TK2MSFTNGP04.phx.gbl...
    >> 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"
    >> <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    >> ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gbl...
    >>> 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
    >>>
    >>>
    >>> "Cicada" <patwwh@hotmail.com> wrote in message
    >>> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
    >>>> 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??
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page