Single frontend with multiple backend (1 Viewer)

[sohailcdc]

Hi there, please be patience English is not my first language, therefore, nor I am technical persons in terms of explain my situation related to Access

First and foremost I am not professional developer nor I have any advance level skills and or any formal education, however, based on information available online I am trying to learn during my free time

Anyways, by profession I am accountant, therefore, I tried to build an application using “MS Access” in order to capture certain information which will be helpful in future with reference to my job requirements

This application basically helps me to capture Freight variance, provides the freight for unit, any major change in shipper freight invoices etc

I am currently, working in a company where we have more than 5 different units and unfortunately\fortunately; I am solely responsible “supervisor sort of role” related accounting activities associate with freight such as monthly, quarterly forecast, budgeting etc

As I said I am not a professional developer, therefore, the application which I build (with help of this website and other online material), works for me and for each different unit I am using separate database with different name and have shortcut on my desktop (works for me great)

Now, here is the twist, now my supervisor said, instead of entering all information by yourself, why don’t give access to other staffs in the company, so, that I may get some free time (in nutshell, he actually wants to transfer some of his stupid works to me ..... )

Here is my question based on my situation, Now, I need to give access to other staffs

Luckily, all database and/or table structure and/or forms are same the only difference is I change the name of database based on unit name

This is what I am thinking since, table name, structure and forms are same, I can simply split the application (Front and Backend) store the backend in common drive and provide the copy of frontend to each user

I can simply add in the start-up screen the list box and/or combo box, from where user can select the name of unit (company name), in which s\he want to enter the information

Now I have two big questions
1) How the front end “List Box or Combo Box” knows upon update event that, which database needs to select and/or open
Company Name Database Name
Company ABC DB-ABC
Company 111 DB-111
2) How I can ensure the all the table are liked from the selected database and are synchronize with link table

Sorry for my long story, but I need VBA code which I can put on the startup

 

[GinaWhipp]

So, if I understand you correctly you have multiple backends and each one is basically the same and depending on who logs on depends on which backend they link to? Okay, just one question... Why?

If they are set up the same why not one back end which your Users can then connect to one backend AND you can seperate what they see by their Log On to the database. Your way, is a nightmare to maintain, what happens when you make updates? You have to go to all those backends?

That said, I have not done it that way but I'm sure it's possible. Going to need a Table or two with the VBA, VBA alone is not going to get it. I'm going to hope someone passes by with that code cause just not in my code library.

 

[sohailcdc]

thank Gina
As I said I am not a professional developer nor I make this application for others use (originally, I made this for my own use and I am keeping this on my desktop, but since I am using this, therefore, my supervisor know about this and he is insisting me to do so) as I said above right now it works perfect for me

Second option I have change in the application and add company code in each table to segregate the data, which means I have to re-build the application (which is pain) or, like i have separate database simply give them to other separate link for each database

Anyways, thanks for your suggestion

 

[GinaWhipp]

Hmm, okay well let me put it like...

Pay now or pay later.

Your way you are going to pay later and keep paying, my way you pay once and everything else is down hill. Up tp you... Unfortunately, as no one does it that way you might have to wait this out and hope some has code for this situation OR *Google is your friend*, you might get lucky!

 

[vbaInet]

Maybe sohaildc isn't explaining clearly or he's not understanding what's being questioned.

Sohaildc, do you know that your users can each have their own front end and all of them can connect to one backend? If so, does your boss know this is possible too?

Or is it that your boss knows that you only need one Back End but he's insisting that everyone must have their own back ends? Does your boss understand how databases work?

 

[sohailcdc]

My boss just want to get rid of this from me so that he can assign me new and/or transfer his work to me lol

Nutshell, I made this application for my personal use, then I made the 5 copies of same databased and used for individually for each unit

Now, my boss want to give this access to other user, so that they can input the information in database instead of me

Now I have 5 databased (all table fields, forms everything same for all the databases), for which i have to give them access to other user all 5 database, so that user can enter the information in the respective company

What I am thinking but unable to find any solution is

Since, all the table and forms are same, therefore, I can put 5 different database backend at common drive provide the ONE frontend to each user and add one List Box in the start up form, where user can select the company, and based on the selection of company Access connect with respective company backend database

 

[GinaWhipp]

Right... custom code, not the norm. It can be done just going to be a bit of a bear.

 

[vbaInet]

You're going about it the wrong way. Just use one back end and 5 front end. All the front ends will connect to one back end without any issues.

The only thing that springs to mind about doing it way you're thinking is Replication but I don't think that's supported in newer versions of Access, i.e. the new accdb file formats. But you're better off having one back end and 5 front ends.

 

[GinaWhipp]

@vbaInet,

Confirmed... No more Replication and while you can utilize in Access 2007 and 2010 it is completely gone from Access 2013, so you might as well say forget it.