DDL via DAO: Possible?

We are converting our database from Jet to SQLServer Express. We have a general-purpose database extract/import/management utility, written in VC++, that uses DAO to access and manipulate the database. This utility has grown over the years to include a wide range of functionality, which it would be time-consuming to rewrite.

I've been been able to tweak it to successfully connect to SQLServer, select and update data.

I have NOT been able to find a way to issue DDL, such as ALTER TABLE, etc.

The steps that I am following are:

m_pCDRDatabase = new CDaoDatabase;

m_pCDRDatabase->Open ("",FALSE, FALSE,
"ODBC;"
"PROVIDER=MSDASQL;"
"DSN=DSN1;"
"Database=Current_DB;"
"Uid=user1;"
"Pwd=pwd1;"

m_pCDRQueryDef = new CDaoQueryDef(m_pCDRDatabase);
m_pCDRQueryDef->Create("","DROP TABLE [temp]");
m_pCDRQueryDef->Execute(dbSQLPassThrough);
(I have also tried the dbSeeChanges and dbExecDirect options)

The ->Execute call gives me "Cannot perform this operation.", with an error code of 0x800a0bd8.

I've tried searching MS support, and the web in general, without success.
Does anyone know if it is possible to what I am attempting, and if so, how.

Thanks a lot.

Joe

I don't believe it is using DAO and ODBC based on the Microsoft response to "Problem to connect Access 2003 to SQL Server 2005 Express" - you will have to resort to Visual Studio or the SQL Server Management Studio Express tool. This is conjecture on my part, but I believe that the security enhancements in 2005 are probably one of the major reasons that this won't work.

DDL Conversion

Hi,

How do you convert DDL statements of SQL Server, which are
generated by DTS into other database vendors' syntax (IBM
DB2 or Oracle)?
Any utility tool?

Thank you,

--jaquesjaquesbosch2@.yahoo.de (Jaques) wrote in message news:<569b197f.0311021228.1602722d@.posting.google.com>...
> Hi,
> How do you convert DDL statements of SQL Server, which are
> generated by DTS into other database vendors' syntax (IBM
> DB2 or Oracle)?
> Any utility tool?
> Thank you,
> --jaques

There are a number of third-party products which can do this - I've
used Embarcadero products for similar tasks, which generally work
well, although they can be expensive. The disadvantage of these tools
is that there will always be some platform-specific data types or
syntax which may not be cleanly scripted because there is no direct
equivalent. So there will usually be some amount of manual
checking/modification required.

Simon|||Hi

If you are using a modelling tool, then this may produce the scripts for the
different database systems.

John

"Jaques" <jaquesbosch2@.yahoo.de> wrote in message
news:569b197f.0311021228.1602722d@.posting.google.c om...
> Hi,
> How do you convert DDL statements of SQL Server, which are
> generated by DTS into other database vendors' syntax (IBM
> DB2 or Oracle)?
> Any utility tool?
> Thank you,
> --jaques