Adding Fields to Remote SQL Server

[PiedPiper70]

I'm sure this is easy but I just can't get it - would appreciate some help. I need to add fields to tables on an SQL Server but from an Access front end.

I presume I need to send an "ALTER TABLE........." command, but how?

Thanks very much

 

[Minty]

According to Microsoft

The Microsoft Access database engine does not support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Access databases. Use the DAO Create methods instead.

So the only way I could see you achieving this would be DOA or to call a stored procedure on the server to do it for you...

 

[kevlray]

We just started using SQL Server for the back end of the database. I used Microsoft SQL Server Management Studio (MSSMS) to alter any tables (had to write a query). I believe that MSSMS is a free download, I think it comes with the Express versions of MS-SQL. You will need the correct rights to the database to do the changes.

 

[PiedPiper70]

Minty - thanks. I thought I'd try Stored Procedures as I'd used them before. I assumed I could create one to add a field to a table and call it from Access. So I created one like this:-

USE [LCSArchive]
GO
/****** Object: StoredProcedure [dbo].[spAddField] Script Date: 01/12/2014 16:36:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spAddField] @table_name nvarchar(30),@column_name nvarchar(30),@column_definition nvarchar(30)
AS
ALTER TABLE table_name
ADD column_name column_definition

but it doesn't run if I try to execute it locally. It errors with "Cannot find the object "table_name" etc.

Any ideas please?

 

[PiedPiper70]

We just started using SQL Server for the back end of the database. I used Microsoft SQL Server Management Studio (MSSMS) to alter any tables (had to write a query). I believe that MSSMS is a free download, I think it comes with the Express versions of MS-SQL. You will need the correct rights to the database to do the changes.

I can do this but I am trying to automate the process to make my life easier

 

[Minty]

To execute it from access you need to make sure you have set permissions on the SP to allow users to run it, and that they also have permissions to update the table.

Running it from Access do something like ;

Set cn = New ADODB.Connection
cn.Open = "LCSArchive"
cn.Execute ("EXEC dbo.spAddField '" & Yourparameterhere & "' , '" & secondparameter & "' , '" & thirdParam & "' " )
cn.Close
Set Cn = Nothing

 

[PiedPiper70]

Thanks Minty, but the problem is that it won't even run doing an EXEC directly on the sql server.

The error is "Cannot find the object "table name" because it does not exist or you do not have permissions.

If it's a permission problem then I don't get it as I'm logged into the Management Studio and can do everything manually. Any thoughts?

 

[Rx_]

The rights and permission make a big difference.
Are you going to program something so the user can add a field to a table as part of a process?
Just wondering why you wouldn't use one of the many tools that make it so easy to add a field.
When you are logged into SSMS, you might be logged in as an SA rather than a user?

 

[PiedPiper70]

Hi Rx. To cut a long story short, I currently maintain 2 identical systems, both of which use both an Access BE and an SQL Server BE. Every so often I need to make identical schema changes to all 4 back ends, so I'm trying trying to streamline that process. And those 4 backends could rise to 6, or even 8, by next year.

I'm sure you would agree that it would be nice to try and solve this issue.

So forgetting the Access FE for now, even within the SSMS I can't get the Stored Proc to run, but if I construct a simple line of SQL, with the parameters hard wired, then it executes just fine. So any ideas why that might be?

 

[Minty]

Can you post you whole SP ? I think the problem is that you will need to EXEC the ALTER statement within the SP - you can't use GO.

 

[PiedPiper70]

Minty

I did. See my 2nd post at 04:44

 

[Minty]

Okay - Sorry I though there might be more to it...
This is Air code but should get you going (Untested)

USE [LCSArchive]
GO
/****** Object: StoredProcedure [dbo].[spAddField] Script Date: 01/12/2014 16:36:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAddField] @table_name nvarchar(30),@column_name nvarchar(30),@column_definition nvarchar(30)
AS

Dim sExecCode as nvarchar(MAX)

SET sExecCode = 'ALTER TABLE ' + table_name + ' ADD ' + column_name + ' ' + column_definition

EXEC (sExecCode)

 

[PiedPiper70]

Minty

Thanks, but I don't understand what you're doing here. Firstly, what is AIR code?
Secondly can you explain exactly what is going on - it looks like a mixture of some SQL and some VBA added together? Is this something I can run from Access without bothering with a stored procedure, or what?

What am I missing?

 

[Minty]

Sorry Air code means it's not tested, written without the aid of a safety net...
And you are right I've messed up Between VBA And SQL. (I've been coding VBA all day, they don't mix well off the top of your head) It should be all in your SQL SP.

This works - I've now had time to test it

ALTER PROCEDURE [dbo].[spAddField] @table_name nvarchar(30),@column_name nvarchar(30),@column_definition nvarchar(30)

AS
BEGIN
	
DECLARE 	@sExecCode as nvarchar(MAX)

SET  @sExecCode = 'ALTER TABLE ' + @table_name + ' ADD ' + @column_name + ' ' + @column_definition

EXEC (@sExecCode)

END
GO

 

[PiedPiper70]

Brilliant - it works. Thanks very much Minty. Now I can create a little widget that will do all my table updates.

Cheers