Create Table DAO (1 Viewer)

[Bechert]

Hello group,

I have an application that runs Access 2007 (runtime package) at remote locations. I want to add several tables to these existing databases. The code below creates a table and adds the primary key field. When I run the code it creates an entry in the table list, but when I double-click or right-click (Design view) a message tells me it can't find the table.

thanks for your help.

Private Sub CreateTable(db As DAO.Database, TableName As String, PrimaryKeyFieldName As String)
On Error GoTo ErrHandler
    'Purpose:   Create a table using DAO.
    Dim tdf As DAO.TableDef
    Dim Fld As DAO.Field
    Dim Ind As DAO.index
    
    Set tdf = db.CreateTableDef(TableName)
    
    With tdf
        Set Fld = .CreateField(PrimaryKeyFieldName, dbLong)
        Fld.Attributes = dbAutoIncrField + dbFixedField
        .Fields.Append Fld
        
    End With
    
    'Primary key index.
    Set Ind = tdf.CreateIndex("PrimaryKey")
    With Ind
        .Fields.Append .CreateField(PrimaryKeyFieldName)
        .Unique = True
        .Primary = True
    End With
    tdf.Indexes.Append Ind
    
    'Save the table.
    db.TableDefs.Append tdf
    
        
ExitHandler:
    Set Fld = Nothing
    Set tdf = Nothing
    Set Ind = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description & "  Message Number -  " & Err.Number
    Resume ExitHandler
    
End Sub

 

[spikepl]

I ran your code as is in my a2010 - worked just fine, and the table it spawned was editable by double click or right click.

 

[gemma-the-husky]

are you trying to create this in the local database, or in the backend?

you probably won't be able to do the latter with that code. Is that the problem?

 

[Bechert]

Yes, I am trying to create the tables in the backend.
What do I need to do to accomplish that?

Thanks,
Bill

prior to the call to the subroutine the db is set to the location of the BE db.
In development this all works fine (adding tables to a standalone BE).

 

[MarkK]

That code creates the table in the database represented by the parameter db.

Private Sub CreateTable([COLOR="Red"]db As DAO.Database[/COLOR], TableName As String, PrimaryKeyFieldName As String)

You can get a reference to a different database than the current one using the OpenDatabase method of the DBEngine object, so something like . . .

    Dim rdb As DAO.Database
    Set rdb = DBEngine.OpenDatabase("\\yourserver\your_be.mdb")
[COLOR="Green"]    'create the table in rdb[/COLOR]
    CreateTable rdb, "TestTable", "TestTableID"

Finally, the TableDefs collection, which like any collection is programmatically somewhat costly to create, does not Refresh automatically when you add a table in code. You may need to run the TableDefs.Refresh method before the newly appended object is visible and available.

I would also be tempted to write a function that returns--as well as creates--the new TableDef . . .

Function CreateTable([COLOR="Red"]db As DAO.Database[/COLOR], Name As String, PrimaryKey As String) As DAO.TableDef
[COLOR="Green"]   '...
   'your code here
   '...
   'save the table.
[/COLOR]   db.TableDefs.Append tdf
[COLOR="Green"]   'return the new TableDef to consumer[/COLOR]
   Set CreateTable = tdf

End Function

 

[Bechert]

Lagbolt, that helped. But still have a problem.
When I look at the Backend database directly the new tables are there and display (both data and in design view). When I open the FE the new tables are in the table list and appear to be linked to the FE. But when I try to look at them I get the message that the table doesn't exist or is misspelled.
When I start the FE I receive the same message when accessing forms or queries that refer to the new tables.

Could the issue now be that the link for the new tables needs to be refreshed? If so, how would I do that?
thanks,
Bill

 

[MarkK]

Here's code to create a linked table in the FE.

Function CreateLinked(SourceDb As DAO.Database, SourceTable As String, _
    TargetDB As DAO.Database, TargetTable As String) As TableDef
    Dim tdf As DAO.TableDef

    Set tdf = TargetDB.CreateTableDef(TargetTable)           [COLOR="Green"]'create new TargetTable in TargetDb[/COLOR]
    tdf.Connect = "MS Access;PWD=;DATABASE=" & SourceDb.Name [COLOR="Green"]'define link to SourceDb ...[/COLOR]
    tdf.SourceTableName = SourceTable                        [COLOR="Green"]'... and to SourceTable[/COLOR]
    TargetDB.TableDefs.Append tdf                            [COLOR="Green"]'append to collection[/COLOR]
    Set CreateLinked = tdf                                   [COLOR="Green"]'assign to function[/COLOR]
    
End Function

You may need to delete the old links first.

 

[Bechert]

In the upgrade code, I delete the current links and then after the new tables are added, tabledef is refreshed and inks rebuilt. The result is the same - the BE database has all the changes (new tables and new fields) but I cannot open new tables from the FE.

Here is how the upgrade process works. A new FE is created with new forms, queries, and reports and VBA code to update existing tables with new fields and create new tables. At the beginning of the upgrade code the links to the BE are deleted, at the end of the code (after tabledef is refreshed) the links are rebuilt.
The new FE replaces the current FE. When the application starts, it detects a change in software version and the upgrade code is executed. All the functionality related to adding fields to existing tables works fine. Any functionality related to the new tables generates a message the the table is misspelled or missing.

The new tables are in the tables list as linked. When I open the BE as a standlone DB I can open all the new tables and view them in design view.

Any other suggestions.
Thanks,
Bill

 

[MarkK]

I still don't get your upgrade process. I'll tell you what I do, and you tell me at what part of that process you do something different.

1) Customer has FE version 1.0 on his local machine, and a BE file on the server.
2) I ship FE version 2.0.
3) Customer deletes version 1.0, copies version 2.0 to location recently vacated by 1.0, and runs version 2.0
4) Version 2.0 detects whether BE is compatible, and if not runs updates, including changing data or structures in the BE.
5) Updates succeed or not. <-- is this where your links are dead??? Are the links in the FE broken, and if so, can't you delete them and re-add them?

 

[Bechert]

Lagbolt, my upgrade process is similar to yours.

I found a solution to the new tables in the BE not accessible from the FE.
I discovered the issue when I right-clicked on one of the "linked" new tables in the FE and selected Link Manager. The new tables had a path to the BE in the install folder, not the folder on the server.
I solved the problem by setting the link path to the server location at the time the new table is created:
dbCurrent.TableDefs(tablename).connect = ";Database=" & DBLocation
dbCurrent.TableDefs(tablename).Refreshlink

Where
dbcurrent is the FE
DBLocation is the path + DB name on the server.

thanks for all your help.
Bill

 

[MarkK]

Cool. Nice job.