Loop through table def

  • Thread starter Thread starter alex
  • Start date Start date
A

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
 
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
 
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
 
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
Click to expand...

Kevin,

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