Compatible DDL (CREATE TABLE) across different SQL databases?

I'm working on a desktop application that must support (currently) MS Access and SQL Server back ends. The application is under constant development and changes are frequently being made to the database, mostly t开发者_运维百科he addition of tables and views to support new features (but also some DROPs and ALTER TABLEs to add new columns).

I have a system that compiles the DDL into the executable, checks the database to see if the executable has any new DDL that has to be executed, and executes it. This works fine for a single database.

My immediate problem is that SQL Server and Access support wildly different names for data types so a CREATE TABLE statement that executes against Access will not execute against SQL Server (or worse, will execute but create a table with different datatypes).

Is there a method that can be used to create DDL (especially CREATE TABLE commands) that can be executed through ADO against both of these databases without having to craft separate DDL for each provider?

---

Since you are already using ADO, you should look into Microsoft ADOX

This allows you to manipulate structures in a data source using an ADO object model that is independent of the underlying data source type. i.e. without resorting to explicit DDL

Support for ADOX is not guaranteed by any given ADO Provider, and the level of ADOX support may vary even when it is available. But for MS Access and MS SQL Server I think you will find all the capability you require (and quite possibly more!)

---

This can be done using DBX in Delphi.

The following is links to sample code showing how this can be done. http://cc.embarcadero.com/item/26210

---

I had the same problem. I resolved it applying a C preproccessor to the SQL before executing it. The preprocessor includes macros in order to handle the different dbs.

Stefano

---

Did you check

http://db.apache.org/ddlutils/

or

http://publib.boulder.ibm.com/infocenter/wtelecom/v6r1/index.jsp?topic=%2Fcom.ibm.twss.plan.doc%2Fdb_scripts.html