Move Access data to a SQL Server database by using the Upsizing Wizard – Access –

How database objects get upsized

The following data and database objects get upsized:

Data and data types All Access database data types are converted to their equivalent in SQL Server. The wizard converts Access database text to Unicode by adding the Unicode string identifier to all string values and by adding the Unicode n prefix to all data types.


Select queries that don’t have an ORDER BY clause or parameters are converted to views.

Action queries are converted to stored procedure action queries. Access adds SET NOCOUNT ON after the parameter declaration code to make sure the stored procedure runs.

Select queries that only reference tables (also called base queries) that use either parameters or an ORDER BY clause are converted to user-defined functions. If necessary, the TOP 100 PERCENT clause is added to a query that contains an ORDER BY clause.

Parameter queries that use named parameters maintain the original text name used in the Access database and are converted either to stored procedures or inline user-defined functions.

Note You might need to manually convert queries that did not upsize, such as SQL pass-through queries, data definition queries, and crosstab queries. You might also have to manually upsize queries that were nested too deeply.

Forms, reports, and controls SQL statements in RecordSource, ControlsSource and RowSource properties for forms, reports, or controls are kept in place and are not converted to stored procedures or user-defined functions.

Startup properties The Upsizing Wizard upsizes the following startup properties:














Modules and macros The Upsizing Wizard does not make any changes to modules or macros. You might need to modify your application to take full advantage of SQL Server’s features. For more information, see the MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server.

via Move Access data to a SQL Server database by using the Upsizing Wizard – Access –

Create an Access project – Access –

Create an Access project

Click the Microsoft Office Button Button image, and then click New.

Alternatively, on the Getting Started with Microsoft Office Access page, under New Blank Database, click Blank Database.

Do one of the following:

Create an Access project in the default file location

Under Blank Database, type a name for the project in the File Name box using the .adp file name extension.


Create an Access project in a different file location

Under Blank Database, click Browse Button image for a location in which to save your database.

In the File New Database dialog box, browse to the location where you want to save the project, or accept the default location.

Type a file name for the project in the File name box.

In the Save as type list, select Microsoft Office Access Projects (*.adp), and then click OK.

Under New Project, click Create.

Access prompts you with the following message:

Do you want to connect to an existing SQL Server database?

If you want to connect to an existing SQL Server database, click Yes and continue with step 4; otherwise, click No and skip to step 5 to create a new SQL Server database.

Connect to an existing SQL Server database

In the Data Link Properties dialog box, enter the required information for the following:

Server name

Information needed to log on to the server

Database name

Data Link Properties dialog box

Click Test Connection to verify that the settings are correct and that the connection succeeds.

If the connection does not succeed, the Microsoft Data Link Error dialog box displays a message describing the problem. Click OK to close the message, check your settings in the Data Link Properties dialog box, and then click Test Connection again. Also, you might need to check with the SQL Server database administrator for the database you are connecting to, to make sure your settings are correct or that the computer is available.

After the Microsoft Data Link dialog box displays the message Test connection succeeded, click OK to close the dialog box, and then click OK to close the Data Link Properties dialog box.

Access connects the project to the SQL Server database.

Create a new SQL Server database

On the first page of the Microsoft SQL Server Database Wizard, enter the following information:

The SQL Server computer you want to use for this database

Your logon information

A name for the SQL Server database

Microsoft SQL Server Database Wizard

Note To skip the connection process at this time, click Cancel. Access creates the project, but it is not connected to a SQL Server database. Before you can do anything with the project, you will need to connect it to a SQL Server database, which you can do by using the steps in the next section.

Click Next to continue.

Access attempts to connect to the SQL server computer you specified. If Access cannot connect to the SQL server computer, it displays an error message describing the problem. The SQL server computer name or logon information might be incorrect, or you might not have the necessary permissions to create a database on the SQL Server computer that you specified. You might need to check with the SQL Server database administrator for the database you are creating, to make sure your settings are correct or that the computer is available.

After Access successfully connects to the SQL Server computer, the wizard displays the message: The wizard has all the information it needs to create your SQL Server database. Click Finish to continue.

Access creates the SQL Server database and connects it to the project.

via Create an Access project – Access –

Access 2002 Data Projects for Developers

Using functions

SQL Server 2000 implements a new type of rowset-returning object, the function. A function is similar to a stored procedure in that it can return a value that depends on an input parameter. Unlike a stored procedure, though, the result of a function can be used in the FROM clause of a SELECT statement. From the Transact SQL point of view, you can think of a function as a sort of inline procedure; from the point of view of Access ADPs, functions are good ways to handle parameterized queries.

Figure 4 showed you a function in the designer. This particular function takes the storeid that represents a store in the pubs database as a parameter, and returns a rowset containing all books sold in that store together with the quantity sold. Figure 7 shows a form based on this function. When the user selects a store from the combo box, the Change event runs to reset the form’s RecordSource property:

Private Sub cboStore_Change()

Me.RecordSource = _

“SELECT * FROM SalesByStore(” & cboStore & “)”

End Sub

Functions provide a handy trick for the construction of parameterized recordsets. Functions can contain quite complex conditional logic and multiple statements, though once they get past a certain level of complexity they can’t be represented in the graphical designer.

via Access 2002 Data Projects for Developers.

About an Access project (ADP) – Access –

Working with an Access project is very similar to working with an Access database. The process of creating forms, reports, data access pages, macros, and modules is virtually the same as that used to create an Access database.

Once you connect to an SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, user-defined functions, and database diagrams by using the Database Designer, Table Designer, Query Designer, Query Builder, and SQL Text Editor. Although the user interface for working with these database objects is different from the equivalent database objects in an Access database, it is just as user-friendly. An Access project also contains many of the same wizards as an Access database, such as the Form Wizard, the Report Wizard, the Page Wizard, and the Input Mask Wizard. These Wizards help you to quickly create a prototype or simple application, and also make it easier to create an advanced application.

via About an Access project (ADP) – Access –