Welcome to SPN

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

Sign Up Now!

Please HELP! About SQL for ADO

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

Tags:
  1. Cicada

    Cicada
    Expand Collapse
    Guest

    I am using an application similar to MS Access and use the ADO engine also.
    I have a few SQL questions. Please kindly help.

    1. Any SQL command for Compressing database, as that supported in MS
    Access's appliction level?

    2. Any SQL command equivalent to "Execute Immediate" command for Oracle?

    3. Any SQL command to backup a table in current opened Database to another
    Database??

    4. I test below SQL command with Substring() / Substr() and get failure.
    WHY?
    SELECT Substring(table.field) FROM table
    a) Has test field in varchar type and text(i.e.memo) type. Both failure.
    b) Has replace Substring() with Left(). Successful!
    I very need to use Substring(). How to solve it?

    Notice: All above questions are asking for SQL command, not Application
    command

    SUPER THANKS!
     
  2. Loading...

    Similar Threads Forum Date
    General Help! Looking for a good clairvoyant/card reader......any suggestions? Please Blogs Jan 25, 2016
    Please help Questions and Answers Nov 1, 2013
    Please Help! What is the Name of the Narrator/Raagi? Gurbani Download Oct 16, 2012
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012
    Help Please :/ Relationships Apr 19, 2011

  3. Smartin

    Smartin
    Expand Collapse
    Guest

    Cicada wrote:
    > I am using an application similar to MS Access and use the ADO engine also.
    > I have a few SQL questions. Please kindly help.
    >
    > 1. Any SQL command for Compressing database, as that supported in MS
    > Access's appliction level?
    >
    > 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >
    > 3. Any SQL command to backup a table in current opened Database to another
    > Database??
    >
    > 4. I test below SQL command with Substring() / Substr() and get failure.
    > WHY?
    > SELECT Substring(table.field) FROM table
    > a) Has test field in varchar type and text(i.e.memo) type. Both failure.
    > b) Has replace Substring() with Left(). Successful!
    > I very need to use Substring(). How to solve it?
    >
    > Notice: All above questions are asking for SQL command, not Application
    > command
    >
    > SUPER THANKS!
    >
    >


    Can't help with 1-3, but for #4, try
    LEFT (string, len)
    MID (string, start [,len])
    RIGHT(string, len)
    --
    Smartin
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Replies inline

    Pieter

    "Cicada" <patwwh@hotmail.com> wrote in message
    news:OGIXSKDsGHA.3412@TK2MSFTNGP05.phx.gbl...
    >I am using an application similar to MS Access and use the ADO engine also.
    > I have a few SQL questions. Please kindly help.
    >
    > 1. Any SQL command for Compressing database, as that supported in MS
    > Access's appliction level?


    No, But you can use the DAO.CompactDatabase method to create a compacted
    copy of the DB
    you'd then have to copy the compacted Db back to it's original name

    >
    > 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >

    Not as such, you'd have to use DAO/ADO to run the DDL's

    > 3. Any SQL command to backup a table in current opened Database to another
    > Database??

    see above
    >
    > 4. I test below SQL command with Substring() / Substr() and get failure.
    > WHY?
    > SELECT Substring(table.field) FROM table
    > a) Has test field in varchar type and text(i.e.memo) type. Both failure.
    > b) Has replace Substring() with Left(). Successful!
    > I very need to use Substring(). How to solve it?
    >

    Jet Uses the Mid Function, Not Substr
    > Notice: All above questions are asking for SQL command, not Application
    > command
    >
    > SUPER THANKS!
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Replies inline

    Pieter

    "Cicada" <patwwh@hotmail.com> wrote in message
    news:OGIXSKDsGHA.3412@TK2MSFTNGP05.phx.gbl...
    >I am using an application similar to MS Access and use the ADO engine also.
    > I have a few SQL questions. Please kindly help.
    >
    > 1. Any SQL command for Compressing database, as that supported in MS
    > Access's appliction level?


    No, But you can use the DAO.CompactDatabase method to create a compacted
    copy of the DB
    you'd then have to copy the compacted Db back to it's original name

    >
    > 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >

    Not as such, you'd have to use DAO/ADO to run the DDL's

    > 3. Any SQL command to backup a table in current opened Database to another
    > Database??

    see above
    >
    > 4. I test below SQL command with Substring() / Substr() and get failure.
    > WHY?
    > SELECT Substring(table.field) FROM table
    > a) Has test field in varchar type and text(i.e.memo) type. Both failure.
    > b) Has replace Substring() with Left(). Successful!
    > I very need to use Substring(). How to solve it?
    >

    Jet Uses the Mid Function, Not Substr
    > Notice: All above questions are asking for SQL command, not Application
    > command
    >
    > SUPER THANKS!
    >




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

    Smartin
    Expand Collapse
    Guest

    Smartin wrote:
    > Cicada wrote:
    >> I am using an application similar to MS Access and use the ADO engine
    >> also.
    >> I have a few SQL questions. Please kindly help.
    >>
    >> 1. Any SQL command for Compressing database, as that supported in MS
    >> Access's appliction level?
    >>
    >> 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >>
    >> 3. Any SQL command to backup a table in current opened Database to
    >> another Database??
    >>
    >> 4. I test below SQL command with Substring() / Substr() and get
    >> failure. WHY?
    >> SELECT Substring(table.field) FROM table
    >> a) Has test field in varchar type and text(i.e.memo) type. Both
    >> failure.
    >> b) Has replace Substring() with Left(). Successful!
    >> I very need to use Substring(). How to solve it?
    >>
    >> Notice: All above questions are asking for SQL command, not
    >> Application command
    >>
    >> SUPER THANKS!
    >>

    >
    > Can't help with 1-3, but for #4, try
    > LEFT (string, len)
    > MID (string, start [,len])
    > RIGHT(string, len)


    OK for #3 you can do

    SELECT * INTO BackupTable IN 'path\database.mdb' FROM SomeTable;

    You might want to

    DELETE * FROM BackupTable IN 'path\database.mdb';

    first. Not sure if you can DROP a table in another database.

    HTH
    --
    Smartin
     
  7. Cicada

    Cicada
    Expand Collapse
    Guest

    As I know, ADO's engine is OLE DB.
    Then, I don't udnerstand at 2 points.

    I see ADO and DAO as the same level, and the former is newer than the
    latter.
    Actually, does ADO completely "replace" or "contain" DAO??
    ~~~> related to Pieter's answer 1 below

    Similarly, I see OLE DB and JET as the same level, and the former is newer
    than the latter.
    Again, does OLE DB completely "replace" or "contain" JET ??
    ~~~> related to Pieter's answer 4 below

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    ¼¶¼g©ó¶l¥ó·s»D:uSo91ZEsGHA.1288@TK2MSFTNGP02.phx.gbl...
    > Replies inline
    >
    > Pieter
    >
    > "Cicada" <patwwh@hotmail.com> wrote in message
    > news:OGIXSKDsGHA.3412@TK2MSFTNGP05.phx.gbl...
    >>I am using an application similar to MS Access and use the ADO engine
    >>also.
    >> I have a few SQL questions. Please kindly help.
    >>
    >> 1. Any SQL command for Compressing database, as that supported in MS
    >> Access's appliction level?

    >
    > No, But you can use the DAO.CompactDatabase method to create a compacted
    > copy of the DB
    > you'd then have to copy the compacted Db back to it's original name
    >
    >>
    >> 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >>

    > Not as such, you'd have to use DAO/ADO to run the DDL's
    >
    >> 3. Any SQL command to backup a table in current opened Database to
    >> another Database??

    > see above
    >>
    >> 4. I test below SQL command with Substring() / Substr() and get failure.
    >> WHY?
    >> SELECT Substring(table.field) FROM table
    >> a) Has test field in varchar type and text(i.e.memo) type. Both failure.
    >> b) Has replace Substring() with Left(). Successful!
    >> I very need to use Substring(). How to solve it?
    >>

    > Jet Uses the Mid Function, Not Substr
    >> Notice: All above questions are asking for SQL command, not Application
    >> command
    >>
    >> SUPER THANKS!
    >>

    >
    >
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    ADO can use a number of different drivers (much like ODBC) including DAO
    (over)simplified It is a wrapper for OLE DB which is a wrapper for the
    native drivers
    OLE DB is/was rather tougher to deal with directly

    Pieter

    "Cicada" <patwwh@hotmail.com> wrote in message
    news:O8eACtNsGHA.4744@TK2MSFTNGP02.phx.gbl...
    > As I know, ADO's engine is OLE DB.
    > Then, I don't udnerstand at 2 points.
    >
    > I see ADO and DAO as the same level, and the former is newer than the
    > latter.
    > Actually, does ADO completely "replace" or "contain" DAO??
    > ~~~> related to Pieter's answer 1 below
    >
    > Similarly, I see OLE DB and JET as the same level, and the former is newer
    > than the latter.
    > Again, does OLE DB completely "replace" or "contain" JET ??
    > ~~~> related to Pieter's answer 4 below
    >
    > "Pieter Wijnen"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > ¼¶¼g©ó¶l¥ó·s»D:uSo91ZEsGHA.1288@TK2MSFTNGP02.phx.gbl...
    >> Replies inline
    >>
    >> Pieter
    >>
    >> "Cicada" <patwwh@hotmail.com> wrote in message
    >> news:OGIXSKDsGHA.3412@TK2MSFTNGP05.phx.gbl...
    >>>I am using an application similar to MS Access and use the ADO engine
    >>>also.
    >>> I have a few SQL questions. Please kindly help.
    >>>
    >>> 1. Any SQL command for Compressing database, as that supported in MS
    >>> Access's appliction level?

    >>
    >> No, But you can use the DAO.CompactDatabase method to create a compacted
    >> copy of the DB
    >> you'd then have to copy the compacted Db back to it's original name
    >>
    >>>
    >>> 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >>>

    >> Not as such, you'd have to use DAO/ADO to run the DDL's
    >>
    >>> 3. Any SQL command to backup a table in current opened Database to
    >>> another Database??

    >> see above
    >>>
    >>> 4. I test below SQL command with Substring() / Substr() and get failure.
    >>> WHY?
    >>> SELECT Substring(table.field) FROM table
    >>> a) Has test field in varchar type and text(i.e.memo) type. Both
    >>> failure.
    >>> b) Has replace Substring() with Left(). Successful!
    >>> I very need to use Substring(). How to solve it?
    >>>

    >> Jet Uses the Mid Function, Not Substr
    >>> Notice: All above questions are asking for SQL command, not Application
    >>> command
    >>>
    >>> SUPER THANKS!
    >>>

    >>
    >>

    >
    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    ADO can use a number of different drivers (much like ODBC) including DAO
    (over)simplified It is a wrapper for OLE DB which is a wrapper for the
    native drivers
    OLE DB is/was rather tougher to deal with directly

    Pieter

    "Cicada" <patwwh@hotmail.com> wrote in message
    news:O8eACtNsGHA.4744@TK2MSFTNGP02.phx.gbl...
    > As I know, ADO's engine is OLE DB.
    > Then, I don't udnerstand at 2 points.
    >
    > I see ADO and DAO as the same level, and the former is newer than the
    > latter.
    > Actually, does ADO completely "replace" or "contain" DAO??
    > ~~~> related to Pieter's answer 1 below
    >
    > Similarly, I see OLE DB and JET as the same level, and the former is newer
    > than the latter.
    > Again, does OLE DB completely "replace" or "contain" JET ??
    > ~~~> related to Pieter's answer 4 below
    >
    > "Pieter Wijnen"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > ¼¶¼g©ó¶l¥ó·s»D:uSo91ZEsGHA.1288@TK2MSFTNGP02.phx.gbl...
    >> Replies inline
    >>
    >> Pieter
    >>
    >> "Cicada" <patwwh@hotmail.com> wrote in message
    >> news:OGIXSKDsGHA.3412@TK2MSFTNGP05.phx.gbl...
    >>>I am using an application similar to MS Access and use the ADO engine
    >>>also.
    >>> I have a few SQL questions. Please kindly help.
    >>>
    >>> 1. Any SQL command for Compressing database, as that supported in MS
    >>> Access's appliction level?

    >>
    >> No, But you can use the DAO.CompactDatabase method to create a compacted
    >> copy of the DB
    >> you'd then have to copy the compacted Db back to it's original name
    >>
    >>>
    >>> 2. Any SQL command equivalent to "Execute Immediate" command for Oracle?
    >>>

    >> Not as such, you'd have to use DAO/ADO to run the DDL's
    >>
    >>> 3. Any SQL command to backup a table in current opened Database to
    >>> another Database??

    >> see above
    >>>
    >>> 4. I test below SQL command with Substring() / Substr() and get failure.
    >>> WHY?
    >>> SELECT Substring(table.field) FROM table
    >>> a) Has test field in varchar type and text(i.e.memo) type. Both
    >>> failure.
    >>> b) Has replace Substring() with Left(). Successful!
    >>> I very need to use Substring(). How to solve it?
    >>>

    >> Jet Uses the Mid Function, Not Substr
    >>> Notice: All above questions are asking for SQL command, not Application
    >>> command
    >>>
    >>> SUPER THANKS!
    >>>

    >>
    >>

    >
    >




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

Share This Page