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.
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:
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:
When I paste this in a query in SQL-server it runs fine
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.
Code:
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:
Code:
strSQL = dlookup("script", "tblScripts", "[id]=1")
conn.execute strSQL
The error returned is "incorrect syntax near GO"
The script:
Code:
/****** 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
Last edited: