Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-02-04
6
Medium Priority
?
324 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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 52

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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