... Database Design

Are you interested in moving your MS Access database to the cloud? Microsoft Azure can host SQL Server databases in the cloud. From a Microsoft Access database, you can connect to the database and use those tables the same way you could link to SQL Server databases on your network or SQL Express on your desktop.

Linking Access to SQL Server Tables in the Cloud

Luke Chung

Cloud computing will be a huge benefit to the Access community. Instead of worrying about the hardware and deployment issues around applications, one can focus on building the solution and using the enterprise quality cloud platforms which previously didn't exist or were prohibitively expensive and difficult to use. For a fraction of the cost of buying and setting up a SQL Server box on your network, you can have Microsoft do it for you without worrying about licenses, downtime, hardware, etc., and it's available over the Internet to anyone you give the credentials for logging into it.

Read more

Why choose MS Access versus Excel

Microsoft article

  • Do you need to help users enter data? For example, some users find it hard to enter data in a grid of cells. With Access, you can work around that problem by creating data entry forms that make it easier to enter data accurately.
  • Do your users need reports? With Access, you can create reports that users can run at any time.
  • Access is also a better choice when you need to have multiple users working on the data at the same time. Several users can work on the same data, because Access locks a record only while a user changes it. You can share an Excel workbook with several users, but the process works best when users change data in Excel at separate times.
  • If you need to connect to several data sources and edit the data directly in those sources, then Access is your choice. You can view external data with Excel, but you can't change that data.
  • Do you need to store and manage your data, or do you need to analyze that data? If storage is your primary goal, then use Access.
  • Do you have a lot of data? For example, is your Excel worksheet so large that it's hard to use? If so, even if you have flat data, Access can make your information easier to find.
  • And finally, a relational structure helps keep your information accurate, because you can prevent users from entering data in the wrong table.

Excel Vs. Access – Can A Spreadsheet Replace A Database?


Or should you be using both? Access and Excel both feature data filtering, collation and querying, but which program is suited to your work requirements, and how do you get the best of both worlds?

Understanding What Each Tool Is For

Access versus Excel

Read more

Using Access or Excel to manage your data

In very general terms, Access is the best choice when you have to track and record data regularly, and then display, export, or print subsets of that data. Access forms provide a more convenient interface than an Excel worksheet for working with your data. You can use Access to automate frequently performed actions, and Access reports let you summarize data in printed or electronic form. Access provides more structure for your data; for example, you can control what types of data can be entered, what values can be entered, and you can specify how data in one table is related to data in other tables. This structure helps you ensure that only the correct types of data are entered.

Using Access to build a front end for SQL Server

Tech Republic, Susan Harkins

What are the advantages of using Access as the front end to a SQL Server database? For starters, it's likely that your client's users will be more familiar with it, and it's relatively easy to put in place.

Knowing your database system like the back of your hand is good, but only part of the battle. Coming up with an easy-to-use interface that your users can learn quickly is just as critical.

You could spend a lot of time reinventing the wheel, or you could use Access—a relational database on its own. Unlike SQL Server, Access also offers a variety of development tools and controls for building a flexible and easy-to-use end product. Lots of developers depend on Access to build their front-end applications.

Over the years, Access has proven to be a useful front end for the big databases. Now you have the Access Data Project (ADP), which is much cleaner and easier to work with. An ADP is a specific Access file type that stores user objects such as forms, reports, macros, and Visual Basic for Applications (VBA) code modules. All the other objects—the tables, stored procedures, views, and so on—are stored on the database server. You can use an ADP as a front end to SQL Server in three ways:

  • Convert an MDB file to an ADP file, which will use SQL Server. All the data will be stored on SQL Server, but you'll still use Access' familiar interface to interact with the data. This choice requires a substantial investment in development time because you usually have to manually revamp forms, reports, queries, and so on.
  • Keep your MDB file intact, but link to the server tables from inside the MDB. Doing so will use an ODBC link to the newly upsized tables on SQL Server. The resulting arrangement is slower than the previous one.
  • Upsize data to SQL Server by creating an entirely new database on SQL Server without making changes to the actual MDB file.

Read more