Linking Tables TO another DB

[GracieLou]

I am using Access 2003 SP2.

I have a masster db and several working db's. When I create a table in the
master db is it possible to link that table from the master to a working db?
I know I can link it while in the working db, but can I do it from the master?

It seems to me that the lined tables would be better managed if I can set
the link from the master.

I've tried using the TransferDatabase, but that seems to require you to be
in the working db.

Any help would be greatly appreciated.

Thanks

 

[Klatuu]

What do you mean by Master and Working Dbs?
That is not a term normall used in Acces.
Typically, when correctly configured, you have a Front End mdb that contains
all the form, report, query, class, module, and macro objects. You have one
or more
Back End mdbs that contain only tables and relationships.
You link to tables in the Back End mdbs from the Front End mdb.
If this is not what you are doing, please explain.

 

[Pat Hartman \(MVP\)]

You can do it by setting the dao.database object to the database you want
the link to be in. However, this only works if you have a fixed (and
limited) set of front ends that you want to link to back end tables. If you
always want your front end to link to ALL tables in the back end, you can
look for new tables in your front end's opening form where you can link them
regardless of how many front ends you have. If you want to control which
tables get linked, you can prefix them and just link the ones with the
particular prefix. The name of the table in the source database may be
different from it's name in the linking database so you won't have to modify

 

[GracieLou]

Sorry about the terms.

I have one db that holds all of the data in different tables. I call this
my Master. I have three other db's that have no actual data, just linked
tables from the Master. I do all of my queries, forms and reports in the
other db's.

What I am trying to do is when I create a new table in my Master is to link
that table to a working db programmically rather than open one of the working
db's and going through the menu choices to link the table there.

I hope this is more clear.

Thanks

 

[GracieLou]

Pat,

Thanks for your response. My mai problem is that I have never had any
formal training in Access and what I do know is from doing. Meaning I don't
always understand the termonology. You mentioned setting the DAO.Database
that I want to link in.

What I used to get as far as I did was code I found in "Programming
Microsoft Access Version 2002, page 233". That code is, modified with my
names;
Sub link02GLYr1()
Dim rstl As ADODB.Recordset

DoCmd.TransferDatabase acLink, "Microsoft Access", _
"J:\Brown\Databases\02glyr1", _
acTable, "Gl_detail_503_2003Orig", "Gl_detail_713_2003Orig"

Set rstl = New ADODB.Recordset
rstl.ActiveConnection = CurrentProject.Connection
rstl.CursorType = adOpenKeyset
rstl.LockType = adLockOptimistic
rstl.Open "02glyr1", , , , adCmdTable
Do Until rstl.EOF
Debug.Print rstl.Fields(0).Value, rstl.Fields(2)
rstl.MoveNext
Loop

rstl.Close
Set rstl = Nothing

End Sub

Where would I put the DAO.Database? My master db, or back-end, is Brown.
This hold the data. I do have 3 front-ends, 02GlYr1, 03GLYr2, and 04Glyr3.

I do hope this makes sense.

You guys are a tremendous help.

I really appreciate what you do for the rest of us.