Author: Michael Gonzalez
Frequently Answered Questions
Miscellaneous

File Text Search & Replace Utility
HTML Text Extraction using innerText
E-mail (CDONTS.NewMail) Sample Code
SQL Server 7.0/2000

Incorporating ASP and SQL Server
100's of T-SQL Scripts
Don't Use @@ERROR with UPDATE Statements
Exporting Tables to Text Files
Creating SQL Server Databases
ASP (SQL) Query Analyzer
Increasing SQL Server Performance with Indexes
Distributed SQL Server Transactions & Queries
COM/COM+ Development
What is COM?
Isn't ActiveX and COM the same?
How can Components benefit my ASPs?
Am I using COM Components now?
How do I use COM Components in my ASPs?
Creating your First COM Component
Creating a COM Component that uses ASP Intrinsic Objects
Creating a COM Component to access an MS-Access Database
MTS Component Template
MSMQ Component Template / Example

Creating a COM Component to access an MS-Access Database



In this chapter we will create a component that can be used to perform SQL commands on databases. The component itself does not necessarily have to be limited to MS-Access databases. You can use an MS-SQL Server database if you'de like. But, for the puspose of this demonstration, we will be using a MS-Access database to retrieve data from and display it via our ASP page.

First, we must setup a Microsoft© Access database. I have provided the database we will be using in our tutorial in my source_code directory. Click here(168K) to download the Database.mdb Access database we will be using. The database will used to put and pull data into and from our ASP pages.

Rather than explain the theory and an understanding of the technologies we will be using in our tutorial (such as ADO, ODBC, OLE DB, and SQL), I am going to dive right into the dirty work and get to it - I'll do some explaining some as we move along.

Before we continue, you will need to make sure you have a few things ready:
  • Visual Basic© 6 (for creating your component)
  • PWS (Personal Web Server) or IIS4 (Internet Information Server v.4)
  • Notepad editor or Visual InterDev (for ASP editing)
Please follow the directions below step-by-step:
  1. Download the Database.mdb database I have created for this tutorial by clicking here Copy it to a directory of your liking.
  2. Go to your Windows©' Control Panel and double-click on the ODBC Data Sources (32bit) icon.
  3. Click on the System DSN tab.
  4. Click on the Add... button.
  5. Click on Microsoft© Access Driver (*.mdb).
  6. Click on the Finish button.
  7. In the Data Source Name: field, type MSAccess.
  8. Click on the Select... button.
  9. Select the path/location to where you saved the Database.mdb database file.
  10. Click on the OK button.
  11. Click on the OK button to exit the administrator.


    12. We have now successfully provided a data provider to our MS-Access database and called it MSAccess using ODBC!

  12. Startup Microsoft© Visual Basic© 6.0.
  13. Choose ActiveX DLL for your new project then click on the Open button.
  14. Change the project name from Project1 to DataAccess.
  15. Change the module name from Class1 to SQL.
  16. Click on the Project menu then click on References....
  17. Click on the checkbox next to Microsoft© ActiveX Data Objects 2.1 Library.
  18. Click on the OK button.
  19. Type the following code in the code window: Public Function Execute(strSQL As Variant, strConn As Variant) As ADODB.Recordset 'Only continue with the method if these two variables 'contain text If Trim(strSQL) <> "" And Trim(strConn) <> "" Then 'Create a new ADO Connection object Dim objConn As New ADODB.Connection 'Create a connection to the provider in strConn objConn.Open strConn 'Make the cursor run on the client - this must be 'specified in order to use a disconnected recordset 'because will be not be connected to the data store 'througout our ASP page objConn.CursorLocation = adUseClient 'Return a Recordset back to the calling ASP object 'based on the strSQL command(s) provided Set Execute = objConn.Execute(strSQL) 'Disconnect out Execute object from the strConn 'Connection so we don't get an error Set Execute.ActiveConnection = Nothing End If End Function
  20. Save your project into a location of your liking and name it what you will.
  21. The DataAccess.SQL component is now ready to compile. Click on the File menu then click on Make DataAccess.dll.... Save the DLL somewhere on the web server where we will be accessing it from our ASP page.
  22. On your web server, register the component by typing regsvr32 X:\Path\DataAccess.dll from the DOS Prompt or from Windows©' Run dialogue box.
  23. Now we must create an ASP page that will both create a new record/row with data in the first three columns of Table1 of the Database.mdb database and retrieve that data to display out to the clients browser. Create an ASP page called Test.asp somwhere on your Inetpub\wwwroot directory and place either of the following code examples in it:

    Using VBScript: 'Instantiate the COM Component DataAccess.SQL (DataAccess.dll) Set objDS = Server.CreateObject("DataAccess.SQL") 'Create a new SQL Command that creates a new record/row with data in the first three columns strSQL = "INSERT INTO Table1 (FIELD1,FIELD2,FIELD3) VALUES ('one','two','three')" 'Specifies the DSN data source name to our Database.mdb access database strConn = "DSN=MSAccess" 'Execute the SQL Method objDS.Execute strSQL, strConn 'Retrieve a recordset with the data we just added to the database Set objRS = objDS.Execute("SELECT * FROM Table1", "DSN=MSAccess") 'While the cursor is not at the end of the table display the contents of F1-F3 Do While Not objRS.EOF Response.Write("<b>FIELD1:</b>" & objRS.Fields.Item("FIELD1").value & "<br>") Response.Write("<b>FIELD2:</b>" & objRS.Fields.Item("FIELD2").value & "<br>") Response.Write("<b>FIELD3:</b>" & objRS.Fields.Item("FIELD3").value & "<br>") 'Move the next record/row in Table1 objRS.MoveNext() 'Do it again until the cursor is at the end of Table1 Loop 'Destroy the Recordset object Set objRS = Nothing 'Destroy the DataAccess.SQL object Set objDS = Nothing Using JavaScript: //Instantiate the COM Component DataAccess.SQL (DataAccess.dll) var objDS = Server.CreateObject("DataAccess.SQL"); //Create a new SQL Command that creates a new record/row with data in the first three columns var strSQL = "INSERT INTO Table1 (FIELD1,FIELD2,FIELD3) VALUES ('one','two','three')"; //Specifies the DSN data source name to our Database.mdb access database var strConn = "DSN=MSAccess"; //Execute the SQL Method objDS.Execute(strSQL, strConn) //Retrieve a recordset with the data we just added to the database var objRS = objDS.Execute("SELECT * FROM Table1", "DSN=MSAccess") //While the cursor is not at the end of the table display the contents of F1-F3 while (! objRS.EOF) { Response.Write("<b>FIELD1:</b>" + objRS.Fields.Item("FIELD1").value + "<br>"); Response.Write("<b>FIELD2:</b>" + objRS.Fields.Item("FIELD2").value + "<br>"); Response.Write("<b>FIELD3:</b>" + objRS.Fields.Item("FIELD3").value + "<br>"); //Move the next record/row in Table1 objRS.MoveNext() //Do it again until the cursor is at the end of Table1 } //Destroy the Recordset object var objRS = null; //Destroy the DataAccess.SQL object var objDS = null;
  24. Now, run the Test.asp in a browser. The ASP page will do two things: 1) Update the Database.mdb database with new records which populate the FIELD1, FIELD2, and FIELD3 fields with data and 2) retrieves that same data and displays it to the client's browser using the Response.Write method.
I have provided, in a ZIP format, all of the source code, the database, and DLL that we have produced in this tutorial. Click here to download the database.zip file.
Comments & Questions Form

Send It!