is it possible to see SQL Scripts for Tables in Access ?

[Guest]

HI all,

i want to know if it's possible to see the sql script for tables (not
queries) in access. for example i want to know if access is able to show the
following sql script for a specific table (or is there any way to see them in
this format):

create table branch
( branch_id integer not null PRIMARY KEY,
branch_name varchar(20) not null,
branch_addr varchar(50),
branch_city varchar(20) not null,
branch_phone integer );

insert into branch values
( 10, 'Main', '1234 Main St.', 'Vancouver', 5551234 );

insert into branch values
( 20, 'Richmond', '23 No.3 Road', 'Richmond', 5552331 );

insert into branch values
( 30, 'West Creek', '251 Creek Rd.', 'Sechelt', 5552511 );

any answer will be appreciated.

Thanks
sasan

 

[Randy]

HI all,

i want to know if it's possible to see the sql script for tables (not
queries) in access. for example i want to know if access is able to show
the
following sql script for a specific table (or is there any way to see them
in
this format):

create table branch
( branch_id integer not null PRIMARY KEY,
branch_name varchar(20) not null,
branch_addr varchar(50),
branch_city varchar(20) not null,
branch_phone integer );

insert into branch values
( 10, 'Main', '1234 Main St.', 'Vancouver', 5551234 );

insert into branch values
( 20, 'Richmond', '23 No.3 Road', 'Richmond', 5552331 );

insert into branch values
( 30, 'West Creek', '251 Creek Rd.', 'Sechelt', 5552511 );

any answer will be appreciated.

Thanks
sasan

Have you tried QueryAnalyzer, an utility part of SQL Server?

-Randy

 

[Guest]

hi Randy,
i dont have access to Sql server, but i want to see the Sql scripts for
table designs.

please give me some input on this.
thanks
sasan

 

[Randy]

hi Randy,
i dont have access to Sql server, but i want to see the Sql scripts for
table designs.

please give me some input on this.
thanks
sasan

Well... Are you familiar with Access queries? If you create a query in
Access you could switch to the SQL design-view, which will give you the SQL
code. You can change the Access query type to:

Make Table Query -> to get code for a CREATE TABLE
Append Query -> to get code for an INSERT INTO
And much more...

However that is Access SQL, which syntax may be slightly different for SQL
Server, so you may have to have to tailor down the code for compliancy in
some cases. Not too difficult, but it is better than writing the whole SQL
code from scratch. If you are going to do this quite frequently then you
will definitely need a piece of software to generate these scripts. Try the
above and let me know if that helps.

-Randy

 

[John Vinson]

i want to know if it's possible to see the sql script for tables (not
queries) in access.

Not in native Access. They're not stored in that way.

Somebody may have written VBA code to use DAO functions to loop
through the Tabledef's Fields collection and generate such a script,
but I have not seen an explicit posting of such a function. It would
be handy for upgrading!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tim Ferguson]

i want to know if it's possible to see the sql script for tables (not
queries) in access.

Rumour has it that Visio will (a) import table descriptions from Jet
databases and (b) export SQL CREATE TABLE scripts. I have done (a) but I
have never achieved (b).

Every problem is a programming opportunity..!

All the best


Tim F