Avis
Junior Poster
Joined: 07 Oct 2003
Posts: 510
Location: India
|
 Posted: Dec 2nd, 2003 12:07 PM Post subject: ASP :: Query Any/All Access Databases with one Generic ASP P |
 |
|
This code snippet allows a user to run ad-hoc SELECT queries on ANY TABLE IN ANY ACCESS .MDB FILE without adding or modifying code.
To use, simply:
Save this code snippet as BROWSER.ASP to a ASP-enabled directory on your web server.
Toss one or more .MDB files into the same directory [plus the standard ADOVBS.INC].
Now you're ready to go. Just fire up your browser and point it at BROWSER.ASP.
The program will first ask you to choose the desired .MDB file (from among those it finds in the current directory). From there you'll be asked to choose the desired table and the fields from that table that you want to display. A SELECT query is generated and the results displayed in a nice little HTML table.
| Code: | <% Option Explicit %>
<!-- #include file="adovbs.inc" -->
<%
Dim dbc, rsData, rsFieldNames
'--- PROMPT FOR DESIRED .MDB FILE (IF USER HAS NOT YET SPECIFIED) ---
If Request.Form("mdb")="" Then
Dim objFSO, objFolder, objItem
'--- CREATE PULL-DOWN LIST WITH NAMES OF .MDB FILES IN DEFAULT DIRECTORY ---
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Server.MapPath("."))
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<FORM METHOD='Post' ACTION='browser.asp'>"
Response.Write "Please select desired Access database: "
Response.Write "<SELECT name='mdb'>"
For Each objItem In objFolder.Files
If InStr(UCase(objItem.Name), ".MDB") > 0 Then Response.Write "<OPTION value=" & """" & objItem.Name & """" & "</OPTION>" & UCase(objItem.Name)
Next
Response.Write "</SELECT>"
Response.Write "<Input type='submit' value=' Next --> '>"
Response.Write "</FORM>"
Set objFSO = Nothing
Set objFolder = Nothing
Set objItem = Nothing
'--- PROMPT FOR DESIRED TABLE IN .MDB FILE (IF USER HAS NOT YET SPECIFIED) ---
ElseIf Request.Form("table")="" Then
Set dbc = Server.Createobject("ADODB.Connection")
dbc.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(Request.Form("mdb"))
'--- CREATE PULL-DOWN LIST OF ALL TABLES IN SELECTED .MDB FILE ---
Set rsFieldNames = dbc.OpenSchema(adSchemaTables)
rsFieldNames.Filter = "TABLE_TYPE='TABLE'"
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<FORM METHOD='Post' ACTION='browser.asp'>"
Response.Write "Please select desired table: "
Response.Write "<SELECT name=table>"
Do While Not rsFieldNames.EOF
Response.Write "<OPTION value=" & """" & rsFieldNames("TABLE_NAME") & """" & "</OPTION>" & rsFieldNames("TABLE_NAME")
rsFieldNames.MoveNext
Loop
Response.Write "</SELECT>"
Response.Write "<input name='mdb' type=hidden value='" & Request.Form("mdb") & "'>"
Response.Write "<Input type='submit' value=' Next --> '>"
Response.Write "</FORM>"
rsFieldNames.Close
set rsFieldNames = Nothing
dbc.Close
set dbc = Nothing
'--- PROMPT FOR DESIRED FIELDNAMES IN SELECTED TABLE (IF USER HAS NOT YET SPECIFIED) ---
ElseIf Request.Form("selectfields")="" Then
Dim table
table = Request.Form("table")
Set dbc = Server.Createobject("ADODB.Connection")
dbc.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(Request.Form("mdb"))
'--- CREATE RADIO-BUTTON SELECTION OF AVAILABLE FIELDS IN SELECTED TABLE ---
Set rsFieldNames = dbc.OpenSchema(4, Array(empty, empty, table, empty) )
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<FORM METHOD='Post' ACTION='browser.asp'>"
Do While Not(rsFieldNames.EOF)
Response.Write "<INPUT TYPE=checkbox NAME='selectfields' VALUE=" & """" & rsFieldNames(3) & """" & ">" & rsFieldNames(3) & "<BR>"
rsFieldNames.MoveNext
Loop
Response.Write "<INPUT TYPE='hidden' NAME='mdb' VALUE='" & Request.Form("mdb") & "'>"
Response.Write "<INPUT TYPE='hidden' NAME='table' VALUE='" & Request.Form("table") & "'><BR>"
Response.Write "<Input type='submit' value=' Next --> '>"
rsFieldNames.Close
set rsFieldNames = Nothing
dbc.Close
set dbc = Nothing
'--- IF ALL NECESSARY INFORMATION HAS BEEN PROVIDED, DISPLAY DATA FROM SELECTED TABLE ---
Else
'--- ESTABLISH CONNECTION TO DESIRED .MDB FILE ---
Set dbc = Server.Createobject("ADODB.Connection")
dbc.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(Request.Form("mdb"))
'--- CONSTRUCT SQL QUERY STRING ---
Dim sql
sql = "SELECT "
Dim i
For i = 1 to Request.Form("selectfields").Count
sql = sql & "[" & Request.Form("selectfields")(i) & "]"
If i<Request.Form("selectfields").Count Then sql = sql & ", "
Next
sql = sql & " FROM [" & Request.Form("table") & "];"
'--- CREATE RECORDSET BASED ON QUERY STRING
Set rsData = dbc.Execute(sql)
'--- CREATE RECORDSET OF DATABASE SCHEMA INFORMATION ---
Set rsFieldNames = dbc.OpenSchema(adSchemaColumns)
'--- WRITE DATA AND SCHEMA INFO ON CHOSEN FIELDS TO HTML TABLE
Response.Write "<HTML><BODY BGCOLOR=White><font face='verdana,arial' size=2>"
Response.Write "<b>Database:</b> " & Request.Form("mdb") & ", <b>Table: </b> " & Request.Form("table") & "<br>"
Response.Write "<b>Query: </b> " & sql & "<br><br>"
Do While Not(rsData.EOF)
Response.Write "<TABLE WIDTH=100% BORDER=1 ALIGN=center>"
Response.Write "<TR><TD width='25%'><font face='verdana,arial' size=2><B>Field Name</b></TD><TD width='30%'><font face='verdana,arial' size=2><B>Description</b></TD><TD width='45%'><font face='verdana,arial' size=2><B>Value</b></TD></TR>"
For i = 1 to Request.Form("selectfields").Count
rsFieldNames.filter = "TABLE_NAME='" & Request.Form("table") & "'" & " AND COLUMN_NAME='" & Request.Form("selectfields")(i) & "'"
Response.Write "<TR>"
Response.Write " <TD width='25%'><font face='verdana,arial' size=2>" & Request.Form("selectfields")(i) & " </TD>"
Response.Write " <TD width='30%'><font face='verdana,arial' size=2>" & rsFieldNames("DESCRIPTION") & " </TD>"
Response.Write " <TD width='45%'><font face='verdana,arial' size=2>" & rsData(Request.Form("selectfields")(i)) & " </TD>"
Response.Write "</TR>"
Next
Response.Write "</TABLE><BR>"
rsData.MoveNext
Loop
'--- CLEAN UP ---
rsData.Close
rsFieldNames.Close
dbc.Close
set dbc = Nothing
End If
%> |
Hope this helps!
_________________
Code Snippets, Tutorials, Utilities, Controls
Low cost Web Hosting
Hosting starts at as low as $4 per year!
Always follow posting guidelines
Put your VB code in [vb ] your code [ /vb] tags! |
|
