Please HELP! About SQL for ADO

[Cicada]

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!

 

[Smartin]

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)

 

[Pieter Wijnen]

Replies inline

Pieter

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

 

[Pieter Wijnen]

Replies inline

Pieter

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!

--

 

[Smartin]

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

 

[Cicada]

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"

 

[Pieter Wijnen]

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

 

[Pieter Wijnen]

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

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"

--