Generate MS-ACCESS table using SQL Script generated by SQL Server 
Author Message
 Generate MS-ACCESS table using SQL Script generated by SQL Server

PLATEFORM

UI Tool - VC++ 6.0
Database - MS-ACCESS, SQL Server

Hello,

I am using MS-ACCESS database into my project. I am creating SQL script
using SQL Server. I want to use same script to generate MS-ACCESS database
tables.

For example given script is generated by SQL Server and I want to use same
script to create table into MS-ACCESS database.

----------------------------------------------------------------------------
----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Orders]
GO

CREATE TABLE [dbo].[Orders] (
 [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
 [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [EmployeeID] [int] NULL ,
 [OrderDate] [datetime] NULL ,
 [RequiredDate] [datetime] NULL ,
 [ShippedDate] [datetime] NULL ,
 [ShipVia] [int] NULL ,
 [Freight] [money] NULL ,
 [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
 [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Kindly let me know if any one knows how can it be done.

Thanks in advance.

Aarti



Wed, 31 Mar 2004 16:07:05 GMT
 Generate MS-ACCESS table using SQL Script generated by SQL Server

You can execute a script as a DDL query in Access, but you'll have to
remove all of the T-SQL syntax and stick to Access SQL.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

Quote:

>PLATEFORM

>UI Tool - VC++ 6.0
>Database - MS-ACCESS, SQL Server

>Hello,

>I am using MS-ACCESS database into my project. I am creating SQL script
>using SQL Server. I want to use same script to generate MS-ACCESS database
>tables.

>For example given script is generated by SQL Server and I want to use same
>script to create table into MS-ACCESS database.

>----------------------------------------------------------------------------
>----------------------------
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id,
>N'IsForeignKey') = 1)
>ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
>GO

>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[Orders]
>GO

>CREATE TABLE [dbo].[Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmployeeID] [int] NULL ,
> [OrderDate] [datetime] NULL ,
> [RequiredDate] [datetime] NULL ,
> [ShippedDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [Freight] [money] NULL ,
> [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO

>Kindly let me know if any one knows how can it be done.

>Thanks in advance.

>Aarti



Wed, 31 Mar 2004 21:57:57 GMT
 Generate MS-ACCESS table using SQL Script generated by SQL Server
Another option is using DAO or ADOX

You can also use a util to replay changes - we use www.ssw.com.au/DataPRO

Adam
* Keep up-to-date by attending the monthly meetings at the 'Access/SQL
Server/ASP.Net User Group, Sydney"   www.ssw.com.au/AccessUG


Quote:
> PLATEFORM

> UI Tool - VC++ 6.0
> Database - MS-ACCESS, SQL Server

> Hello,

> I am using MS-ACCESS database into my project. I am creating SQL script
> using SQL Server. I want to use same script to generate MS-ACCESS database
> tables.

> For example given script is generated by SQL Server and I want to use same
> script to create table into MS-ACCESS database.

> --------------------------------------------------------------------------
--
> ----------------------------
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
> GO

> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Orders]
> GO

> CREATE TABLE [dbo].[Orders] (
>  [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
>  [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [EmployeeID] [int] NULL ,
>  [OrderDate] [datetime] NULL ,
>  [RequiredDate] [datetime] NULL ,
>  [ShippedDate] [datetime] NULL ,
>  [ShipVia] [int] NULL ,
>  [Freight] [money] NULL ,
>  [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
>  [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO

> Kindly let me know if any one knows how can it be done.

> Thanks in advance.

> Aarti



Thu, 01 Apr 2004 08:47:49 GMT
 Generate MS-ACCESS table using SQL Script generated by SQL Server
Is there any utility to convert SQL Server generated T-SQL script to
MS-ACCESS compitable .

Aarti.

Quote:
---------Original message------------


> You can execute a script as a DDL query in Access, but you'll have to
> remove all of the T-SQL syntax and stick to Access SQL.

> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446


> >PLATEFORM

> >UI Tool - VC++ 6.0
> >Database - MS-ACCESS, SQL Server

> >Hello,

> >I am using MS-ACCESS database into my project. I am creating SQL script
> >using SQL Server. I want to use same script to generate MS-ACCESS
database
> >tables.

> >For example given script is generated by SQL Server and I want to use
same
> >script to create table into MS-ACCESS database.

>---------------------------------------------------------------------------
-
> >----------------------------
> >if exists (select * from dbo.sysobjects where id =
> >object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id,
> >N'IsForeignKey') = 1)
> >ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
> >GO

> >if exists (select * from dbo.sysobjects where id =
> >object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> >drop table [dbo].[Orders]
> >GO

> >CREATE TABLE [dbo].[Orders] (
> > [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> > [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [EmployeeID] [int] NULL ,
> > [OrderDate] [datetime] NULL ,
> > [RequiredDate] [datetime] NULL ,
> > [ShippedDate] [datetime] NULL ,
> > [ShipVia] [int] NULL ,
> > [Freight] [money] NULL ,
> > [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> >,
> > [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >) ON [PRIMARY]
> >GO

> >Kindly let me know if any one knows how can it be done.

> >Thanks in advance.

> >Aarti



Sat, 03 Apr 2004 19:12:33 GMT
 Generate MS-ACCESS table using SQL Script generated by SQL Server
I don't know of any, but that doesn't mean one doesn't exist. You
could always try a google search.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

Quote:

>Is there any utility to convert SQL Server generated T-SQL script to
>MS-ACCESS compitable .

>Aarti.

>---------Original message------------


>> You can execute a script as a DDL query in Access, but you'll have to
>> remove all of the T-SQL syntax and stick to Access SQL.

>> -- Mary
>> Microsoft Access Developer's Guide to SQL Server
>> http://www.amazon.com/exec/obidos/ASIN/0672319446


>> >PLATEFORM

>> >UI Tool - VC++ 6.0
>> >Database - MS-ACCESS, SQL Server

>> >Hello,

>> >I am using MS-ACCESS database into my project. I am creating SQL script
>> >using SQL Server. I want to use same script to generate MS-ACCESS
>database
>> >tables.

>> >For example given script is generated by SQL Server and I want to use
>same
>> >script to create table into MS-ACCESS database.

>>---------------------------------------------------------------------------
>-
>> >----------------------------
>> >if exists (select * from dbo.sysobjects where id =
>> >object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id,
>> >N'IsForeignKey') = 1)
>> >ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
>> >GO

>> >if exists (select * from dbo.sysobjects where id =
>> >object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> >drop table [dbo].[Orders]
>> >GO

>> >CREATE TABLE [dbo].[Orders] (
>> > [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
>> > [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [EmployeeID] [int] NULL ,
>> > [OrderDate] [datetime] NULL ,
>> > [RequiredDate] [datetime] NULL ,
>> > [ShippedDate] [datetime] NULL ,
>> > [ShipVia] [int] NULL ,
>> > [Freight] [money] NULL ,
>> > [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>> > [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL
>> >,
>> > [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >) ON [PRIMARY]
>> >GO

>> >Kindly let me know if any one knows how can it be done.

>> >Thanks in advance.

>> >Aarti



Sat, 03 Apr 2004 20:32:52 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Generate SQL Script for data of SQL Server table

2. Generate SQL Script for data of SQL Server table

3. Generate access database using SQL Script

4. Generate SQL script for all objects in a database - but using ADO,not SQL-DMO

5. Generate SQL Scripts using T-SQL

6. Generating SQL to Generate Tables from Existing Database

7. Generate ms access from sql server

8. SQL Script to generate SQL Scripts???

9. How to get rid of [dbo].[SquareBrackets] in Generate SQL Scripts in SQL Server 7 EM

10. Generating SQL Scripts out of an SQL Server.

11. Generate SQL script in SQL 2000 for SQL 6.5

12. Generate scripts using SQL-DMO


 
Powered by phpBB® Forum Software