• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Need help with DAO code not adding table for some reason.

Please note the following code:  For some reason it doesn't add the table LINKED_TABLE but it does not give any error.  I just look in the navigation pane area and it doesn't appear - even after closing out and re-opening.   No where the code dies but it just refuses to work.  I know I'm close but still far - please help anyway you can!

I know some would rather give me some clues or links to more reading - which I have already done thus far.  Right now, I'm just looking for the direct answer to solve this ASAP.

Thanks,

Stephen

Private Sub Ensure_Table_Exists()

  Dim sSQL As String
  Dim dbs As DAO.Database
  Set dbs = CurrentDb
  
  If TableExists("LINKED_TABLE") = False Then
  
    Dim tbl As TableDef, fld As Field, fld2 As Field
    Set tbl = dbs.CreateTableDef("LINKED_TABLE")
    ' Creating the fields
    
    Set fld = tbl.CreateField("Table_Name", dbText, 255)
    With tbl
    
      .Fields.Append fld
        Set fld2 = tbl.CreateField("Linked_FileName", dbText, 255)
          .Fields.Append fld2
        Set fld2 = Nothing
        
    End With
    Set fld = Nothing
    
    dbs.TableDefs.Append tbl
    
    Set tbl = Nothing
    Set dbs = Nothing

  End If

End Sub

Open in new window

0
stephenlecomptejr
Asked:
stephenlecomptejr
1 Solution
 
omgangCommented:
If the function TableExists returns True then the rest of the procedure won't execute.  What is the function TableExists?  Is it always returning True?
OM Gang
0
 
jerryb30Commented:
Try if not(tableExists("Linked_Table")) then
0
 
stephenlecomptejrAuthor Commented:
For some reason it didn't refresh the navigation pane and did add it anyway.

If Table_Exists did not prevent from firing.

So I appreciate the help and wish to give the points - but do not know what's fair?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rey Obrero (Capricorn1)Commented:
you can use this command line to refresh your navigation pane


RefreshDatabaseWindow


place it somewhere in your codes, so you don't need to close/open db to see if the table was created or not.
0
 
jerryb30Commented:
I added nothing. No points here.
0
 
Gustav BrockCIOCommented:
It should work. Try this modification:
Private Sub Ensure_Table_Exists()

  Dim dbs As DAO.Database
  Dim tbl As DAO.TableDef, fld As DAO.Field, fld2 As DAO.Field

  Set dbs = CurrentDb
  
'  If TableExists("LINKED_TABLE") = False Then
  If True Then
    Set tbl = dbs.CreateTableDef("LINKED_TABLE")
    ' Creating the fields
    Set fld = tbl.CreateField("Table_Name", dbText, 255)
    Set fld2 = tbl.CreateField("Linked_FileName", dbText, 255)
    With tbl
      .Fields.Append fld
      .Fields.Append fld2
    End With
    dbs.TableDefs.Append tbl
    
    Set fld = Nothing
    Set fld2 = Nothing
    Set tbl = Nothing
    Set dbs = Nothing

  End If

End Sub

Open in new window

/gustav
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now