Solved

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

Posted on 2013-02-04
6
292 Views
Last Modified: 2013-02-05
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
Comment
Question by:stephenlecomptejr
6 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 38851549
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38851565
Try if not(tableExists("Linked_Table")) then
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 38851676
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38851728
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38853484
I added nothing. No points here.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38853941
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question