Create a new table on SQL server from a form with VBA (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 06:47
Joined
Jun 7, 2018
Messages
100
Hi everyone!
I hoped someone could advice me with something on the old trusty forum :)

I successfully linked my Access front-end with an Sql server and everything checks out.
After some playing around i was wondering if it's possible to use VBA on my FE form that creates a new table on my SQL server.

Is this possible at all? (of course permission wise i will solve that)
Because i can run a SQL command in VBA and create tables and such, but how do i adres the SQL server, and then ofcourse LINK my new table.

Thanks in advance everyone for your thoughts!

Bruce
 

Minty

AWF VIP
Local time
Today, 14:47
Joined
Jul 26, 2013
Messages
7,529
You can probably use a pass through query along the lines of;

SQL:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tb_AuditLog](
    [Audit_ID] [int] IDENTITY(1,1) NOT NULL,
    [User_ID] [int] NULL,
    [DatabaseVersion] [varchar](50) NULL,
    [ProcessName] [varchar](150) NULL,
    [EventTime] [datetime2](7) NOT NULL,
    [OtherData] [varchar](150) NULL,
    [WinPCName] [varchar](50) NULL,
 CONSTRAINT [PK_tb_AuditLog] PRIMARY KEY CLUSTERED
(
    [Audit_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tb_AuditLog] ADD  CONSTRAINT [DF_tb_AuditLog_EventTime]  DEFAULT (getdate()) FOR [EventTime]
GO

Then you can create a link using the tabledefs property collection
 

Ranman256

Registered User.
Local time
Today, 09:47
Joined
Apr 9, 2015
Messages
3,641
No. You must alter in SQL manager.
 

Minty

AWF VIP
Local time
Today, 14:47
Joined
Jul 26, 2013
Messages
7,529
@Ranman256 Sorry to disagree, but I have just tried and after adjusting the query syntax slightly(remove the Go Statements) It works fine from a pass-through.
 

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
2,769
No. You must alter in SQL manager.
You can do just about anything in a pass through. Any time it seems like you can't, create a stored proc on sql server and just pass through query exec it
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:47
Joined
Feb 19, 2002
Messages
30,120
This is a pretty scary question. Why would you ever want to do this? In most companies I consult with, I don't even have permission to modify the production database so it would be a cold day in hell before the DBA would ever grant this permission to a user.

When you get into the real world of RDBMS', you have a minimum of three environments.
Production - the real work of the company gets done here.
QA - quality assurance - final testing gets done here
Test - development work gets done here

The normal progression of changes is Test, QA, and then finally Production. I have one client with EIGHT, count em' EIGHT staging libraries BEFORE Production. They do DOD work and also provide the President's helicopter so they're really anal about testing.

If you were to actually be making tables on the fly, how would you ever coordinate testing?
 

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
2,769
When you get into the real world of RDBMS', you have a minimum of three environments.
Production - the real work of the company gets done here.
QA - quality assurance - final testing gets done here
Test - development work gets done here
You forgot one. It's the one each company segregates to the Finance Department who knows just enough SQL server to run queries that make the lights flicker. 🙂
 

isladogs

CID VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
14,022
I have done this on numerous occasions (as in Minty's example) when releasing new versions of the FE to clients and where I needed to add new SQL tables or modify the design of existing SQL tables in the client's SQL BE.

Having said that, it is a lot easier to do so using a SQL script that can be supplied as part of the FE update and which runs automatically during installation of that update.
I also add code lines to the script to check whether the script has already been run so that changes are not overwritten
For example:
SQL:
USE [SDABE]
GO

/****** Object:  Table [dbo].[tblBookableEquipment]    Script Date: 28/12/2015 21:06:50 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[tblBookableEquipment]') AND type in (N'U')
)
    BEGIN
        SET ANSI_NULLS ON

        SET QUOTED_IDENTIFIER ON

        SET ANSI_PADDING ON

        CREATE TABLE [dbo].[tblBookableEquipment](
            [ItemID] [int] IDENTITY(1,1) NOT NULL,
            [ItemName] [varchar](20) NULL,
            [Description] [varchar](30) NULL,
            [Bookable] [bit] NOT NULL,
            [Info] [text] NULL,
         CONSTRAINT [PK_tblBookableEquipment] PRIMARY KEY CLUSTERED
        (
            [ItemID] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]

        SET ANSI_PADDING OFF

        ALTER TABLE [dbo].[tblBookableEquipment] ADD  CONSTRAINT [DF_tblBookableEquipment_Bookable]  DEFAULT ((0)) FOR [Bookable]
    END
GO


 /****** Object:  Table [dbo].[tblEquipmentBookings]    Script Date: 28/12/2015 21:06:50 ******/
--create new table if it doesn't exist

IF NOT EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[tblEquipmentBookings]') AND type in (N'U')
)
    BEGIN
        SET ANSI_NULLS ON

        SET QUOTED_IDENTIFIER ON

        SET ANSI_PADDING ON

        CREATE TABLE [dbo].[tblEquipmentBookings](
            [BookingID] [int] IDENTITY(1,1) NOT NULL,
            [ItemID] [int] NOT NULL,
            [ItemName] [varchar](20) NULL,
            [BookingDate] [datetime] NULL,
            [LessonID] [int] NULL,
            [Period] [int] NULL,
            [StartTime] [datetime] NULL,
            [EndTime] [datetime] NULL,
            [AllDay] [bit] NOT NULL,
            [TeacherID] [varchar](5) NULL,
            [ClassID] [varchar](10) NULL,
            [RoomID] [varchar](10) NULL,
            [SubjShort] [varchar](10) NULL,
            [Activity] [varchar](30) NULL,
         CONSTRAINT [PK_tblEquipmentBookings] PRIMARY KEY CLUSTERED
        (
            [BookingID] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]

        SET ANSI_PADDING OFF

        --add referential integrity
        ALTER TABLE [dbo].[tblEquipmentBookings] ADD  CONSTRAINT [DF_tblEquipmentBookings_AllDay]  DEFAULT ((0)) FOR [AllDay]

        ALTER TABLE [dbo].[tblEquipmentBookings]  WITH CHECK ADD  CONSTRAINT [FK_tblEquipmentBookings_tblBookableEquipment] FOREIGN KEY([ItemID])
        REFERENCES [dbo].[tblBookableEquipment] ([ItemID])
        ON UPDATE CASCADE
        ON DELETE CASCADE
        
        ALTER TABLE [dbo].[tblEquipmentBookings] CHECK CONSTRAINT [FK_tblEquipmentBookings_tblBookableEquipment]
    END
GO


/****** Object:  Table [dbo].[tblBookableRooms]    Script Date: 28/12/2015 21:32:27 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[tblBookableRooms]') AND type in (N'U')
)
    BEGIN
        SET ANSI_NULLS ON

        SET QUOTED_IDENTIFIER ON

        SET ANSI_PADDING ON

        CREATE TABLE [dbo].[tblBookableRooms](
            [RoomID] [varchar](10) NOT NULL,
            [RoomDescription] [varchar](50) NULL,
            [ITRoom] [bit] NOT NULL,
            [TimetabledRoom] [bit] NOT NULL,
            [Bookable] [bit] NOT NULL,
            [RoomInfo] [text] NULL,
         CONSTRAINT [PK_tblBookableRooms] PRIMARY KEY CLUSTERED
        (
            [RoomID] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


        SET ANSI_PADDING OFF

        ALTER TABLE [dbo].[tblBookableRooms] ADD  CONSTRAINT [DF_tblBookableRooms_ITRoom]  DEFAULT ((0)) FOR [ITRoom]         

        ALTER TABLE [dbo].[tblBookableRooms] ADD  CONSTRAINT [DF_tblBookableRooms_TimetabledRoom]  DEFAULT ((0)) FOR [TimetabledRoom]

        ALTER TABLE [dbo].[tblBookableRooms] ADD  CONSTRAINT [DF_tblBookableRooms_Bookable]  DEFAULT ((0)) FOR [Bookable]
    END
GO


/****** Object:  Table [dbo].[tblRoomBookings]    Script Date: 28/12/2015 21:40:09 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[tblRoomBookings]') AND type in (N'U')
)
    BEGIN
        SET ANSI_NULLS ON

        SET QUOTED_IDENTIFIER ON

        SET ANSI_PADDING ON

        CREATE TABLE [dbo].[tblRoomBookings](
            [ID] [int] IDENTITY(1,1) NOT NULL,
            [RoomID] [varchar](10) NOT NULL,
            [Timetabledroom] [bit] NOT NULL,
            [BookingDate] [datetime] NULL,
            [LessonID] [int] NULL,
            [Period] [int] NULL,
            [StartTime] [datetime] NULL,
            [EndTime] [datetime] NULL,
            [AllDay] [bit] NOT NULL,
            [TeacherID] [varchar](5) NULL,
            [ClassID] [varchar](10) NULL,
            [SubjShort] [varchar](10) NULL,
            [Activity] [varchar](30) NULL,
         CONSTRAINT [PK_tblRoomBookings] PRIMARY KEY CLUSTERED
        (
            [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]

        SET ANSI_PADDING OFF

        ALTER TABLE [dbo].[tblRoomBookings] ADD  CONSTRAINT [DF_tblRoomBookings_AllDay1]  DEFAULT ((0)) FOR [Timetabledroom]
        
        ALTER TABLE [dbo].[tblRoomBookings] ADD  CONSTRAINT [DF_tblRoomBookings_AllDay]  DEFAULT ((0)) FOR [AllDay]


         --add referential integrity
        ALTER TABLE [dbo].[tblRoomBookings]  WITH CHECK ADD  CONSTRAINT [FK_tblRoomBookings_tblBookableRooms] FOREIGN KEY([RoomID])
        REFERENCES [dbo].[tblBookableRooms] ([RoomID])
        ON UPDATE CASCADE
        ON DELETE CASCADE
        

        ALTER TABLE [dbo].[tblRoomBookings] CHECK CONSTRAINT [FK_tblRoomBookings_tblBookableRooms]
    END
GO
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
2,769
During my last job I started to make liberal use of "drop object if exists"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:47
Joined
Feb 19, 2002
Messages
30,120
Colin,
You would certainly need to handle schema changes for products that you sell. I always provide scripts for the DBA to run. The DBA for a client who bought a product I sell would simply never, ever give me access to their production server. Your clients might like you better.

For internal distribution, I would use whatever method the client's DBA requested.
 

isladogs

CID VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
14,022
@Pat Hartman
I release all updates via my website. The DBAs for each client are automatically alerted when an update is available and download the installation file at a convenient time. When this is installed by the DBAs, the SQL script runs automatically along with any other necessary changes. Full documentation is provided so the DBA knows in advance all changes that will be made
On the very unlikely event of problems occurring, another script is provided to reverse any SQL changes made.

I never get involved with the client server in any way at all as there is no need for me to do so.
 

Users who are viewing this thread

Top Bottom