Update table links from a utility to separate BE and FE 1

[nikademous]

Hello, I have a update utility that moves files (tables, forms and module) into a chosen front end and back end. What im trying to do from this utility is link the that BE(s) tables to the FE that I chose. I cant get it right, I get down to the second Debug.Print strTblName and it gives me the correct names of the tables in the BE but wont update them to the front end.

strBEFile and strFEFile are the full paths of the BE and FE that was selected to link tables.

   Dim dbs, dbs1 As DAO.Database
    Dim tdf, tdf1 As TableDef
    Dim strFEFile, strBEFile, strTblName As String
    
    strBEFile = Me.txtBackEnd
    strFEFile = Me.txtFEMaster
   
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(strBEFile)
    Set dbs1 = DBEngine.Workspaces(0).OpenDatabase(strFEFile)
    
    On Error Resume Next
    For Each tdf1 In dbs1.TableDefs
        strTblName = tdf1.Name
        'Debug.Print strTblName
        If Left(strTblName, 4) <> "msys" Then
            dbs.TableDefs.Delete strTblName
            'Debug.Print strTblName
            
            Set tdf = CurrentDb.CreateTableDef(strTblName)
            tdf.Connect = ";DATABASE=" & strBEFile
            tdf.SourceTableName = strTblName
            CurrentDb.TableDefs.Append tdf
            
        End If
    Next tdf1
    
    Set dbs1 = Nothing
    Set dbs = Nothing

 

[dhookom]

I would get rid of the On Error Resume Next.

You deleted the tabledef and then attempted to find it's name. Why not keep the tabledef and just change the Connect?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[nikademous]

Duane, Im very green to VBA I pieced that from the net. The (dbs.TableDefs.Delete strTblName) is deleting tables that start with "msys" which must be hidden tables. I see them on the first debug.print and on the second debug.print its just my tables that are in the BE I choose. Now I need to move those to the FE I choose and what it does is deletes all the tables in my FE BUT the one I newly added and that's the one I needed to link.

 

[strongm]

>deleting tables that start with "msys"

That's not what it is doing

 

[nikademous]

Ok so what is it doing, bypassing them and only getting the tables? What am I missing in my VB above that will get this working?

Thanks,

 

[dhookom]

The first thing I noticed is you [highlight #FCE94F][/highlight]are not declaring your variables properly. It should be:

    Dim dbs[highlight #FCE94F]BE As DAO.Database[/highlight], dbs[highlight #FCE94F]FE[/highlight] As DAO.Database
    Dim tdf[highlight #FCE94F]BE As DAO.Tabledef[/highlight], tdf[highlight #FCE94F]FE[/highlight] As DAO.TableDef
    Dim strFEFile[highlight #FCE94F] As String[/highlight], strBEFile[highlight #FCE94F] As String[/highlight], strTblName As String

Notice I renamed the variables so your code is much easier to understand.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Just to clarify what Duane is doing - aside from renaming your variables - declaring your variables correctly.
Otherwise, your code did this:

Dim dbs [red]As Variant[/red], dbs1 As DAO.Database
Dim tdf [red]As Variant[/red], tdf1 As TableDef
Dim strFEFile [red]As Variant[/red], strBEFile [red]As Variant[/red], strTblName As String
...

If you don't specify the type of your variable, it is [tt]Variant [/tt]by default.


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

>so what is it doing, bypassing them and only getting the tables? >

Yep.

> What am I missing

Quiet a lot. The code is not doing what you think it is. Unless the current database is also the front end database. And even then it isn't quite right. So here's a slight reworking, with some variable renaming, that may help:

[blue]Private Sub Link()
    Dim dbFE As DAO.Database
    Dim dbBE As DAO.Database
    Dim tdf As TableDef
    Dim tdf1 As TableDef
    Dim strFEFile As String
    Dim strBEFile As String
    Dim strTblName As String
    
    strBEFile = Me.txtBackend
    strFEFile = Me.txtFEMaster
   
    Set dbFE = DBEngine.Workspaces(0).OpenDatabase(strFEFile)
    Set dbBE = DBEngine.Workspaces(0).OpenDatabase(strBEFile)
    
    On Error Resume Next [COLOR=green]' As long as you know what you are doing here (i.e. pretty much hiding ALL errors from here on)[/color]
    For Each tdf1 In dbBE.TableDefs
        strTblName = tdf1.Name
        If Left(strTblName, 4) <> "msys" Then
            dbFE.TableDefs.Delete strTblName
            Set tdf = dbFE.CreateTableDef(strTblName)
            tdf.Connect = ";DATABASE=" & strBEFile
            tdf.SourceTableName = strTblName
            dbFE.TableDefs.Append tdf
        End If
    Next tdf1
    
    Set dbBE = Nothing
    Set dbFE = Nothing
End Sub
[/blue]

 

[nikademous]

strongm, I tried your altered code and it worked. I been banging my head over this for some time....

Thanks,