Microsoft Access and Microsoft SQL Server
|
|
Microsoft Access and Microsoft SQL Server |
If you have a Microsoft SQL Server database and you want to use it in a Microsoft Access database, you have various options. You can import the objects from the server database or you can create a Microsoft Access project that is a database whose tables link to the server. |
|
If you have one or more tables that are part of a Microsoft SQL Server database but for some reason you do not want, do not need, or are not allowed, to work directly on the database server, you can import the objects to Microsoft Access. You first need to create the recipient database in Microsoft Access. Microsoft Access treats Microsoft SQL Server as one of the ODBC family of databases (the Microsoft Windows operating system has a driver that allows different database environments to communicate or interact with the same database, as long as they follow some rules of the ODBC driver; for example, an application created with Delphi and a PHP web site both can communicate with a Microsoft Access database or a Microsoft SQL Server database). Therefore, before importing a Microsoft SQL Server table (or a table from any ODBC compliant database), you should first create a data source. You can create the data source from Microsoft Access or you can first create it using Control Panel:
In both cases, you would get the Create New Data Source dialog box:
From there, you can click the type of database you want to import. In this case, you would click SQL Server. If you started from Control Panel, you would click Finish. If you had started from Microsoft Access, you can click Next. You would be prompted to enter a name for the data source:
After typing the name, you can click Next and click Finish. Next, the Create a New Data Source dialog box with the name you previously specified. You are allowed to type a small description of the database. You can type anything. Then, in the Server combo box, select the name of the server where the database is located. If the database is in the same computer you are using, you can type (local):
You can then click Next:
You can accept all defaults or, if you are creating a sensitive database that would require people to log in, you can click the second radio button, then provide a user name and a password. When ready, click Next. The next page of the wizard allows you to select the actual database that has the table(s) you want to import. To select it, you should click the top check box to access its combo box. Then click the arrow of the combo box to display the list of databases and select the one you want:
After selecting the database, you can click Next. The last page of the wizard allows you to set some options. Once you are ready, you can click Finish:
To make sure everything is alright, you can click the Test Data Source button. If everything looks alright, you can click OK. If you had started from the Control Panel, the process would end. If you add started from Microsoft Access, the Select Data Source dialog box would come back with the data source you had just created. You can then select it:
And click OK. A dialog box would display, containing all the objects that belong to the database you had selected. The names of the tables start with the schema name, in this case dbo:
Because there are many objects that Microsoft SQL Server creates for each database, you certainly would not need all of them. Therefore, you should select only the table (and probably the views). To select an object, simply click it, then click each of the other objects you would need. Once you are ready, click OK. Microsoft Access would start analyzing and importing the table(s). When the process is over, a dialog box would let you know. You can then click Close. Once you do, the names of the tables would appear in the Navigation Pane. Each starts with the schema name and an underscore. In most cases, you may have to rename each by removing the schema prefix.
Instead of importing the whole database or some of its table from a Microsoft SQL Server application, you can create a project in Microsoft Access that would connect to the table from the server. That way, if you enter data in the Microsoft Access database, the data would be stored on the server. This also makes it possible to create visually good looking objects in Microsoft Access instead of the somewhat less friendly tables of Microsoft SQL Server (in reality, you never want to let users work on the server and in fact, a visual interface in Microsoft Access allows you to exercise a great level of control over what your users can do or cannot do with your database). To create an application in Microsoft Access that can communicate with the objects stored in a Microsoft SQL Server database, you can link the tables from Microsoft Access to tables of a Microsoft SQL Server database or you can create what is called a database project or simply a project. You have various options. After creating a database in Microsoft SQL Server, to create tables linked to it, in Microsoft Access, on the Ribbon, click External Data and, in the Export section, click More -> ODBC Database. In the Get External Data - ODBC Database dialog box, click the second radio button: |
|
Then click OK. The Select Data Source dialog box would open and you must select a data source. If you had not created one, then follow the steps to create it. In the Select Data Source dialog ox, click the data source name and click OK. This would open the Link table dialog box. In the list of tables, you should click only the actual tables you will use in Microsoft Access:
After selecting the tables, click OK. If Microsoft Access needs some explanation about some tables (or views), you may receive a dialog box asking you to identify the records unique identifiers. When everything is done, the linked tables would appear in the Navigation Pane:
Once the tables have been linked, you can use them as you see fit. You can create the forms and allow the users to exploit the database. The new records would be added to the database on the server.
If you have created a database in Microsoft Access and want to use it in Microsoft SQL Server, you can export it. Before doing this, you must create a database in Microsoft SQL Server. It would also be a good idea to create a data source, although you can create it from Microsoft Access. To export a table:
This would open the Export dialog box that asks you to specify the name of the table that will be created in Microsoft SQL Server. By default, the Export dialog box would suggest the same name as the database that was selected:
You can accept it or enter a new one. After specifying the name of the table, click OK. The Select Data Source dialog box would come up. If you had already created a data source, you can select its name. Otherwise, use the New button to launch a wizard that would assist you to create a data source. After specifying the data source of the database that will receive the table, click OK. If everything is alright, the table would be exported. Once the table has been exported, a dialog box would come up to let you know. You can then click Close.
Microsoft Access has some limitations as compared to Microsoft SQL Server. For example, Microsoft Access has limits on both the size of a database and the number of people who can connect to a back-end database at the same time. If you find out that Microsoft Access is becoming too restraint to accommodate your expanding database, you can upgrade it to a formal server database. That is, you can transfer the Microsoft Access database to a Microsoft SQL Server database where it would enjoy more options. Before upsizing a database, you should enable the security. To do this, on the Message Bar, you can click Options... If the Microsoft Office Security Options dialog box, click the Enable This Content radio button and click OK. To upsize a Microsoft Access database, open the database. On
the Ribbon, you can click Database Tools. In the Move Data section, click the
SQL Server button
The first page of the Upsizing Wizard allows you to specify whether you will use an existing Microsoft SQL Server database or you want to create a brand new one. If you had already created a database on the server and you only want to add the tables (and queries) to it, you can select the first option. In this case, you must create a data source for the existing database, and follow the wizard. If you did not first create the server database, you can accept the second radio. Once you are ready, click Next. If you had selected the first radio button, clicking Next would open the Select Data Source dialog box. If you had selected the second radio button in the first page of the wizard before clicking Next, the second page of the wizard would come up:
The second page of the wizard allows you to specify the name of the server where the database will be installed. You must also specify how you will be authenticated. The bottom section of the page displays a text box with a suggested name of the server. The name is made of the name of the Microsoft Access database you started from and appended with the "SQL" suffix. You can accept that name or change it. Once you are ready, click Next. The wizard would analyze the database and display the names of the tables in the third page of the wizard:
The third page allows you to select what tables should be created in the database server:
After selecting the tables, you can click Next:
The fourth page of the wizard allows you to specify some options that would be applied to the database on the server. After accepting the defaults or making your selections, you can click Next:
In the same way, the fifth page presents some options. Once you have reviewed it, accepted or making changes, you can click Next (or Finish). The last page only lets you know that all information is ready to apply on the server:
You can then click Finish. The wizard would then start creating the database or applying the changes:
When the process is over, a report would be presented to you. |
|
|
||
| Home | Copyright © 2008-2016, FunctionX, Inc. | |
|
|
||
to locate its folder and select it. Otherwise, you can create a new data source.
To do this, click the New button. 
.
This would open the Upsizing Wizard:
