You can connect to an Access database within an ASP page with different methods. This article will help you to create a System Data Source Name (DSN) for an Access database on your local machine.
Open Control Panel window, you will see different icons as shown in Pic 1.
Doubble click on ODBC Data Source Administrator icon.

ADO is used to query the data, and the underlying database may be MySQL, SQL Server, Access, Oracle, or any other major RDBMS. Unlike many other articles, complete examples are presented, which may be copied into a file and run directly. Techniques for optimizing the output of tabular data are presented first, followed by techniques for optimizing more complex tabular data typical of reports. The appendix contains instructions for configuring the tests, links to references, and books suggestions.

This one's like a double whammie! Welcome to the world of hard core SQL Server stored procedures and to the NextRecordset method of ADO's recordset object... Before we go on, this requires SQL Server. WON'T WORK WITH ACCESS.

That said, the goal for this example was easy... Get a list of all current tables in the database created by your's truly, get a list of dependent stored procedures for each table and do it all in a stored procedure! Easy...

There are basically 2 parts to this example. The calling application (a VBScript class called SQLServerTools) and the SQL Server stored procedure: sp_Admin_ExampleTablesDependencies (included with the class). The VBScript class is easy and contains only 1 method. That method is a great example of how to use the NextRecordset method of ADO to capture a second (or 50th) recordset returned by a procedure. For example, did you know this was valid SQL:

"SELECT * FROM table1;SELECT * FROM table2;"

Well it is, SQL Server knows that this statement should produce two separate and distinct recordsets however if you execute that and don't use NextRecordset, you will only get that first RS back. The stored procedure I wrote to do all the work is the second part of the example. That procedure returns (2 * num of tables) recordsets which is variable and based on the number of tables found in sysobjects for your particular db. Bottom line, that procedure always returns more than 1 recordset.

Right now, as you read this, I estimate that the procedure returned as many as 30 recordsets that were looped through with NextRecordset... But that's not the real magic. The class only exists to call the procedure and work with it's results. The stored procedure itself does all the work.

I've commented both the app and the stored procedure to the max but I'll give you an overview of the stored procedure... The stored procedure uses a cursor to loop through the records of a recordset returned by the system table: sysobjects. The process used is almost the same as using ADO to manipulate a recordset but right in the db itself... this means huge speed bonus and, by writing a stored procedure, all that logic is forever contained within and isn't spilled out into the class where it doesn't belong.

So anyways, while the procedure is looping through each table returned by sysobjects, it's simultaneously calling the system stored procedure: sp_depends. sp_depends is a handy function that returns a recordset of names representing every stored procedure that references the entered table! sp_depends can be used on any object, not just tables, but that is another discussion for another day...

The following code can be used to compact MS Access(MDB) database using ASP code. Compacting the database is requuired to reduce the growing MDB file size. This can be achieved by using DAO or JRO.

The answer is shown below. All I had to do was create an object, and use "expando properties" to fill it with recordsets. The function DBGetNamedRecords takes the name of the recordset and the SQL to fill it.