1 front end, multiple backends for different uses

[darren_access]

Can I have one FE link to various other BE's for different purposes?

1 fronted with a generic app that stores the data in back-ends dedicated to line of business, such as a finance BE, project BE, inventory BE. Thus allowing cross functional querying between tables in different BE's.

Possible?

Normally I would do this with SQL Server, but that may not be an option here. At least not yet.

 

[Ranman256]

yes, you CAN have 1 FE to many BE.
Usu its many FE, but you can have 1 if you want.

 

[MarkK]

If the question is, "Is it possible?" clearly the answer is "Yes."

But if this is one enterprise, it doesn't make sense to me to have your finance, project, and inventory data in different BEs. Obviously you can do it, but why would you?

Mark

 

[jdraw]

Darren,
Can you tell us more about the organization and its business? You can do this, BUT because it's possible doesn't mean you should. Many organizations spend considerable resources to design , build and maintain more integrated corporate/enterprise systems. Usually SQLServer, Oracle etc not Access.
But why would you want different BE for lines of business? (Seems to go against optimization and efficiency concepts)

 

[darren_access]

haha, i knew this question would raise some eyebrows ...

I have worked with ERP systems before but this company definitely can't afford one ($$) and have some systems already in disparate access db's.

The company is small with only 30 people. I'm only thinking of a distributed architecture to avoid the size limitations of 2GB. Each BE would have tables dedicated to it's LOB with common keys and convention. The front end would have forms and reports that would connect to appropriate BE's. Can front end reports join tables in different backends?
Is there a best practice using ACCESS for this? Or should I consider SQL Server Express?

 

[jleach]

The 2GB limit is (maybe tied with better data security) the number one reason to move to SQL Express (usually).

Pat's right on the money here. Splitting out Access BEs will cause you no end of trouble down the road.

Quick edit: Pat stated 2GB limit in SQL Express, which I thought was 10GB. A quick search (completely informal, please verify on your own) states:
2008: 4gb
2008R2: 10gb
2012: 10gb
2014: 10gb

 

[Mark_]

@Darren,

Is this because you wish to keep large image files inside your database instead of keeping said files only on the server? If so I would really really advise not doing so. The first time you get asked "How can I edit this file now?" you'll start seeing just how much of a pain it will be.