Can you use Access in A-Level Computing Project?

Hi,

I am in the process of creating my project for a-level computer science and I need a software for relational databases.

I know how to use Access but my teacher told me to use MySQL or SQL Server but I find them so confusing that it’s putting me off working on my project.

Is Access okay to be used in a computing project?

Thanks :smile:

Reply 1

999tigger

19

Original post by SophieMay16

Hi,

I am in the process of creating my project for a-level computer science and I need a software for relational databases.

I know how to use Access but my teacher told me to use MySQL or SQL Server but I find them so confusing that it’s putting me off working on my project.

Is Access okay to be used in a computing project?

Thanks :smile:

You could look at the specification or check with the exam board. You should also check the significance of going against your teachers instructions. I would follow what teacher said unless I had good reason to believe I could do something else.

Reply 2

winterscoming

19

Original post by SophieMay16

Hi,

I am in the process of creating my project for a-level computer science and I need a software for relational databases.

I know how to use Access but my teacher told me to use MySQL or SQL Server but I find them so confusing that it’s putting me off working on my project.

Is Access okay to be used in a computing project?

Thanks :smile:

What do you intend to do with Access that you couldn't do with SQL Server or MySQL anyway? Both of those have designer tools for creating your database schema and designing queries. Their UI is fairly similar to Access for that kind of thing, and the underlying concepts are exactly the same (i.e. you have related tables with fields, keys, constraints, etc. ) so if you know what you want to do in Access, then it will be the same in SQL server, just a slightly different UI.

Access has at least one very significant downside which propably makes it unsuitable for A-Level, that there's no way to be able to export a copy of the 'raw' relational database schema SQL, which you'd usually need so that you can Copy+Paste that into your report. (i.e. the script which contains all of your CREATE TABLE stuff). -- for that reason alone I'd imagine Access isn't appropriate for the project.

Also, if you're going to try connecting your app to Access then you would still need to write all of the queries and other SQL commants that your program needs 'by hand' in plain SQL, since those queries will need to be part of your program's code, but again if you have SQL Server or MySQL there's designer tools which can help.

You can import an Access DB into SQL Server to get your tables/schema into SQL Server if you've already built your database using Access, so you could use Access as your designer tool then import it:

SQL Server Wizard tool: https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-access-data-source-sql-server-import-and-export-wizard?view=sql-server-2017
Or doing it through the menus:
https://dba.stackexchange.com/questions/160794/easiest-way-to-import-access-db-accdb-into-sql-server-2016

(Also, which programming language are you using? There may be some tools or libraries on the other side which might help -- for example, VB.NET and C# can automatically turn simple classes directly into database tables within a SQL Server database)

(edited 4 years ago)

Reply 3

SophieMay16

OP

13

Original post by winterscoming

What do you intend to do with Access that you couldn't do with SQL Server or MySQL anyway? Both of those have designer tools for creating your database schema and designing queries. Their UI is fairly similar to Access for that kind of thing, and the underlying concepts are exactly the same (i.e. you have related tables with fields, keys, constraints, etc. ) so if you know what you want to do in Access, then it will be the same in SQL server, just a slightly different UI.

Access has at least one very significant downside which propably makes it unsuitable for A-Level, that there's no way to be able to export a copy of the 'raw' relational database schema SQL, which you'd usually need so that you can Copy+Paste that into your report. (i.e. the script which contains all of your CREATE TABLE stuff). -- for that reason alone I'd imagine Access isn't appropriate for the project.

Also, if you're going to try connecting your app to Access then you would still need to write all of the queries and other SQL commants that your program needs 'by hand' in plain SQL, since those queries will need to be part of your program's code, but again if you have SQL Server or MySQL there's designer tools which can help.

You can import an Access DB into SQL Server to get your tables/schema into SQL Server if you've already built your database using Access, so you could use Access as your designer tool then import it:

SQL Server Wizard tool: https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-access-data-source-sql-server-import-and-export-wizard?view=sql-server-2017
Or doing it through the menus:
https://dba.stackexchange.com/questions/160794/easiest-way-to-import-access-db-accdb-into-sql-server-2016

(Also, which programming language are you using? There may be some tools or libraries on the other side which might help -- for example, VB.NET and C# can automatically turn simple classes directly into database tables within a SQL Server database)

Thanks for your response. I’m struggling because I know how to use Access but I cannot understand how to use SQL Server. I know that I need to start coding so I don’t want to waste lots of time learning how to use a new software when I know how to use Access. I would only be using Access for the relational databases. I am coding in VB.

Reply 4

SophieMay16

OP

13

Original post by 999tigger

You could look at the specification or check with the exam board. You should also check the significance of going against your teachers instructions. I would follow what teacher said unless I had good reason to believe I could do something else.

Thanks for your response. I know that my friends are using Access because we have learnt how to use it in class. I just don’t understand SQL Server at all and it’s putting me off working on the project. I think I will ask my teacher if it’s okay.

Reply 5

SophieMay16

OP

13

It’s just that I don’t have lots of time and I’m kind of stressing. I want to start coding my program but I need to know where I’ll be storing my data.

Reply 6

SophieMay16

OP

13

I’m not sure what you mean by technologies but I’m coding using VB.NET on a laptop/computer and just need somewhere to create, store and use a relational database.

I have researched SQL Server and have some idea about creating a database but I’m unsure on how to create a relational database and also how to access the database from VB.

Reply 7

winterscoming

19

Original post by SophieMay16

I’m not sure what you mean by technologies but I’m coding using VB.NET on a laptop/computer and just need somewhere to create, store and use a relational database.

I have researched SQL Server and have some idea about creating a database but I’m unsure on how to create a relational database and also how to access the database from VB.

Relational databases are just tables which are linked together using foreign keys.

If you want a really primitive, low-level DIY solution, then you can use SqlConnection to connect and then SqlCommand to run SQL commands..

But.... If you're using VB.NET then an easy (cooler) way to do this would be using a Microsoft Library called Entity Framework which will do the database creation stuff for you (It will also help with Select/Insert/Update/Delete with the data if you want it to. Or you can just use it to Create the DB then do all the data stuff with raw SQL queries and SqlCommand if you want to...).

The first cool part about it is having classes in your VB program which connect directly to your DB/tables. The coolest part is that it figures out all the foreign key relational stuff for you, so a relationship in your VB program turns into a table relationship in the database. Writing some simple VB Classes using VB Properties tends to be quicker/easier than clicking through SQL server and setting up all the keys/attributes/constraints "by hand".

Try this...
Install "Developer" edition of SQL Server (that's basically the full version for free) -- https://www.microsoft.com/en-gb/sql-server/sql-server-downloads
(The Basic install is fine. But then make sure you also install "SSMS" -- SQL Server Management Studio so you can look at the database later. It will need you to reboot the computer once it's installed).
- If it asks you for an instance name, let it give you the default called MSSQLSERVER, don't change anything there.
- If it asks you anything about Authentication, it should already have 'Windows Authentication' set, don't change that, Windows Authentication is easiest... but there might be a button where it lets you "Add Current User", so it's useful to click that to make sure you have full admin permissions on it.

VB will require a 'Connection String' which you can use to connect to your database. That's just a fairly standard thing for any programming language and any database really. If you install SQL Server as I've just described, then your Connection String will be
"Server=.;Database=MyLittleSqlDatabase;Trusted_Connection=True"

•

Server=. means the database server it's connecting to is the default SQL Server "instance" (the windows process) running on your computer (the default one is always called MSSQLSERVER). If you've already installed it and used a different name than MSSQLSERVER (SQL Management Studio will tell you the name at the top), then it will be ".\Name" instead. For example, if you installed SQL Express then I you'll have ended up with one called ".\SQLEXPRESS" instead.

•

Database=MyLittleSqlDatabase is (you probably guessed...) the name of the database running inside the SQL Server Instance, Try it with a database name which doesn't exist yet, or it'll may throw an error. When the database doesn't exist, Entity Framework will always create it.

•

Trusted_Connection=True means it'll connect as your Windows User which should already have full admin permission -- i.e. when you're running the program, it'll use your windows login automatically.

Even if you don't use Entity Framework to do the Query/Modify stuff with your data and you just use the plain VB SqlConnection/SqlCommand objects, you'll still need that connection string.

When you've got SQL Server installed. Do this in Visual Studio / VB:

1.

Create a New VB.NET Project in Visual Studio (Console Project just to make this super quick and easy, but a Windows Forms project uses exactly the same code)

2.

In Visual Studio, go to the 'Tools' Menu at the top. Navigate to 'NuGet Package Manager'. choose 'Manage NuGet Packages for Solution'

3.

A screen called NuGet Package Manager will appear. There's a tab at the top called Browse. Search for Entity Framework -- click on it. click Install.

That gives you the libraries you need to create and connect to a database in SQL server in that project. (In the VB code itself it's called System.Data.Entity)

In the code, you can then create classes for each of the tables you want... each Class is a table. Each Property is a field on that table (It's literally that easy..).
something like this -- assuming you did the NuGet bit above in a VB.NET console project, then this will create a little database of Departments and Employees:

Department.vb

' This is just a normal VB class, If you feed it to Entity Framework it will 
' connect the class to a database table
Public Class Department
    ' Entity Framework assumes that Id is your primary key
    Property Id As Integer

    ' These are just plain old data fields....
    Property Name As String
    Property Manager As String

    ' Entity Framework assumes that ICollection is a one-to-many relationship.
    ' Employee class has a foreign key "DepartmentId" Integer for this to work!
    Property Employees As ICollection(Of Employee) = New List(Of Employee)

End Class

Employee.vb

Public Class Employee
    ' Entity Framework will assume that this is the primary key to the Employee table
    Property Id As Integer

    ' Plain old data fields in the database
    Property Name As String

    ' a questionmark is useful because DateTime normally doesn't allow nulls. 
    ' But the questionmark means "Nullable DateTime" in the database. 
    Property Dob As DateTime?

    ' Entity Framework will assume that this is the foreign key to the Department Table
    Property DepartmentId As Integer
End Class

The magic happens in a part of Entity Framework called DbContext and DbSet. DbContext is a database. DbSet is a table. This is how you tie it together:

MyDbContext.vb

Imports System.Data.Entity

' This is the class which derives from Entity Framework to create a database
'
' Get used to manually deleting this database from SQL Server because every time 
' you change one of the classes you will need to delete the whole database and let 
' Entity Framework create it again fresh.
' Otherwise it complains about a "model context change".
Public Class MyDbContext
    ' DbContext contains loads of EntityFramework code that connects to the DB, 
    ' and reflects the data schema into VB. 
    Inherits DbContext

    ' The Constructor.  If the database doesn't exist when this runs, 
    ' Entity Framework will create it
    Public Sub New()
        ' This is the SQL Server connection string for MyLittleDatabase
        MyBase.New("Server=.;Database=MyLittleDatabsae;Trusted_Connection=True")
    End Sub

    ' Entity Framework looks at every "DbSet" and turns those into a table
    Property Employees As DbSet(Of Employee)
    Property Departments As DbSet(Of Department)

End Class

Lastly... your main console app, or your windows form, or whatever -- just to actually use that code above, create a database, and a quick example of pushing some data into it.

Module1.vb - console app example.

Module Module1
    Sub Main()
        ' Connects to the database and maybe creates the database if it doesn't exist.  
        ' It's fine to keep connecting/closing over and over.
        ' Any data-related stuff needs to happen when the connection is open.
        ' Not after it closes.
        Using db As New MyDbContext

            ' Create a couple of simple VB objects -- a new employee and a department
            Dim emp As Employee = New Employee
            Dim dept As Department = New Department

            ' Adds the employee to the department.  These are still just VB objects
            dept.Employees.Add(emp)

            ' Adds the department to Entity framework's "cache" for the Departments table
            db.Departments.Add(dept)

            ' Saves (commits) the Entity Framework changes into the database  
            ' i.e. creates the Employee and Department.
            db.SaveChanges()
            ' Now check the database in SQL management studio
            ' it will have 2 tables called Employee and Department.
            ' Each with one row in them

        ' "End Using" Closes the DB connection.  Like the end of a Transaction. 
        ' Everything with the data (read or modify) needs to happen before this. 
        End Using
    End Sub
End Module

Try to copy/paste/run the code, see what happens. You'll find a few VB examples using Entity Framework, although most are in C#. C# examples are still helpful if you can get past the syntax differences. The C# language is the exact same programming language as VB, it just uses a different (less-wordy) syntax.

Entity Framework has a lot of its own rules so just as you have to get used to VB compiler errors, you'll get used to the same common Entity Framework errors (Exceptions) when you make a mistake too. There are probably half a dozen different things mistakes which are easy to make. It's usually going to be something like getting a Property wrong, Or forgetting to delete the database after you change something, or writing a SQL query that breaks, or having an empty field which isn't null, etc.

Some of it is based on "magic" naming styles or looking at Class' Property types as well. e.g. a few of the most common ones:

•

Having an Integer Property called "Id" will magically be the primary key for that Class' corresponding table.

•

Having another Integer Property like "TableNameId" will magically be a foreign key out to another Table/Class called TableName.

•

Having a list Property based on ICollection(Of SomeTable) will assume a one-to-many to the Class/Table "SomeTable". (Needs a foreign key integer property on the other table...)

(edited 4 years ago)

Can you use Access in A-Level Computing Project?

Quick Reply

Related discussions

Latest

Trending

Trending

Articles for you