SQL Scripts and run SQL in Batch Mode

[BD]

Is there any utilities that make a SQL Script for CREATE TABLE and set
CONSTRAINT for index fields and relationship compatible with Access 97
and 2000? Like open a *.mdb and create a script for the tables and
relationship with tables automatically.

It's possible to run that's scripts in Batch Mode like the SQL Server
do or we must run one of each time?

[]'s
BD

 

[Pat Hartman\(MVP\)]

I used to have a product that would produce DDL for Access tables but the
company seems to be out of business. There are a couple of ways to solve
your problem. If you already have the queries written, you can create a
procedure to run all of them or you can create a macro to run them. I don't
use macros and I don't recommend them because they have no error handling.
If there is a problem, the database just stops. If you are not comfortable
writing code, create the macro and then use the tool that converts it to
code. Then delete the macro.

If you don't have the DDL but you do have existing tables of the correct
structure, you can use the TransferDatabase Method/Action to copy the tables
to a different database.

 

[BD]

I have the tables of the correct structure but I don't want to use the
TransferDatabase to copy the tables, it's just for help the
development. Here it will be very useful a software that can write the
DDL script that I will use in code to create the tables and
relationship. Indeed is more easer to create the tables through the
interface of Access but after I want to create them within the code, I
don't like to use macros too, to more quicker make the development. In
the procedure I put the SQL DDL in each query and I must execute one
of each time, I think.

[]'s
BD

 

[Larry Linson]

Is there any utilities that make a SQL Script
for CREATE TABLE and set CONSTRAINT
for index fields and relationship compatible
with Access 97 and 2000? Like open a *.mdb
and create a script for the tables and
relationship with tables automatically.

It's possible to run that's scripts in Batch Mode
like the SQL Server do or we must run one
of each time?

With the features of Access, it is very likely that someone here can suggest
a _better_ way to solve most problems than using DDL to create tables -- but
only if you explain what you are trying to accomplish. So far, you have
described how you want to accomplish it, and defending that approach when
anything else was suggested, but you have not said what you are trying to
do.

I've used Access since the very early days of Access 1.0 and I have solved a
lot of business problems with it, and, you know, I have never faced a
business problem for which the best, or even a realistic, solution included
building tables and relationships with DDL -- not one. You aren't likely to
get as good help here on Jet DDL as on many other subjects, simply because
of the fact that most of us never use it, and most of the rest use it
rarely.

So, just for a few posts, let go of the idea that you _have to_ use DDL,
that you _have to_ create the tables and relationships anew for each run,
tell us what you are trying to accomplish from a business or requirements
point of view and see if we can't arrive at a good solution.

Oh, one more thing, tell us where the data is coming from that you intend to
manipulate in the tables and relationships you create -- is it coming from
some electronic source, or does some key it in, or ???

Larry Linson
Microsoft Access MVP

 

[BD]

I intend to do a multi user application with various databases in back
end where some are common to all others databases because they will
contain general tables configuration, etc. In the front end I will
only use linked tables from some back end databases, queries, forms,
reports and modules. I don't want expose the template(s) database(s)
from where I easily could use the TransferDatabase Method/Action to
create the back end databases. In that case I think that the only way
to resolve that is create the databases from code using DDL, DAO or
ADO.
The data come all from Access 97 databases.
I will need save the version of the my DB's to compare if I will need
to upgrade the back end DB, case I add or remove some fields, indexes
and relationships.

[]'s
BD