executing MSSQL-scripts from Access (1 Viewer)

[Zakraket]

I'm building an Accessclient which works on a SQL-backend (2008r2).

I'm working on a VBA-module that updates the backend in steps, so that it's easy to deploy from DEVELOP to TEST, to PRODUCTION: the client will update the backend with the necessary changes (when possible if changes not too complex)

It's not that difficult, I open a ADODB-connection to the right database and then f.e.

conn.execute "AlTER TABLE dbo.TBLUSER ADD SHORT nvarchar(3)"

However, I doing the major work of building tables on SQL-server directly. I'm looking for a way to execute scripts generated by SQL-server from then access-client.

So I thought I would create a table tblScripts in the client that holds all scripts needed to do the proper changes on the backend.

F.e. a SQL-script to create a new table with constraints, foreignkeys etc. So, when I make a change on the developdatabase, I save the SQLscript executed by SQL-server to a file, then put that script in the table and then:

strSQL = dlookup("script", "tblScripts", "[id]=1")
conn.execute strSQL

However, the exact script that is returned by the Dlookup won't run from Access, but when I paste the Dlookup-output from Access in SQL-server in a new Query it runs fine.
The error returned is "incorrect syntax near GO"

The script:

/****** Object:  Table [dbo].[TBLBRIDGEPART_META]    Script Date: 02/26/2015 11:57:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TBLBRIDGEPART_META](
    [BRIDGEPART_META_ID] [int] IDENTITY(1,1) NOT NULL,
    [BRIDGEPART_ID] [int] NULL,
    [META_TYPE] [nvarchar](255) NULL,
    [META_TAG] [nvarchar](50) NULL,
    [META_VALUE] [numeric](18, 3) NULL,
    [ENTEREDON] [datetime] NOT NULL,
    [ENTEREDBY] [nvarchar](255) NULL,
    [UPDATEDBY] [nvarchar](255) NULL,
    [UPDATEDON] [datetime] NULL,
    [DELETED] [bit] NULL,
 CONSTRAINT [PK_TBLBRIDGEPART_META] PRIMARY KEY CLUSTERED 
(
    [BRIDGEPART_META_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TBLBRIDGEPART_META]  WITH CHECK ADD  CONSTRAINT [FK_TBLBRIDGEPART_META_TBLBRIDGEPARTS] FOREIGN KEY([BRIDGEPART_ID])
REFERENCES [dbo].[TBLBRIDGEPARTS] ([BRIDGEPART_ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TBLBRIDGEPART_META] CHECK CONSTRAINT [FK_TBLBRIDGEPART_META_TBLBRIDGEPARTS]
GO

ALTER TABLE [dbo].[TBLBRIDGEPART_META] ADD  CONSTRAINT [DF_TBLBRIDGEPART_META_ENTEREDON]  DEFAULT (getdate()) FOR [ENTEREDON]
GO

ALTER TABLE [dbo].[TBLBRIDGEPART_META] ADD  CONSTRAINT [DF_TBLBRIDGEPART_META_DELETED]  DEFAULT ((0)) FOR [DELETED]
GO

When I paste this in a query in SQL-server it runs fine

 

[spikepl]

You need to break it up - access does one statement in one go

 

[Zakraket]

You need to break it up - access does one statement in one go

Tnx. I need to break it up for every line?

Like:

- SET ANSI_NULLS ON

- GO

- SET QUOTED_IDENTIFIER ON

- GO

- CREATE TABLE [dbo].[TBLBRIDGEPART_META](
    [BRIDGEPART_META_ID] [int] IDENTITY(1,1) NOT NULL,
    [BRIDGEPART_ID] [int] NULL,
    [META_TYPE] [nvarchar](255) NULL,
    [META_TAG] [nvarchar](50) NULL,
    [META_VALUE] [numeric](18, 3) NULL,
    [ENTEREDON] [datetime] NOT NULL,
    [ENTEREDBY] [nvarchar](255) NULL,
    [UPDATEDBY] [nvarchar](255) NULL,
    [UPDATEDON] [datetime] NULL,
    [DELETED] [bit] NULL,
 CONSTRAINT [PK_TBLBRIDGEPART_META] PRIMARY KEY CLUSTERED 
(
    [BRIDGEPART_META_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

- GO

etc

Lot of work (for large scripts). Maybe I can cut it up with VBA automatically and put it in records.

I'll test a bit with this coming week, tnx

edit: hmm...probably I don't need to use the GO if I run every line seperatly (not a SQL-expert ). Maybe I can use the GO to cut up scripts automatically.

 

[spikepl]

I'm no expert, but if you send a separate GO the poor thing will just get confused. I reckon you need to feed it executable statements one at a time to an .Execute command. The one with constrains I have no idea. Try the few relevant permutations. If that fails, there is an AWF VIP here totally into this kind of stuff so try PM him: stopher

 

[Zakraket]

It works, I can put the whole "create table"-section in an execute (have to remove the newlines though) and the table is created.
After that I can execute the constraints seperatly

Means a bit of work on the scripts though (and this is just a simple one). I could perhaps automatically remove the newlines (with REPLACE) and use the "GO" to cut up the seperate commands (maybe using the SPLIT(x, "GO" function or something like that).

 

[kentgorrell]

When I create a table in SS from VBA, I create the table with just the PK

strSQL = "CREATE TABLE dbo." & strTableName _
                        & " (" & strFieldName _
                        & " INT IDENTITY(1,1) NOT NULL" & vbNewLine _
                        & " CONSTRAINT " & strConstraintName _
                        & " PRIMARY KEY CLUSTERED)"

cnn.Execute strSQL

then create the columns and then apply the constraints one at a time.

This simplified approach makes troubleshooting easy.

the only exception is for Bit (or other columns with Not Null and a Default) where I always apply the default and the Not Null when I create the column. Note the " WITH VALUES" to populate existing records with the default

strSQL = "ALTER TABLE " & strTableName & vbNewLine _
                           & " ALTER COLUMN [" & strFieldName & "]" & vbNewLine  _
                           & " Bit NOT NULL" _
                           & " CONSTRAINT " & strConstraintName & " DEFAULT " & strDefault & " WITH VALUES"

As you can probably tell, I have a table of tables and a table of columns in the FE and run this schema update process first against Dev data, then test data before it moves onto production. This makes the SDLC thoroughly reliable.