Split database with linked tables

[Janelle.Dunlap]

I have an Access database that I need to split into a front-end and
back-end to allow multiple users. Currently my database is linked to
an external Excel spreadsheet and it will not recognize this linked
table when I go to split the database. Is there a workaround for this?
Is it possible to split the database without any tables, add a linked
table to the front-end that points to the spreadsheet, and then link
this table to the back-end? Thanks in advance for any suggestions!

 

[Jeff Boyce]

Why do you feel you need to link to both backend and frontend?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You can remove the link to the xls, split the db, and then re-link to the xls
from the front-end db.

 

[Janelle.Dunlap]

I thought this was how a split database works - the BE and FE share
linked tables. I could be wrong.

 

[Jeff Boyce]

A front-end can link to any number of back-ends. If some of your data is in
an Access .mdb file, and some is in Excel, and some is in a DB2 database,
and some is in ..., you can use a front-end and links to all these. You
would NOT need to create links to any of these from within the back-end
(remember, you could have several).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Janelle.Dunlap]

Ok...this is making more sense to me but I am unclear on how I would
physically split my database. Do I just do it manually by creating an
empty database and then linking to my spreadsheet, which creates my
back-end, which I point to my front-end containing my forms, coding,
etc? If so, how do I go about pointing my front-end to the back-end
without using the Database Splitter Tool. The reason I ask is that it
currently won't let me use this tool to split my db beacuse it doesn't
recognize that I have any tables in my db (I only have 1 linked table
to excel). Hopefully I am making sense. I appreciate your help.

 

[Janelle.Dunlap]

I think I figured out how to split manually....by making two copies of
my db, deleting the table in one to create a FE and deleting the forms,
coding, etc in the other to create a BE. Problem is when I go to the
FE to link to the table in the BE it still is not recognizing the
table, I believe because it is already linked to a spreadsheet. I am
at a loss of what to do.

 

[Jeff Boyce]

I may be mis-reading, but it sounds like you are leaving a link to the
spreadsheet in the back-end. You don't need to link to the spreadsheet from
the back-end, only the front-end.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Janelle.Dunlap]

You are not misreading, maybe I am still confused....I though the BE is
supposed to contain all the data (or tables) while the FE contains only
the application (forms, coding, etc.). I was thinking that I needed to
link the spreadsheet to the BE and then link this table from the BE to
the FE.

 

[Ron2006]

Think of it this way: You cannot link to a link.

The idea of the FE is that no tables are local to it. And the FE can
point to many sources of data - to many BE. In your case one of your
BEs is an mdb and the other is a spreadsheet. FE-BE says that the FE
contains no tables not that one BE contains all the data/tables.

Physically each one of your tables could be in a different mdb. You
would still have a FE-BE situation. Forms in the FE and all data in
BEs. (In fact this is one way that you could get around the max 2G size
for a mdb - spliting the tables amoung many mdbs.)

You FE should simply point to whatever BE file contains the data.

Ron

 

[Janelle.Dunlap]

Thanks for the reply, I think I understand now. My BE is actually my
spreadsheet, and since I only have one table I guess this also means
that my database is already split. Thanks for the clarification.

 

[dbahooker]

Ron

I strongly dis-agree with your diagnosis.

A front-end backend split is typically the 'large tables' on the
backend and the 'small tables' on the frontend.

Either way; splitting is worthless.

you would have better results if you kept all your data on 1 server and
you used Access Data Projects.

with ADP you dont' ahve to link to other databases; you can just refer
to them with the 3-part naming convention database.owner
(schema).object

 

[Ron2006]

That is great if your IT dept would let you use SQL. If they won't (and
ours won't) our options are cut significantly.

MDB is not ideal solution, but it sure beats trying to share
spreadsheets.

 

[Ron2006]

As to putting the large vs small tables in one or the other, it is
rather a point of whether independent users are going to be changing
the data in those small or large tables.

Static tables are definitely items to consider keeping in the FE.

But obviously non-static (dynamic - multiuser updatable) tables need to
be in the BE.

 

[dbahooker]

your IT dept won't let you use SQL?

it's an OPTIONAL OFFICE component.

MSDE 2000 is included on the Office 2002 and 2003 disc; arent' they?

-Aaron