Loop through table def

[alex]

Loop through table def

Hello,

Using Access ’03…

I have the following code in a module (some of which graciously
provided by a member of this group):
‘’’’’’’’’’’’’’’’’’’
Function TableExists(strTable As String) As Boolean
Dim varDummy As Variant
On Error Resume Next
varDummy = CurrentDb().TableDefs(strTable)
TableExists = (Err.Number = 0&)
End Function

Function FindTable()
'used as a test for the function above

Dim strTable1 As String
Dim strTable2 As String
strTable1 = "Closed_Case_Link"
strTable2 = "Open_Case_Link"

If Not TableExists(strTable1) Then
MsgBox strTable1 & " does not exist!"
Else
MsgBox "tbl exists!"
End If

End Function
‘’’’’’’’’’’’’’’’’’’’’’’
In the function FindTable, I need to loop through (look for) about 10
tables.

I can Dim them all and set a value (you can see I’ve done two
already). I need the function, however, to look for all ten tables
and provide a message box (for each) if it cannot find any of the ten.

I could write an if statement for all ten, but that seems tedious…
there must be a better way.

I hope I’ve made sense!
Thanks for any advice.

alex

 

[kc-mass]

Hi

This will iterate through all your tables and message box the name.
You will want to store your required table name in a table.
You then can do a query or dlookup to see if each is presnt.

Sub TableAndFieldList()
Dim lngTable As Long
Dim DB As Database
Set DB = CurrentDb
'Loop through all tables
For lngTable = 0 To DB.TableDefs.Count - 1
'Do nothing if temporary or system table
If Left(DB.TableDefs(lngTable).Name, 1) = "~" Or _
Left(DB.TableDefs(lngTable).Name, 3) = "DBO" Or _
Left(DB.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'engineer your comparison to required tables here - maybe Dlookup?
MsgBox (DB.TableDefs(lngTable).Name)
End If
Next lngTable
Set DB = Nothing
End Sub


Regards

Kevin

Loop through table def

Hello,

Using Access ’03…

I have the following code in a module (some of which graciously
provided by a member of this group):
‘’’’’’’’’’’’’’’’’’’
Function TableExists(strTable As String) As Boolean
Dim varDummy As Variant
On Error Resume Next
varDummy = CurrentDb().TableDefs(strTable)
TableExists = (Err.Number = 0&)
End Function

Function FindTable()
'used as a test for the function above

Dim strTable1 As String
Dim strTable2 As String
strTable1 = "Closed_Case_Link"
strTable2 = "Open_Case_Link"

If Not TableExists(strTable1) Then
MsgBox strTable1 & " does not exist!"
Else
MsgBox "tbl exists!"
End If

End Function
‘’’’’’’’’’’’’’’’’’’’’’’
In the function FindTable, I need to loop through (look for) about 10
tables.

I can Dim them all and set a value (you can see I’ve done two
already). I need the function, however, to look for all ten tables
and provide a message box (for each) if it cannot find any of the ten.

I could write an if statement for all ten, but that seems tedious…
there must be a better way.

I hope I’ve made sense!
Thanks for any advice.

alex

 

[kc-mass]

Here is something more on the track you were pursuing.
Stick your 10 table names in a table called tblTableNames with a field
called TableName

Sub TableCheck()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("Select * from tblTableNames")
RS.MoveFirst
Do While Not RS.EOF
If Not TableExists(RS!TableName) Then
MsgBox RS!TableName & " does not exist!"
Else
MsgBox "Found It"
End If
RS.MoveNext
Loop
Set RS = Nothing
Set DB = Nothing
End Sub

Regards

Kevin


Loop through table def

Hello,

Using Access ’03…

I have the following code in a module (some of which graciously
provided by a member of this group):
‘’’’’’’’’’’’’’’’’’’
Function TableExists(strTable As String) As Boolean
Dim varDummy As Variant
On Error Resume Next
varDummy = CurrentDb().TableDefs(strTable)
TableExists = (Err.Number = 0&)
End Function

Function FindTable()
'used as a test for the function above

Dim strTable1 As String
Dim strTable2 As String
strTable1 = "Closed_Case_Link"
strTable2 = "Open_Case_Link"

If Not TableExists(strTable1) Then
MsgBox strTable1 & " does not exist!"
Else
MsgBox "tbl exists!"
End If

End Function
‘’’’’’’’’’’’’’’’’’’’’’’
In the function FindTable, I need to loop through (look for) about 10
tables.

I can Dim them all and set a value (you can see I’ve done two
already). I need the function, however, to look for all ten tables
and provide a message box (for each) if it cannot find any of the ten.

I could write an if statement for all ten, but that seems tedious…
there must be a better way.

I hope I’ve made sense!
Thanks for any advice.

alex

 

[alex]

Here is something more on the track you were pursuing.
Stick your 10 table names in a table called tblTableNames with a field
called TableName

Sub TableCheck()
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("Select * from tblTableNames")
    RS.MoveFirst
    Do While Not RS.EOF
        If Not TableExists(RS!TableName) Then
            MsgBox RS!TableName & " does not exist!"
        Else
            MsgBox "Found It"
        End If
        RS.MoveNext
    Loop
    Set RS = Nothing
    Set DB = Nothing
End Sub

Regards

Kevin


Loop through table def

Hello,

Using Access ’03…

I have the following code in a module (some of which graciously
provided by a member of this group):
‘’’’’’’’’’’’’’’’’’’
Function TableExists(strTable As String) As Boolean
    Dim varDummy As Variant
    On Error Resume Next
    varDummy = CurrentDb().TableDefs(strTable)
    TableExists = (Err.Number = 0&)
End Function

Function FindTable()
'used as a test for the function above

Dim strTable1 As String
Dim strTable2 As String
strTable1 = "Closed_Case_Link"
strTable2 = "Open_Case_Link"

If Not TableExists(strTable1) Then
    MsgBox strTable1 & " does not exist!"
Else
    MsgBox "tbl exists!"
End If

End Function
‘’’’’’’’’’’’’’’’’’’’’’’
In the function FindTable, I need to loop through (look for) about 10
tables.

I can Dim them all and set a value (you can see I’ve done two
already).  I need the function, however, to look for all ten tables
and provide a message box (for each) if it cannot find any of the ten.

I could write an if statement for all ten, but that seems tedious…
there must be a better way.

I hope I’ve made sense!
Thanks for any advice.

alex

Kevin,

The second piece of code worked perfectly...much appreciated! I like
the idea of storing the table names inside a table object.
alex