In article <95*************************@posting.google.com> ,
ad***@mentorelectronics.com (Charlie) writes:
Hello,
I have data in an Access table that I would like to export to multiple
HTML tables. I would like to split the data in the Access table
(about 92,000 records) into multiple HTML tables/files to reduce
download time and bandwidth usage on my web server. That way, the
user can select a particular page to download instead of downloading a
page with all of the records.
I would like to set a limit of only 500 records per file.
For example, I would like to be able to read from an Access Query,
'qryA'. This query selects all records whose part number starts with
"A".
The pseudo-code for the routine that I would like to run is as
follows:
// Variables
RecordCount = 1
PageCount = 1
Buffer = some kind of temp space
Outfile = qryA-(PageCount).shtml
// (The filename for the first pass would be qryA-1.shtml)
Read all records from qryA into Buffer
while not EOF (Buffer)
{
----->write to Outfile "<table>"
-----> while RecordCount <= 500 {
-------->read next line from 'Buffer'
-------->write to Outfile "<tr><td>Buffer.fieldA</td></tr>"
-------->write to Outfile "<tr><td>Buffer.fieldB</td></tr>"
-------->write to Outfile "<tr><td>Buffer.fieldC</td></tr>"
-------->write to Outfile "
"
-------->RecordCount = RecordCount + 1
--------> }
------> write to Outfile "</table>"
------> Close Outfile
------> RecordCount = 1
------> PageCount = PageCount + 1
}
// If the dataset had 1400 records I should end up with 3 files:
qryA-1.shtml (500 records)
qryA-2.shtml (500 records)
qryA-3.shtml (400 records)
This seems to be a fairly simple algorithm, but I have not coded in
quite some time. I would like to do this within Access 97 VBA if
possible. Any help would be appreciated
Thank you very much in advance.
Charlie
This code should get you going in the right direction:
Sub sCreateStaticHTMLFiles()
On Error GoTo E_Handle
Dim db As Database ' Database object
Dim rs As Recordset ' Recordset object
Dim strHTMLFile As String ' Path and name of the HTML file to be
written to
Dim strSQL As String ' SQL String
Dim intFile As Integer ' File number being dealt with
Dim intCountFiles As Integer ' Number of files to be created
Dim intHTMLFile As Integer ' File allocation number
Dim strOutput As String ' Data to be written to the file
Set db = DBEngine(0)(0)
' Get the number of files to be created (rounding up)
intCountFiles = -Int(-DCount("OrderID", "Orders") / 100)
' Deal with the first 500 records
strSQL = "SELECT TOP 500 * FROM Orders " _
& " ORDER BY OrderID ASC;"
Set rs = db.OpenRecordset(strSQL)
If Not (rs.BOF And rs.EOF) Then
strHTMLFile = "D:\Test0.htm"
intHTMLFile = FreeFile
Open strHTMLFile For Output As intHTMLFile
Print #intHTMLFile, "<html><body><table>"
Do
strOutput = "<tr><td>" & rs!OrderID & "</td><td>" & rs!CustomerID &
"</td></tr>"
Print #intHTMLFile, strOutput
rs.MoveNext
Loop Until rs.EOF
Print #intHTMLFile, "</table></body></html>"
Close #intHTMLFile
End If
rs.Close
' Now deal with subsequent batches of 500 records
For intFile = 1 To intCountFiles
strSQL = "SELECT TOP 500 * FROM Orders " _
& " WHERE OrderID NOT IN " _
& " (SELECT TOP " & 100 * intFile & " OrderID FROM Orders ORDER BY
OrderID ASC) " _
& " ORDER BY OrderID ASC;"
Set rs = db.OpenRecordset(strSQL)
If Not (rs.BOF And rs.EOF) Then
strHTMLFile = "D:\Test" & intFile & ".htm"
intHTMLFile = FreeFile
Open strHTMLFile For Output As intHTMLFile
Print #intHTMLFile, "<html><body><table>"
Do
strOutput = "<tr><td>" & rs!OrderID & "</td><td>" &
rs!CustomerID & "</td></tr>"
Print #intHTMLFile, strOutput
rs.MoveNext
Loop Until rs.EOF
Print #intHTMLFile, "</table></body></html>"
Close #intHTMLFile
End If
rs.Close
Next intFile
sExit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Reset
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit
End Sub
--
Jon
www.applecore99.com - Access Tips and Tricks
