New Table in a split db HOWTO

  • Thread starter Thread starter Roy Ristie
  • Start date Start date
R

Roy Ristie

LS,

having split my db into fe and be, I now wonder what steps to take when I
have to define
a new table. So far I have;

a) fe and be which relink on startup.
b) function to allow the user to search for the be in another folder should
step a) fail.

My questions;
1) As any newly defined table (defined in the be) is unknown to the fe, how
do I proceed at relink time?.Should I maybe define it in the fe and than
move it to the be?

2) What if I decide that a (lookup) table should be moved from be to fe.
Does Access realize that this table is nolonger remote?

Thanks for any replies.

--Roy--
 
Here's an example from a current project ...

'Get the path to the back-end from the Connect property of an
'existing linked table (be sure to do this *after* any relinking).
strDataFile = CurrentDb.TableDefs("tblAbsenceReason").Connect
strDataFile = Mid$(strDataFile, InStr(1, strDataFile, "=") + 1)
Set db = DBEngine.OpenDatabase(strDataFile, True)

'Now create the table in the back-end.
Set tdf = db.CreateTableDef("tblNEWBReason")
Set fld = tdf.CreateField("NEWBCode", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("NEWBDescrip", dbText, 50)
tdf.Fields.Append fld
Set fld = Nothing
db.TableDefs.Append tdf
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("NEWBCode")
tdf.Indexes.Append idx
Set idx = Nothing
Set tdf = Nothing

'And now create the link in the front-end.
Set tdf = CurrentDb.CreateTableDef("tblNEWBReason")
tdf.Connect = ";DATABASE=" & strDataFile
tdf.SourceTableName = "tblNEWBReason"
CurrentDb.TableDefs.Append tdf
 
I am sure the Access gurus on the forum are going to scream, but this is what
I have done when I had to add another table to the BE.

Remember to back up your FE and BE before doing this.
----------------------------------------------------------------

1. Add new table to FE
2. Split DB again, and choose the same BE

HTH
Rajesh
 
Roy said:
LS,

having split my db into fe and be, I now wonder what steps to take
when I have to define
a new table. So far I have;

a) fe and be which relink on startup.
b) function to allow the user to search for the be in another folder
should step a) fail.

My questions;
1) As any newly defined table (defined in the be) is unknown to the
fe, how do I proceed at relink time?.Should I maybe define it in the
fe and than move it to the be?

2) What if I decide that a (lookup) table should be moved from be to
fe. Does Access realize that this table is nolonger remote?

Thanks for any replies.

--Roy--

In short you need to replace or edit the FE.

I always created a new FE from a copy of the old one and then made all
the changes I needed then distributed it to all the FE users that needed the
new copy.

When you have a lot of users, you learn that keep everyone up to date
can be a problem and there are solutions for that as well.
 
Thanks everyone, I've tried Brendan's solution and it works fine.
Problem solved!

Thanks again,
--Roy--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Back
Top