Print a few records from each table in a Access db

[KevinsHandle]

I want to iterate thru TDFs, set a strSQL to get the top 5 records, open and print the query result and move on to the next TDF. I have code for looping the TDFs but after opening the query how do I print it and close it ? Thanks.

 

[Remou]

Some notes.

Dim qdf As QueryDef
Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb

Set tdf = db.TableDefs("Table1")

sSQL = "SELECT * FROM [" & tdf.Name & "]"

If DLookup("Name", "MSysObjects", "Name='TempQry'") = Null Then
    CurrentDb.CreateQueryDef "TempQry", sSQL
Else
    CurrentDb.QueryDefs("TempQry").sql = sSQL
End If

DoCmd.OpenQuery "TempQry"
DoCmd.RunCommand acCmdQuickPrint
DoCmd.Close acQuery, "TempQry"

http://lessthandot.com

 

[KevinsHandle]

Here is the quick and dirty code that I usedto find my data.

Sub SampleData()
'**********************************************
'*This is designed to walk the table definitions in an access db
'*and print out the top five records from each similarly named
'*table. you could change the IF condition to do groups of tables
'*All tables or all but system tables
'
' Needed this because some soul rearanged server level source tables
' and schema without notice
'**********************************************
Dim db As Database
Dim qdfnew As QueryDef
Dim strSQL As String
Dim lngTable As Long
Set db = CurrentDb
For lngTable = 0 To db.TableDefs.Count - 1
If db.TableDefs(lngTable).Name Like "tblT*" Then
strSQL = "Select Top 5 " & db.TableDefs(lngTable).Name & ".* FROM " & db.TableDefs(lngTable).Name & ";"
With CurrentDb
.QueryDefs.Delete ("qryGetFileSample")
Set qdfnew = .CreateQueryDef("qryGetFileSample", strSQL)
.Close
End With
Debug.Print strSQL
DoCmd.OpenQuery ("qrygetfilesample")
DoCmd.PrintOut acPrintAll
DoCmd.Close acQuery, "qrygetfilesample"
End If
Next lngTable
End Sub