Solved

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

Posted on 2013-02-04
6
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 50

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

688 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