Create Tables In Access 2010 Using SQL Commands

Advertisement

MS Access provides a number of ways to create tables and queries. Using simple SQL commands, you can create tables, queries, forms, and reports. SQL stands for Structured Query Language that is being used extensively for creating, handling and manipulating RDBMS. Its syntax is very easy to comprehend, but becomes complex when you try to handle the colossal database. In this post we will define a simple query with SQL commands, which eventually creates a simple table.

To start off with, Launch Access 2010 and give database an appropriate name. Once done, click Create.

1 Create table

It will open a blank table, close it in order to remove it. Now, navigate to Create tab and click Query Design.

2 query design (1)

Upon click you will see Show Table, close this dialog box.

3 cose query

Now, head over to Design tab and click Data Definition from Query Type group.

4 data def

It will open Query tab1. Here, you can write SQL commands. Since we want to create simple table, we will write SQL commands to create table and important field labels with their respective data types.

CREATE TABLE  Student
(
ST_ID int PRIMARY KEY,

Name CHAR(255),

Course CHAR(255),

Marks INTEGER,

Grade CHAR(255),

Phone INTEGER,

Present YESNO);

In SQL commands, we started off with CREATE TABLE, this will create a table with the name specified Student. The first field label ST_ID with data type int (Integer), we have also set it as PRIMARY KEY (unique identifier and non-repetitive). The next field label Name has data type CHAR(255) or Text. The last field label Present has data type YESNO, which confines your database users to enter either Yes or NO.

As you can see in SQL commands, we have enclosed field labeling & data type section in parenthesis and placed semicolon at the end. Syntax error would erupt if you don’t comply with these standards.

5 write SQL

Now, save the query by right-clicking Query1 tab and hit Save. Specify an appropriate name for it.

6 save query

You will see the newly created query through Data Definition in the left sidebar of the database window.

9 create table SQL query (1)

Now run this query, a message window will pop-up, asking you to confirm the action. Click Yes to continue.

10 info message data def

Upon click, it will do as specified in SQL command; it will create a table by the name of Student, as shown in the screenshot below.

11 Student table created

On opening newly created table, you will see all the field labels as specified in SQL commands. As shown in the screenshot below that it has all the column labels; ST_ID, Name, Course, Marks, Grade, Phone, and Present.

12 table open

Now switch to Design view and check the Data types. Here, you can apply new constraints for table data fields and add more field labels etc.

13 design view

You can also check out previously reviewed guides on Import phpmyadmin database to Access 2010 & Defining Relationships and Query Interconnection

Advertisement
  • great tutorial……thanks alot 🙂

  • Aldahbra

    This is fantastic, I just didn’t know how to get started, nothing seemed to go that basic. Just the job, well done. Now the rest of my lecture notes makes sense.

  • Pingback: Create Hyperlink Field In Access 2010()

  • Pingback: Use Expression Builder In Access 2010 For Query, Forms, Reports()

  • Rajukri

    Thank you Usman. This was helpful. I was struggling to get to the SQL view and this post bailed me out. 🙂

  • Haidar

    Great job man. It is something  that really deserves to be called a true, compact and very very precise tutorial. Very elegant. Got me up and running in seconds.

  • Nakita

    very very helpful. ut now tht i have closed the query i want to reopen it to make changes but i don’t know how to.

  • Les F

    Thank You, I lost my way through the menu system and this post pointed me in the right direction.

  • Dennis

    Thanks man! Without you is wouldn’t be able to make one in SQL

  • Muhammad Ismael

    Very helpful for a beginner, thank you for sharing. God bless you.

  • Brijesh Jajal

    SQL Datatypes and examples are clearly stated here…. Good one…

  • pal

    Getting Error – Query must have at least one destination field.

    • codervnjp

      Go to “Design” –> “Compact and Repair Database Tools”
      Then you can run your query!!

  • Lakhan Singh

    Thank You, I lost my way through the menu system and this post pointed me in the right direction.

  • Gustavo Pacheco

    Thank you for this, the information is great. This is just what I needed. I was completely stuck on how to this, and two hours later I completed my first table. Thank you.

  • KDP

    MYDATE DATE, MYCURRENCY CURRENCY, MY BIGTEXT VARCHAR(255)

  • Reyes

    This is awesome, thanks!

  • Tebogo

    Thanks

  • Laszlo Illyes

    If we have Decimal number, how can I define in SQL command? I try it with Decimal, Decimal(2), Decimal(10,2), but no result.

    • RICHARD

      INTEGER(10,2)

      • Mr. Hand

        Doesn’t work.

  • Encik Kopi O®

    Thanks a lot..really helpful

  • Micro

    This has solved my problem … thank you 😀

  • Jim Steffel

    Nice thanks.

  • cybermario

    is possible to run northwind.sql (for sql server) to get this to database on access? thanks in advance

  • joanne bushell

    Please could someone tell me the meaning of the label commands for Aa and ab (what is the difference?) in Microsoft Access 2010.E.G WHEN ADDING A LABEL IN FORM FOOTER OR ENHANCING

  • Andrew

    Using your suggestion above, is it possible to update the field attributes such as Required = Yes to make the field mandatory (in the script) ? Thanks!