We help IT Professionals succeed at work.

HTML table linked to Access Base

Matt_Kennedy
Matt_Kennedy asked
on
I am trying to build an html page that displays fields from an ACCESS 2003 database in a table.  For some reason the fields do not come up when the html page is executed.  The SQL query looks at the database and is suppose to return all rows that begin with the letter R,  Instead it returns a blank table.  Can someone please look at the code and see where the mistakes lay? The code is below:

<html>
      <head>
      <title>Brand Select</title>
      </head>
      <body>

      <%
      ' Database connection - remember to specify the path to your database

      'DIM Conn
      Set Conn = Server.CreateObject("ADODB.Connection")
      DIM ConnectString
      Set ConnectString="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\anderson\company\BrandSource\Brands.mdb"
      Conn.Open ConnectString
      Set RSTitleList = Server.CreateObject("ADODB.Recordset")
      ' SQL Query
      'DIM strSQL
      SET strSQL = "SELECT * FROM Brands WHERE (((Brands.BrandName) Like 'R*'))"
      Set RSTitleList =  Conn.Execute(strSQL)
      ' Execute the query (the recordset rs contains the result)
%>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>

<!-- Begin our column header row -->  
<TR>
   <TD  VALIGN=TOP BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Brand Name</FONT>
   </TD>
    <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      SubBrand</FONT>
   </TD>
   <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      Manufacture</FONT>
   </TD>
   <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      Handle</FONT>
   </TD>
   <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      Website</FONT>
   </TD>
</TR>

<!-- Ok, let's get our data now -->
<% do while not RStitleList.EOF %>
<TR>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
           <%=RStitleList("BrandName")%></font>   </TD>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("SubBrand")%>   </font>   </TD>  
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("Manufacture")%>   </font>   </TD>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("Handle")%>   </font>   </TD>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("Website") %>   </font>   </TD>
</TR>

   <% RSTitleList.MoveNext%>
<%loop %>      
      <% Conn.Close
      Set Conn = Nothing%>

      </body>
      </html>
      
thank you in advance!
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
why not just do something like this:

    DoCmd.OutputTo acOutputTable, "YourTableName", acFormatHTML, "C:\YourFolder\YourTableName.HTML"

Author

Commented:
Thank you for the response, however, the question was,  where is the problem with the code I have presented to you?
thank you
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
OK,
Fair enough, ...
;-)

I just thought that the greater issue was that you simply needed to generate HTML for a table/query...

I am sure an HTML Expert will be along shortly and see the issue more clearly...

;-)

Jeff


Aurelian ConstantinProgrammer-analyst

Commented:
First of all, make sure that you have some records in the Access table that begin with R , otherwise it is normal you get emplty HTML table.

Second, the SQL query phrase should be a little cleaner:

SELECT * FROM Brands WHERE BrandName Like 'R*'

Open in new window


Finally, even your SQL is absolutely correct, I would use:

SELECT * FROM Brands WHERE UCase(Left(BrandName,1))='R'

Open in new window


If your table contains a huge number of records, check that the BrandName column is indexed.

My solution is (read my important comments that begin with ============):

<html>
<head>
<title>Brand Select</title>
</head>
<body>
<%
' Database connection - remember to specify the path to your database
'DIM Conn
Set Conn = Server.CreateObject("ADODB.Connection")
DIM ConnectString
'Set ConnectString="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\anderson\company\BrandSource\Brands.mdb"
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\anderson\company\BrandSource\Brands.mdb"
Conn.Open ConnectString
Set RSTitleList = Server.CreateObject("ADODB.Recordset")
' SQL Query
'DIM strSQL
'strSQL = "SELECT * FROM Brands WHERE BrandName Like 'R*'"
' =============================== I CHOOSED TO USE A DIFFERENT SQL
strSQL = "SELECT * FROM Brands WHERE UCase(Left(BrandName,1)) = 'R'"
' =============================== PAY ATTENTION TO THE FOLLOWING LINE
RSTitleList.Open strSQL, Conn
' Execute the query (the recordset rs contains the result)
%>
<TABLE align=center CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- Begin our column header row -->  
<TR>
   <TD  VALIGN=TOP BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Brand Name</FONT>
   </TD>
    <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      SubBrand</FONT>
   </TD>
   <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      Manufacture</FONT>
   </TD>
   <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      Handle</FONT>
   </TD>
   <TD ALIGN=CENTER BGCOLOR="#800000">
     <FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>      Website</FONT>
   </TD>
</TR>
<!-- Ok, let's get our data now -->
<% 
' =============================== FIRST CHECK THAT WE REALLY HAVE SOME RECORDS
if RSTitleList.BOF And RSTitleList.EOF Then
%>
	<TR>
	<TD colspan="5" align="center">No records found!</TD>
	</TR>
<%
Else
' =============================== NOW WE CAN CYCLE THROUGH ALL RESULT RECORDS
' =============================== I USED WHILE - WEND CYCLE
	while not RStitleList.EOF
%>
<TR>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
           <%=RStitleList("BrandName")%></font>   </TD>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("SubBrand")%>   </font>   </TD>  
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("Manufacture")%>   </font>   </TD>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("Handle")%>   </font>   </TD>
   <TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
          <%=RSTitleList("Website") %>   </font>   </TD>
</TR>

<% 
		RSTitleList.MoveNext
	Wend
End If
' =============================== NOW I CLOSE THE RECORDSET AND DISPOSE IT
RStitleList.Close
Set RStitleList = Nothing
Conn.Close
Set Conn = Nothing
%>
</body>
</html>

Open in new window

Aurelian ConstantinProgrammer-analyst

Commented:
I tested the solution I posted above with a small Brands database, and here is the screenshot:

 Screenshot of result page

Author

Commented:
Aurelianco

Thank you for the reply.  With your code, however, I receive No Records found, even though there are two records in the table that begin with R.  Are there any requirements to use the ADODB.connection ?
What could i be missing? the database is 2003/2007 and it is shared. Can you send me the database you are using?
thank you again for the help

Author

Commented:
Aurelianco,

Attached is the database and I trying to access along with the code page.
Brands.mdb
retrieve.htm

Author

Commented:
Can anyone provide any feedback from the above attached files?
For some reason I receive 'No Results Found' everytime I load the page.
Any insight would be greatly appreciated.
thank you
Aurelian ConstantinProgrammer-analyst

Commented:
This is the Brands.mdb database I used. It is Access 2000.
 Brands.mdb
Aurelian ConstantinProgrammer-analyst

Commented:
With your database, I get this result:

 Screenshot
Aurelian ConstantinProgrammer-analyst

Commented:
Apparently the code and database are all OK, the problem comes from the retrieve.htm file name.

HTM files are NOT processed on the server. So, you have to rename it as retrieve.asp and the IIS will process the file on the server as an ASP script.

Author

Commented:
Aurelianco,

Thank you for the solution!  I am almost there, but I am not sure how to put the file into a directory that will allow me to serve the retreive.asp file via a url.  When I click on the file directory it opens it in sharepoint designer.  If i open it with IE it just displays the code.  Can you help me with that?
thank you again!
Programmer-analyst
Commented:
To make the server to process the ASP file, you have to put it in the IIS home directory. To find that, open IIS management console by right-clicking My Computer - Manage an then choose on the bottom tree Services and Applications - Internet Information Services.

Now expand it and go to Default Website. Right-click on it and choose Properties, then choose Home Directory tab. Here you will find it as Local path (default is C:\Inetpub\wwwroot).

So you have to put in that directory the ASP file.

After you do that, you can test it by accessing with your internet browser the address http://localhost/retrieve.asp (if you are working on the same computer). If you are using the internet browser from another computer, replace localhost with the name of the computer where you have the ASP file.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.