1 front end, multiple backends for different uses (1 Viewer)

darren_access

Registered User.
Local time
Today, 04:43
Joined
Jan 8, 2018
Messages
57
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

Well-known member
Local time
Today, 07:43
Joined
Apr 9, 2015
Messages
4,337
yes, you CAN have 1 FE to many BE.
Usu its many FE, but you can have 1 if you want.
 

MarkK

bit cruncher
Local time
Today, 04:43
Joined
Mar 17, 2004
Messages
8,178
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

Super Moderator
Staff member
Local time
Today, 07:43
Joined
Jan 23, 2006
Messages
15,361
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)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:43
Joined
Feb 19, 2002
Messages
42,970
Sounds like you are thinking about making Identical copies of the BE and letting different groups put their own data into the different copies.

Yes, you can do it. Should you? Absolutely NOT. This is a poor design strategy. Companies spend millions of dollars to purchase ERP systems to integrate the multitude of back stage apps so they all talk to each other. You are thinking about going the opposite direction for some reason.

Please tell us what you are thinking this will gain and we'll help you get the design sorted out.
 

darren_access

Registered User.
Local time
Today, 04:43
Joined
Jan 8, 2018
Messages
57
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:43
Joined
Feb 19, 2002
Messages
42,970
I wasn't suggesting an ERP. I was pointing out why companies spend millions to implement them. You are making a nightmare for yourself.
#1 you will not be able to use autonumber primary keys if you expect to merge the data at some point.
#2 you will not have consolidated reporting.
#3 Jet/ACE can hold a lot of data if you properly normalize it and don't bog it down with OLE objects. Millions of rows are no problem.
#4 If the 2G limit really becomes a problem, you can split the BE to isolate some tables in one BE and other tables in other BE's. You loose RI and you would have to implement it via code but the real limitation with Jet/ACE as the BE is that no single table and its associated indexes can exceed 2G. SQL Server Express has the same 2G limit as Access unless it has been raised.
#5 If you use a little foresight, converting to SQL Server or some free RDBMS will be trivial - but only if you know how to use proper client/server design techniques. Typically, I can convert an app I built from ACE to SQL Server in under an hour. In fact I have one that my client sells to the public that can hot-swap between ACE and SQL Server depending on what the client wants to use. He simply uses my relinker and swaps. There was only one small procedure where I needed separate code for ACE and SQL Server. Otherwise all the code is identical. Build for SQL Server and ACE is happy.
 

jleach

Registered User.
Local time
Today, 07:43
Joined
Jan 4, 2012
Messages
308
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:43
Joined
Feb 19, 2002
Messages
42,970
I just checked. It looks like Express is up to 10g.
 

Mark_

Longboard on the internet
Local time
Today, 04:43
Joined
Sep 12, 2017
Messages
2,111
@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.
 

Users who are viewing this thread

Top Bottom