Connecting to a Microsoft Access Database via DSN

For the web server to access a database, you need to define an ODBC data source. A data source contains the information required to connect to a data provider, in this case, a Microsoft Access database. Earlier in this book you learned about three types of data sources: User DSN, File DSN, and System DSN. File DSNs offer the most flexibility for web work. Because the information required to connect to the data source is stored in a text file, file DSNs can be readily shared with other users and even easily moved to another web server if needed.

The following hands-on exercise will walk you through the steps required to set up a File DSN on your computer.

Part V

©Hands-On 28-5: Creating a File DSN

1. Open the Windows Control Panel. If you are using Windows XP or Windows 2000, double-click the Administrative Tools icon and double-click the Data Sources (ODBC) icon. In Windows NT, choose the ODBC icon. The ODBC Data Source Administrator window appears, as shown in Figure 28-10.

Figure 28-10: Use the ODBC Data Source Administrator window to set up the required connection to your database.

2. In the ODBC Data Source Administrator window, select the File DSN tab and click the Add button (Figure 28-11).

Figure 28-11: Use the Add button on the File DSN tab to add a file data source name for your database connection.

3. The Create New Data Source window opens. Select the Microsoft Access Driver as shown in Figure 28-12 and click Next.

Taking Your VBA Programming Skills to the Web

Figure 28-12: The first step in creating a new data source is to specify a driver that will enable you to connect to the required data provider.

4. Type Northwind for the name for the DSN, and click Next.

Figure 28-13: Use the Create New Data Source window to specify the name of the data

The second Create New Data Source dialog box will appear as shown in Figure 28-14.

Figure 28-14: Access displays the information you selected during the ODBC DSN setup so that you can revise it, if necessary, by clicking the Back button.

Figure 28-14: Access displays the information you selected during the ODBC DSN setup so that you can revise it, if necessary, by clicking the Back button.

Part V

5. After ensuring that your information matches Figure 28-14, click the Finish button. If you have made a mistake, click the Back button and move to the appropriate step to correct your selections.

6. In the ODBC Microsoft Access Setup window that appears, click the Select button.

7. In the Select Database window, select Northwind.mdb in the Learn_ASP folder, and click OK (see Figure 28-15).

Figure 28-15: Use the ODBC Microsoft Access Setup window to specify the name of the database you want to connect to.

8. Click OK to close the Select Database window. When you click OK you should see the path of the selected database in the Database area of the ODBC Microsoft Access Setup window (Figure 28-16).

Figure 28-16: After choosing the database name using the Select button, the ODBC Microsoft Access Setup window displays the full path of the selected database.

9. Click OK to close the ODBC Microsoft Access Setup window. The ODBC Data Source Administrator window appears with the name of your new data source, Northwind (Figure 28-17).

Taking Your VBA Programming Skills to the Web

Taking Your VBA Programming Skills to the Web mmsaammms^msm^^^mMim Figure 28-17: The

ODBC Data Source mmsaammms^msm^^^mMim Figure 28-17: The

ODBC Data Source

source named

Northwind.dsn created in Hands-on 28-5.

source named

Northwind.dsn created in Hands-on 28-5.

An ODBC File data sauce vcu to comcct to a data provider. File DSNs can be shared by users who have the same drivers nslajlod.

C I- Cancel I Hele

By default, the File DSN is stored in the \Program Files\Common Files\ODBC\Data Sources folder.

10. Click OK to close the ODBC Data Source Administrator window.

11. Open Windows Explorer and copy Northwind.dsn from the \Program Files\Common Files\ODBC\Data Sources folder to your Learn_ASP folder.

The following hands-on exercise demonstrates how you should edit the Employees.asp file created in Hands-On 28-1 so that it uses the Northwind data source name that was set up in Hands-On 28-5.

© Hands-On 28-6: Modifying the ASP File to Use the File DSN

1. Start Notepad and open the Employees.asp file located in the Learn_ASP folder.

2. Find the following statement in the Employees.asp file: conn.open "Northwind","Admrin",""

and replace it with:

conn.open "FILEDSN=C:\Learn_ASP\Northwind.dsn","Admrin",""

Change the drive letter in the above statement, if necessary, to match the setup of the Learn_ASP folder on your computer. The modified code fragment should look as follows:

If IsObject(Session("Northwind_conn")) Then Set conn = Session("Northwind_conn")

Else

Set conn = Server.CreateObject("ADODB.Connection") conn.open "FILEDSN=C:\Learn_ASP\Northwind.dsn","Admrin","" Set Session("Northwind_conn") = conn

Part V

3. Save the Employees.asp file and exit Notepad.

4. Launch Internet Explorer and type http://localhost/NorthDB/ Employees.asp in the address box of your browser. Press Enter to execute the Active Server Pages (.asp) file or click the Go button. The contents of the Employees table should appear in your browser as shown in Figure 28-18.

5. In the browser window, select View | Source to view the source code.

The Source command uses Windows Notepad to display a source file. Because the script commands contained in the ASP file are evaluated on the server before the browser receives the page, the resulting page in the browser is 100% pure HTML code. Notice that the browser does not display any of your ASP code.

Figure 28-18: After adjusting the connection setting in the Employees.asp file (see step 2 above), you can request the ASP page by typing its URL in the web browser's address

Taking Your VBA Programming Skills to the Web

Rie Edit Format Vbw Hefcj

<meta htt P -equiV= "c ont e nt -Type " comt ent="t e xt/htntl; c h ar 5 et =wi nd ows -12 5 2 " > <title >snployees i/titl e >

<TABLE BOR DER-1 BGCOLOR-#ffffff CELLSPACING-OxFONT F*CE-"Arial" COLOR-#000 000 XC APTIOM> <B>EliiplOye es </B></CAPTIOM> </FO NT>

<th bgcolor=#C0CQCQ bordercolor=#000000 xfont Styl e=f0nt-si2e:lOpi face="Ar1al color=#oooooo>enp 1 oyee io</fontx/th>

<TH BGCOLOR=#cOcOcO BORDERCOLOR=#OOQOOO XFOMT Styl e=FONT-SIZEilOpt FACE="Arial COLOR-#000000>Last Najne</FONT><AH>

<TH BGCOLOR-rfcQcOcO BORDERCOLOR-#000000 XFOMT Styl e-FONT-SIZEtlOpt FACE-"Arial COLOR=#000 000 >Fi r st Nam e</FONT ></th>

<TH BGCOLOR=#cGcOcO BORDERCOLOR=#00000Q XFOMT Styl e=FONT-SIZE:10pt FACE="Ari al COLOR=#000 000 >Ti T1e</FO NT> <A H >

<TH BGCOLOR=#CQCOcO BORDERCOLOR=#OOOQOQ xFONT Styl6=FONT-SIZ£ilOpt FACE="af1al color=#OQO ooo >t 1 t 1 e of courtesy</font><an>

<TH BGCOLOR=#cOcOcO BORDERCOLOR=#OOOOCO XFOMT Style=FONT-SIZE:10pt FACE = "Arial COL0R-#000000>Bi rth Dat e</FONT><Ah>

<TH BGCOLO R-ifcOc OcO BORDERCOLOR-ifOOQOOQ XFOMT Styl e-FONT-SIZE ilOpt FACE-"Ar i al COLOR=#000000>Hi r e Date</FOMTxAH>

<TH BGCOLOR=#cOcOcO BORDERCOLOR=#000000 XFOMT Styl e=FONT-SIZE;10pt FACE = "Ar i al COLOR=#"QOO 000 >Ad dr e 5 S </FONT ></TH>

<TH BGCOLOR=#C0COcO BORDERCOLO&=#OGQOOO XFONT Styl 6= FONT-SIZE ilOpt FACE="Ar1al COLOR=#000 OOQ >C1T y</FONT X/TH>

<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#OQOOOO XFOMT Style=FONT-SIZEIlOpt FACE = "Arial COLOR-#000 000 >Re g i on</FOWT><AH>

<TH BGCOLOR-#cOcOcO BORDERCOLOR-+000000 XFOMT Styl e-FONT-SIZE:10pt FACE-"Arial COLOR=iK)00000>Postal Codec/FONT></TH>

<TH BGCOLOR=#cOcOcO BORDERCOLOR=#0G0000 XFOMT Styl e=FONT-SIZE:10pt FACE="Ari al COL0R=#000 000 >CO unt r y</FONT X/TH>

<TH BGCOLOR=#CQCOcO BORDERCOLOR=#0G0000 XFONT Styl S=FONT—SIZE JlOpt FACE = "Ar1al color=#ooo ooo >Home p hon s</font ></th>

<TH BGCOLOR=#cOcOcO BORDERCOLOR=#OOOOOQ XFONT Style=FONT-SIZEllOpt FACE = "Arial COL0R-#000 000 >Ext ens i on </FONT > <A H>

<TH BGCOLO R-^cOc OcO BORDERCOLOR-ifrOGQOOO XFOMT Styl e-FONT-SIZE :10pt FACE-"Ar i al COLOR-#000 000 >Ph Ot CK/FO NT> </TH>

<TM BGCOLOR=#cOcOcO BORDERCOLOR=#000000 XFOMT Styl e=FONT-SIZE LlOpt FACE = "Ar i al

Figure 28-19: When you examine the source code of an ASP page (as instructed in step 5 of Hands-On 28-6), you will only see the HTML code. The scripting code is evaluated on the server and only the resulting HTML is passed to the browser!

6. Close Notepad and Internet Explorer.

0 0

Post a comment