Create sql server table (1 Viewer)

KenHigg

Registered User
Local time
Today, 10:50
Joined
Jun 9, 2004
Messages
13,327
Hi all,

We just got access to sql server database and I need to know the best way to create tables in it. Is it easy in ms access? we have an older version of access, 2010 I think. I can connect to it and see all the system tables but for the life of me can't figure out how to create tables... :(

fyi - it's been a while :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
You can create tables in SQL Server from Access using code, but it’s much easier using SSMS.
 

isladogs

MVP / VIP
Local time
Today, 14:50
Joined
Jan 14, 2017
Messages
18,186
Hi Ken
Various methods of creating SQL Server tables (or edit them):

1. In SQL Server Management Studio (SSMS), select your database, click Tables then Table.
This will open the table design window. Very similar to Access

2. In Access, create your tables as normal making sure you avoid any of the datatypes not accepted by SQL Server (attachment/MVFs/calculated)
When you're ready, you can use the upsizing Wizard to export the tables to SSMS
However this feature was deprecated after A2010

If you have A2013 or later, instead use the SQL Server Import/Export wizard which is more powerful anyway

3. You can use a script to create SQL tables. For example
Code:
USE [SDABE]
GO

/****** Object:  Table [dbo].[tblCountStudentAttendanceMarks]    Script Date: 21/11/2015 00:12:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

--create new table if it doesn't exist
IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[tblCountStudentAttendanceMarks]') AND type in (N'U')
)
	BEGIN
		CREATE TABLE [dbo].[tblCountStudentAttendanceMarks](
			[ID] [varchar](50) NOT NULL,
			[Date] [datetime] NULL,
			[StartTime] [datetime] NULL,
			[Session] [varchar](2) NULL,
			[Day] [varchar](3) NULL,
			[ClassID] [varchar](15) NULL,
			[TeacherID] [varchar](10) NULL,
			[RoomID] [varchar](10) NULL,
			[TotalMarks] [int] NULL,
			[CompletedMarks] [int] NULL,
			[MissingMarks] [int] NULL,
		 CONSTRAINT [PK_tblCountStudentAttendanceMarks] 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
	END
GO

4. You can create a SQL table from Access using a passthrough query.
You will need the connection string details. For example:

Code:
Sub SQLTestCreate()

'Used to create a table in SQL datafile
'If the table already exists it will first be deleted

On Error GoTo Err_SQLTestCreate

    'define table name
    strText2 = "_ABCDEFG_TEST"

    '================================
    'Check link details for SDABE datafile
    strSQL1 = "SELECT tblTableLinks.TableName, tblTableLinks.TableAlias, tblTableLinks.LinkActive, tblTableLinkTypes.LinkType, tblTableLinkTypes.LinkServer, tblTableLinkTypes.LinkDatabase, tblTableLinkTypes.LinkUsernamePassword, tblTableLinkTypes.LinkUsername, tblTableLinkTypes.LinkPassword" & _
                " FROM tblTableLinkTypes INNER JOIN tblTableLinks ON tblTableLinkTypes.TableLinkType = tblTableLinks.LinkType" & _
                " WHERE tblTableLinks.TableAlias='PupilData';"
        
    Set db = CurrentDb
    Set MyRset = db.OpenRecordset(strSQL1, dbOpenSnapshot)
    
    'Identify SQL link parameters for SDABE datafile
    strCurrentLink = "ODBC;DRIVER=SQL Server;SERVER=" & MyRset!LinkServer & ";Database=" & MyRset!LinkDatabase & ";UID=" & MyRset!LinkUsername & ";PWD=" & MyRset!LinkPassword
    'Debug.Print strCurrentLink

    '================================
    'Check if qryTempPassthrough already exists
    N = 0
    
    For Each qdfTemp In db.QueryDefs
        If qdfTemp.Name = "qryTempPassthrough" Then N = 1
    Next
    
    'If query exists . . .delete it!"
    If N = 1 Then
        db.QueryDefs.Delete "qryTempPassthrough"
    End If
    '================================
    
    Set qdfPassThrough = db.CreateQueryDef("qryTempPassthrough")
       
    'Setup query conditions based on datafile connection & type
    qdfPassThrough.Connect = strCurrentLink
       
    'delete table if it already exists
    strSQL1 = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_ABCDEFG_TEST]') AND type in (N'U'))" & _
        " DROP TABLE [dbo].[_ABCDEFG_TEST];"
        
    'Run the query..
    qdfPassThrough.SQL = strSQL1
    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Execute
    
    'define SQL for pass through query
    strSQL2 = "CREATE TABLE [dbo].[_ABCDEFG_TEST](" & _
            " [MyText] [varchar](50) NULL," & _
            " [MyMemo] [text] NULL," & _
            " [MyByte] [bit] NULL," & _
            " [MyInteger] [int] NULL," & _
            " [MyDateTime] [datetime] NULL," & _
            " [MyYesNo] [bit] NULL," & _
            " [MyOleObject] [binary](1) NULL," & _
            " [MyBinary] [binary](50) NULL" & _
            " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];"
 
    'Run the create table query . . .
    qdfPassThrough.SQL = strSQL2
    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Execute
   
    'If strText1 = "" Then
        MsgBox "The SQL table " & strText2 & " has been successfully created.", vbInformation, "SQL table created"
    'Else
    '    MsgBox "The SQL table " & strText2 & " was deleted and then successfully re-created.", vbInformation, "SQL table re-created"
    'End If
   
'Delete the temp query & close the database
   db.QueryDefs.Delete "qryTempPassthrough"
   db.Close

Exit_SQLTestCreate:
    Exit Sub

Err_SQLTestCreate:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbNewLine & _
        "The SQL table " & strText2 & " could not be created.", vbCritical, "SQL table not created"
    Resume Exit_SQLTestCreate

End Sub

As you can see you have plenty of choice! :D
 

KenHigg

Registered User
Local time
Today, 10:50
Joined
Jun 9, 2004
Messages
13,327
Wow - Thanks for both replies. I will see if I can get SSMS first!
 

isladogs

MVP / VIP
Local time
Today, 14:50
Joined
Jan 14, 2017
Messages
18,186
The Express version is a free download and may be the only version you will ever need.
 

KenHigg

Registered User
Local time
Today, 10:50
Joined
Jun 9, 2004
Messages
13,327
I will give it a look - thanks!
 

Users who are viewing this thread

Top Bottom