?
Solved

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

Posted on 2013-02-04
6
Medium Priority
?
318 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 51

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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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