VB Code to link backend tables to frontend?

[DahMurf]

I want to link my frontend to my backend tables using VB. I want to do this when I open the database and then drop the link when I close the database. I am only using Access 2000 for both the frontend & backend.

I've seen a lot of posts saying you should do this & I've seen several with examples of linking to tables other then in Access. I've tried to do research & read the help but I'm still lost.

Could someone post an example of code used to do this for Access only?

Thanks in advance!

 

[MoLaker]

Look up DoCmd.TransferDatabase in Access Help. One option is "acLink". Just make sure you delete the link before you attempt to relink or you will wind up with table, table1, table2, etc.

 

[DahMurf]

Thanks! That helped to a point. I'm still not getting it correct tho. I saw that I could do the link in a Macro so I tried it just to see if I could make it work and of course it did. When I go back to my code I can't get it to work.

This is what I have coded:

    Dim bePath As String
    bePath = "c:/aa_share/ecta/Test_Development_be.mdb"
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    bePath, acTable, tbl_membership, tbl_membership

Can anyone tell me where I'm going wrong?

 

[MoLaker]

Dim bePath As String
bePath = "c:/aa_share/ecta/Test_Development_be.mdb"
DoCmd.TransferDatabase acLink, "Microsoft Access", _
bePath, acTable, [red]"[/red]tbl_membership[red]"[/red], [red]"[/red]tbl_membership[red]"[/red]

The table names s/b within quotes. As for using a variable for the DB path, I guess it will work. But, if you still have trouble, try using the path string (with quotes) intead of the variable.

 

[DahMurf]

NEVERMIND!!!!!!!!!

Here's two ways that work!
(There's a reason why I ask for examples!!! I'm usually just missing something small!)

    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    "c:/aa_share/ecta/Test_Development_be.mdb", _
    acTable, "tbl_membership", "tbl_membership"

    Dim bePath As String
    Dim beTable As String
    bePath = "c:/aa_share/ecta/Test_Development_be.mdb"
    beTable = "tbl_membership"

    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    bePath, acTable, beTable, beTable

 

[DahMurf]

Thanks a bunch MoLaker! I guess we were posting at the same time!!!

Deb

 

[MoLaker]

Your efforts gave me an idea I had never considered (even if the need has never arisen).

By assigning the path and table names to variables as you have done, I was picturing the ability to open a workspace and list the tables in the back-end using the tables collection. Then use that list to create the links. This would make it feasible to add tables to the back-end and have the front-end link to the new tables automatically w/o any change in code. Hmmmm.