Export Database To HTML File (DAO)

This code will export your Database to HTML file. You can easily configure this code to determine which fields to include and which not, how to sort the database, which filters to apply (you can use your own SQL statement), and set the HTML file look (Colors, Fonts, etc).

Preparations

Add 1 Command Button and 2 Text Boxes to your form.

Add reference to DAO:
from the menu choose Project-> References, mark the Microsoft DAO 3.6 Object Library check box, and press OK.
If you have the Microsoft DAO 3.6 Object Library reference, you can skip the text below and start copying and pasting the code to your form.

If you have only Microsoft DAO Object Library 3.51 reference or earlier, and the database you want to export made in Access 97 or earlier, You can use it instead of the 3.6 version.

Otherwise (if you using database that made in Access 2000 and you don't have Microsoft DAO 3.6 Object Library reference), click on browse, and select the file C:\Program Files\Common Files\Microsoft Shared\Dao\dao360.dll (If you have Access 2000 installed in your computer you have this file.)
This will add Microsoft DAO 3.6 Object Library reference to your project. Now mark it and press OK.

Form Code

Option Explicit

Private Sub Command1_Click()
Dim fnum As Integer
Dim db As Database
Dim rs As Recordset
Dim num_fields As Integer
Dim i As Integer
Dim num_processed As Integer

    On Error GoTo MiscError

    ' Open the output file.
    fnum = FreeFile
    Open Text2.Text For Output As fnum

    ' Write the HTML header information.
    Print #fnum, "<HTML>"
    Print #fnum, "<HEAD>"
    ' replace "This is the title" with the title of HTML File that will appear
    ' in the upper blue title bar.
    Print #fnum, "<TITLE>This is the title</TITLE>"
    Print #fnum, "</HEAD>"

    Print #fnum, ""
    Print #fnum, "<BODY TEXT=#000000 BGCOLOR=#CCCCCC>"
    'Replace "My Title" with your desirableHTML filetitle
    Print #fnum, "<H1>My Title</H1>"

    ' Start the HTML table.
    Print #fnum, "<TABLE WIDTH=100% CELLPADDING=2 CELLSPACING=2 BGCOLOR=#00C0FF BORDER=1>"

    ' Open the database.
    Set db = OpenDatabase(Text1.Text)

    ' Open the recordset.
    ' replace "Table1" with the name of your DataBase Table
    ' and "ID" with the name of the field you want to sort
    ' the table by it. If you don't want to sort the table, you can
    ' remove the "ORDER BY ID"
    Set rs = db.OpenRecordset("SELECT * FROM Table1 ORDER BY ID")

    ' Use the field names as table column headers.
    Print #fnum, "    <TR>"     ' Start a row.
    num_fields = rs.Fields.Count
    For i = 0 To num_fields - 1
        Print #fnum, "        <TH>";
        Print #fnum, rs.Fields(i).Name;
        Print #fnum, "</TH>"
    Next i
    Print #fnum, "    </TR>"

    ' Process the records.
    Do While Not rs.EOF
        num_processed = num_processed + 1
        ' Start a new row for this record.
        Print #fnum, "    <TR>";

        For i = 0 To num_fields - 1
            Print #fnum, "        <TD>";
            Print #fnum, rs.Fields(i).Value;
            Print #fnum, "</TD>"
        Next i
        Print #fnum, "</TR>";

        rs.MoveNext
    Loop

    ' Finish the table.
    Print #fnum, "</TABLE>"
    Print #fnum, "<P>"
    Print #fnum, "<H3>" & _
        Format$(num_processed) & _
        " records displayed.</H3>"
    Print #fnum, "</BODY>"
    Print #fnum, "</HTML>"

    ' Close the file and database.
    rs.Close
    db.Close
    Close fnum
    MsgBox "Processed " & Format$(num_processed) & " records."

    Exit Sub

MiscError:
    MsgBox "Error " & Err.Number & _
        vbCrLf & Err.Description
End Sub

Private Sub Form_Load()
    'put your database file name in Text1
    'and the HTML file name you want to create in Text2
    Text1.Text = "C:\YourDataBaseFile.mdb"
    Text2.Text = "C:\TheHTMLfileName.htm"
End Sub
Go Back