×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Update table links from a utility to separate BE and FE

Update table links from a utility to separate BE and FE

Update table links from a utility to separate BE and FE

(OP)
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.

CODE -->

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 

RE: Update table links from a utility to separate BE and FE

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

RE: Update table links from a utility to separate BE and FE

(OP)
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.

RE: Update table links from a utility to separate BE and FE

>deleting tables that start with "msys"

That's not what it is doing

RE: Update table links from a utility to separate BE and FE

(OP)
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,

RE: Update table links from a utility to separate BE and FE

The first thing I noticed is you are not declaring your variables properly. It should be:

CODE --> vba

Dim dbsBE As DAO.Database, dbsFE As DAO.Database
    Dim tdfBE As DAO.Tabledef, tdfFE As DAO.TableDef
    Dim strFEFile As String, strBEFile As String, 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

RE: Update table links from a utility to separate BE and FE

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

CODE

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

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


---- Andy

There is a great need for a sarcasm font.

RE: Update table links from a utility to separate BE and FE

>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:

CODE

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 ' As long as you know what you are doing here (i.e. pretty much hiding ALL errors from here on)
    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
 

RE: Update table links from a utility to separate BE and FE

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

Thanks,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close