Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Access to SQL Server: Getting Started with Access Projects

By Kathi Kellenberger,

Access to SQL Server Part 3: Getting Started with Access Projects

Introduction

This is the final article of a three-part series discussing ways to use MS Access as a front end to SQL Server. In the first article, I demonstrated how to use the Upsizing Wizard to create a new SQL Server database from an Access database. In the second article I describe how to link an Access database to an existing SQL Server database. In this article I cover how to get started using Access Projects.

Designing a database, creating tables, forms, queries, and reports in Access are beyond the scope of these articles. I'm going to assume that you know that clicking "Next" moves you to the next step in a wizard and know to click "OK" to accept changes. I also will assume that you have the necessary privileges and permissions in SQL Server.

Create an Access Project

The Access Project type of Access application is tied very tightly to SQL. In fact, when using this type of application, the data can only be stored in SQL. Think of this as an alternate SQL GUI. Tables created in the Access Project are actually created in SQL. One big advantage of this approach over linking in a traditional Access database is the use of stored procedures. In fact, you can even create the stored proc within the Access Project.

To create an Access Project, follow these steps:

  • Open Access. Click File --> New.... Choose Project using existing data... to link to an existing SQL database. Or choose Project using new data... to create a new SQL database.
  • The File Save dialog opens, enter a location for your Access ADP file.
  • Fill in the server name, your credentials and the database name and then click OK.
View the steps to create an Access Project



Figure 1. The Access Project. Tables are stored in SQL Server.

I now have what looks like an ordinary Access database, but the data and table definitions are stored in SQL. Click on the Queries section to see the difference. You can create functions, views, and stored procedures. These objects are actually stored in the SQL Server database. The forms, reports and VBA code you create are stored in the Access database. The data types used in the table definitions are SQL data types, not Access data types. You can even easily link reports to stored procedures. This is possible in traditional Access but not very easy to do. If the stored procedure requires parameters, assign the parameters to the "Input Parameters" property of the report.



Figure 2. Functions, views, and stored procedures are actually located in the SQL database.

Other Access Project Features

If you need to connect your Access Project to a different server or database, you'll find the Data Link Properties dialog under File--> Connections.
Wouldn't it be nice to be able to perform some administrative functions from within the Project? Look in the database utilities where you'll find several administrative features including a way to back up the SQL database.



Figure 3. Some administration tools.

One thing that is missing is a version of Query Analyzer. The only way to run SQL statements is by saving the statements first in a view or stored procedure. Well, I guess you probably don't want to give QA to your customers anyway, so I suppose that's fine. Overall, I'd say that Access Project is a very nice tool to quickly create a client for your SQL database.

Conclusions

While MS Access can't do the work that SQL Server does, it is quick and easy to set up a user interface and reports with little or no programming. It provides a nice front end to SQL databases, and it is very easy to link to many other data sources as well. In this series, I have shown you how to link from Access to SQL using three different methods. The method you choose will depend on your application and preferences.

Total article views: 9953 | Views in the last 30 days: 9
 
Related Articles
FORUM

Security in Multiuser environment (Ms Access Project or Ms access database)

Ms.Access Project Vs. Ms. Access Database

FORUM

Need to give Developer access to create stored procs

Need to give developer access to create stored procs

FORUM

Call store procedure from Access

How to call store procedure from Access

ARTICLE

Creating Your Dream Project

In this article, columnist Christopher Duncan shows you how to create your dream development project...

FORUM

Creating a SQL/Access Database from Scratch

One Man shop needs Direction to create a SQL/Access Database for Mangement Staff

Tags
access    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones