weAscend.com

 

Function GetTblQry(fShowSys As Boolean, Optional strType As String) As String

'***********************
'Author: Michael Blake
'Contact Via: www.weAscend.com
'Date: 06/30/2000
'
'Example:
'
'GetTblQry(False,"Table")
'
'False = Doesn't return system tables.
'Table = Returns only tables.
' If Queries are wanted then enter Query
' If left blank, both will be returned
'
'Notes: This function is primarily used to populate a combo box
'
' To populate the combo box use this code:
'
' cboComboBox.RowSourceType = "Value List"
' cboComboBox.RowSource = GetTblQry(False)
'
' End code
'
'Please include above comments when using this code - Thank you
'************************

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

On Error GoTo Error_Handler

Set db = CurrentDb()
strOutput = ""

If Len(strType) = 0 Then
strType = "Both"
End If

If strType = "Table" Or strType = "Both" Then
db.TableDefs.Refresh
For Each tdf In db.TableDefs
If Not (isSystemObject(acTable, tdf.Name)) Or fShowSys = True Then
strOutput = strOutput & tdf.Name & ";"
End If
Next
End If

If strType = "Query" Or strType = "Both" Then
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
strOutput = strOutput & qdf.Name & ";"
Next
End If

strOutput = Left(strOutput, Len(strOutput) - 1)
GetTblQry = strOutput

Exit_Function:
Set db = Nothing
Exit Function

Error_Handler:
GetTblQry = "#Error#"
GoTo Exit_Function

End Function

Private Function isSystemObject(intType As Integer, _
ByVal strName As String, Optional ByVal varAttribs As Variant)

'****************
'Access Developer's Handbook (Sybex)
'www.developershandbook.com
'****************

If IsMissing(varAttribs) Then
varAttribs = 0
End If

If (Left$(strName, 4) = "USys") Or _
(Left$(strName, 4) = "MSys") Or _
(Left$(strName, 4) = "~sq_") Then
isSystemObject = True
Else
isSystemObject = ((intType = acTable) And _
((varAttribs And dbSystemObject) <> 0))
End If

End Function

 

Contact Us