[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

convert .ddl file to an access db file?

how to convert .ddl file to an access db file? what tool to use?
0
she1
Asked:
she1
  • 6
  • 6
1 Solution
 
Arthur_WoodCommented:
a file with the extension .DDL is a file which contains Data Definition Language (CREATE TABLE... etc).

The best you could do would be to EXECUTE that DDL, to create that tables, though this may not be completely possible, since Access amy or may not support the Data Types specified in the .ddl file.  The best you can do is to open the .ddl file with Notepad, and look at the commands that you find.

AW
0
 
she1Author Commented:
yes , i can open the file with notepad and see these command,
but is there a tool run these command to creat the table?
0
 
BillAn1Commented:
You can simply paste them into a query (one at a time!) in Access
However, as Arthur Wood points out, they may not work in Access, if the data types are wrong, or if there is any sophisticated DDL, such as creating indexes, constraints etc. a lot of DDL is database-specific.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Arthur_WoodCommented:
can you paste the DDL from the file, so we can look at it, and then be better able to advise you as to what to do next?

AW
0
 
she1Author Commented:
DROP TABLE AutoStock;

CREATE TABLE AutoStock (make varchar(25), model varchar(25), year integer,
  color varchar(25), mileage integer, price float(10),
  vin integer PRIMARY KEY);

INSERT INTO AutoStock VALUES (
'Porsche', '911', 2001, 'Black', 25, 90000, 123456
);

INSERT INTO AutoStock VALUES (
'Ferrari', 'Maranello', 2001, 'Red', 15, 200000, 24601
);

INSERT INTO AutoStock VALUES (
'Lamborghini', 'Diablo', 2001, 'Yellow', 20, 275000, 80386
);

INSERT INTO AutoStock VALUES (
'Audi', 'TT', 2001, 'Gray', 25, 40000, 5000
);
0
 
Arthur_WoodCommented:
this is clearly DDL for Microsoft SQL Server, and there are some siginificant differences between the datatype is Access and SQL Server (Access does not support varchar text fields, and Access Integers are not exactly that same type as SQL Server Integers - An SQL INTEGER field is more correctly an Access LONG INTEGER Access also does not support the SQL Server datatype 'float')

Also, this contains both DDL (CREATE TABLE ...) and DML (Data Management Language - INSERT INTO...) statements, and these would need to executed SEPARATELY in Access.  Access can only execute ONE SQL statement per file, not a block such as this.


There is no DIRECT way to convert the SQL Server DDL file to the equivalent Access cvommands - there is no single program that can do this, short of executing the DDL file into SQL Server (creating the table and importing the data), and then exporting the SQL Server table as a Access table (using SQL Server DTS - Data Transformation Service), or Linking Access to the SQL Server table, and importing the data into an equivalent Access Table.

AW
0
 
she1Author Commented:
thanks , is it possible to "importing the data into an equivalent Access Table." directly?
"
0
 
Arthur_WoodCommented:
if you use SQL Server DTS, then you can export from SQL Server directly to a table in your Access database, if that is what you mean.


But that DDL is so simple, why don't you simply create the same table in Access (Table/New)???

AW
0
 
she1Author Commented:
but later on, I have a more complicated DB, and i want to convert it again, what can i do?
0
 
Arthur_WoodCommented:
your best approach would appear to the to execute the DDL in SQL Server, and then export the tables to Access.  As I said, there is no direct way to execute the SQL Server DDL in Access, as the datatypes are too differennt, and the SQL Server DDL incluudes MULTIPLE sets of commands, and Access can only execute one command per instruction set.

AW
0
 
she1Author Commented:
actually the database is cloudscape. my i just run the command in access?
or i have to run it at sql server?
0
 
she1Author Commented:
actually , it might be the cloudscape DB. i am not sure it is or not?
0
 
Arthur_WoodCommented:
I doubt that it is Cloudscape, as CloudScape appears to be an object-relational database management system (ORDBMS).  And I doubt that IBM would use the same command architecture as MicroSoft ( and the DataTYpes specified in the DDL are definitely SQL Server DataTypes).

AS I indicated on 8/23 - process the DDL in SQL Server, and then import the tables into Access, and you should be set to go.

AW
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now