Using Data Access Pages to Access Data on a SQL Anywhere 10 Database

If you’re interested in setting up a front end for SQL Anywhere 10 in Microsoft Access, you’ve come to the right place. This article will show you how to import a table from SQL Anywhere 10 into Microsoft Access, how to query the table, and more.

SQL Anywhere 10

SQL Anywhere 10, which is in its enterprise ready avatar, is extremely easy to administer and supports many advanced features. It can also cater to HTTP requests for data with its HTTP server. The details of downloading and getting your hands wet with SQL Anywhere 10 have been described in another article on the ASPFree.com site. Its migration wizard is easy to use, as was shown in yet another article on DevShed.com.

The present article shows how to set up a front end for this database in Microsoft’s very popular Access application. The IIS 5.1 Server, the SQL Anywhere 10 Server as well as the MS Access 2003 all run on a Windows XP Professional Media Center Edition OS.

Overview of the tutorial

The SQL Anywhere 10 database when installed comes with its own ODBC provider, which is the easiest way to get access to the data on the server. It also comes with a demonstration database demo10.db. The required files will be installed in the same directory where the SQL Anywhere 10 is installed.

Using Microsoft Access 2003’s import utility, a table from SQL Anywhere 10 will be imported. This table will be queried. The Data Access Page based on the query will be created using the Page Wizard. The DAP will then be saved to the root directory of the intranet site. Data Access Pages exposed data is now available to the intranet users, and it is the easiest way to provide data.

Start the SQL Anywhere Network Server

Start the SQL Anywhere 10 Network Server item by accessing it from the SQL Anywhere 10 shortcut as shown.

This starts the demo10 database on port 2638 as shown. After starting, the following window gets minimized. Do not close this window. However, if you wish to close down you may close this window or click on the button Shutdown.

Create a Microsoft Access Database Application

After opening the MS Access program from its shortcut you may access the import utility from File–> Get External Data –>Import as shown in the next picture.

{mospagebreak title=Importing from an ODBC Database}

The Import window comes up. By default it opens up the My Documents folder to look for an existing MDB file. Click on the Files of type: drop-down to click on the ODBC Databases() item as shown.

When you click on the Import button in the previous import screen, the following window named Select Data Source shows up with the File Data Source tab in default. Change to Machine Data Source and click on the button New..

This opens the Create New Data Source window, where you will opt for creating a System Data Source by choosing the proper radio button. Read the description of what a System Data Source is at the bottom of this screen. Click on the button Next.

In this window (it’s actually another page in the Create New Data Source window) you scroll down and choose the SQL Anywhere 10 driver as shown. Click on the button labeled Next.

This shows the summary of our actions up to this point. Click on the button labeled Finish.

Since you are using the SQL Anywhere 10 driver, the ODBC Configuration for SQL Anywhere 10 shows up with five tabbed pages, with the ODBC tab in default as shown. You choose the data source name. SQL Any10 was chosen for this tutorial.  Click on the tab Login.

Here you provide the Login for the database. The UserID is dba  and the password is sql.

Now click on the Database tab to open the next window. The name of the server is demo10 and the Database file may be found at the same location where the SQL Anywhere 10 software files were installed. The other tabs may be accepted for their default for this tutorial.

When you click OK to the above screen you will return to the Select Data Source screen where you see the SQLAny10 datasource added to the Machine data sources as shown.

{mospagebreak title=Importing the Customers Table}

When you click OK to the above screen, the program pauses and the Import Objects window of the MS Access application shows up, displaying the various tables that you may want to import.  Highlight Group0.Custormers and click on the button labeled OK.

The Group0.Customers table is added to the TestXML database as shown. The Group0.Customers table was renamed to Demo10_Customers.

The next window shows some sample data from this table.

Creating a Query for the Data Access Page

The data access page will be based on a query of this table. The query used is shown in the next paragraph. This may also be designed using the MS Access query designer. The query selects several columns and later orders them by the column “State.” The query is given the default name Demo10_Customers Query.

SELECT Dem10_Customers.ID, 
       Dem10_Customers.Surname, 
       Dem10_Customers.GivenName, 
       Dem10_Customers.City, 
       Dem10_Customers.State, 
       Dem10_Customers.Country 
FROM 
       Dem10_Customers 
ORDER BY 
       Dem10_Customers.State; 

{mospagebreak title=Creating a Data Access Page}

In the Main window of the application choose to create a new Data Access Page. Opt for using the Page Wizard. From the drop-down choose the Demo10_Customers Query as shown.

This gets you to the first step of the wizard and since you have already chosen the fields, you shift all of them from Available Fields: area to Selected Fields: area using the >> button. Click on the button labeled Next.

In this second step, you can apply the way you want to group the fields. Highlighting “State” and clicking on the > button will make “State” the first level as shown.

Now highlight and click on “Country” and the > button makes the “Country” the second level as shown.

Of course “Country” should be the first level and “State” should be the second level. You can rearrange the levels by using the UP/DOWN arrows. Now the country and state have been reversed. Click on the button labeled Next.

In the third step of the wizard you can make the sorting orders you wish to impose on the data. Here the “Surname” will be sorted in ascending order as shown.

When you click on the Finish button you will have created a Data Access Page with a page that has been renamed Demo – CUSTOMERS as shown.

The Data Access Page shows up in design as a page with a default name, Page 1 as shown. You may add a heading to the page by clicking and typing in a title. The formatting of this page has been changed in the final displayed page.

The designed page also comes up with a FieldList, the data from the database behind the data access page.

Before you can see the page you have to save it when the SaveAs window shows up, where you change Page 1 to another name you want to use as shown.

When you click on the button labeled OK, you will get to the Save As Data Access Page pointing to the default directory, named My Documents. However since you wish to have intranet access you may choose to save it to the root directory of your intranet site directory , Inetpub/wwwroot as shown.

For the window which shows up next you may provide a suitable answer. Here the No option was used so as not to clutter the intranet site by default.

When you click No to the above you will get the next window, which warns you that the might not be able to connect to the data through the network and that you may need to edit the connection string to specify a UNC network path. Click OK to this as well.

You may now browse the page saved to the intranet site by typing  http://localhost/Customers-demo10.htm in the address  of your browser.

This next window that comes up warns that the data provider may not be safe. Click on the button labeled OK.

This window that comes up next is rechecking your trust of the web site. Click on the button labeled OK on this window as well.

This brings up the web page Demo10 – Customers.htm as shown. The formatting and styling can be changed by setting the properties of each element of this page. You may also change them in the HTML of the source. For working with the Data Access Pages please refer to the tutorials on data access pages on the ASP Free site.

Summary

Microsoft Access 2003 supports importing tables and objects from a wide variety of sources in addition to ODBC data sources. If an ODBC provider is available for the data source then it is relatively simple to bring in the table or objects to the MS Access application. Creating a data access page and enabling it on the intranet does not require any coding. By default the data source uses the local directory path; if you want it accessible from the network you should indicate a UNC path. This change can be made in the ConnectionString that gets written into the source of the data access page.

One thought on “Using Data Access Pages to Access Data on a SQL Anywhere 10 Database

  1. Here is a mix of a popular database, and a terrific front end which can be web enabled with a click and a purr. For developers this must be very attractive because you can download SQL Anywhere 10 by registering and Access 2003 is available equally easily because it is omnipresent.I look forward to your comments and suggestions.Sincerely,Jay

[gp-comments width="770" linklove="off" ]