Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Print a few records from each table in a Access db

Status
Not open for further replies.

KevinsHandle

Programmer
Jan 14, 2012
16
US
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.
 
Some notes.

Code:
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"

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top