UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Split Db Structure, Access 2010    
 
   
dhapp
post Jul 30 2020, 04:43 PM
Post#1



Posts: 1,197
Joined: 17-November 03
From: Hamburg, NY


I am looking for a reality check.

Background: I just got a new client and my role will be to modify current functionality and add functionality to various front end applications. The existing Access databases are of various origins, but it seems that most of the development has been done one person (I'll refer to him as Z.) who is also involved in all of the IT, network management, desktop workstations, etc.

I was invited to do development because Z cannot keep up with all of the IT responsibilities, plus a current initiative to upgrade workstation still using Windows 7, and a growing list of modifications to the existing DBs. I questioned Z on the general structure of the db, specifically where is the back-end stored, and how does he distributes new versions of front-end apps.

Some back end files are stored in a network folder which has an alias of S:, and others are in network folders that are subfolders of alias N:. So there are files in N:\Data, N:\Labels, N:\Office, etc.

Regarding distributing the update front-end apps he puts them all into various folders on the N: drive and has put shortcuts on the desktops of each user. A user only has shortcuts for the apps s/he uses. From my understanding and experience, this is not the best way to manage front-end applications.

Another peculiarity that I have observed is that in one of the back-end files that Z sent to me some of the tables were links to tables in another file. I have no experience in this structure. It seems like trouble to me.

I also observed that in one of the front-end files he stores data. Since that front-end is stored in a network folder it is accessed by multiple users via shortcuts, and it is backed up daily because of the whole network backup schedule.

In my understanding,
All multiuser data is stored in files stored on the network.
Each front-end app is stored on one or more workstations. The front-ends have links to the back-end tables that are needed for functionality. The front-end may have local tables that are used as needed; those tables do not need to be backed up.

My questions
1. Am I correct that some of Z's structure is possibly problematic?
2. What is the most succinct way for me to inform Z that he should reconsider how things are structured?

I want this to be as non-confrontational as possible.
Thank you
Doug

--------------------
Doug
When the eagle is away, the crow says, "I am the eagle."
Go to the top of the page
 
tina t
post Jul 30 2020, 10:11 PM
Post#2



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


well, i can comment on a couple points:

QUOTE
Regarding distributing the update front-end apps he puts them all into various folders on the N: drive and has put shortcuts on the desktops of each user. A user only has shortcuts for the apps s/he uses.

it's not clear what the setup is here. does each user's hard drive have a copy of each FE app that he/she uses? or a link to a server copy of the FE app(s) that only he/she uses? or are several users using the same copy of a FE app that's sitting on the server?

a copy of the FE app(s) sitting on the users' hard drives is optimal. a dedicated copy of the FE app for each user, all sitting on the server, should be okay from an Access standpoint. it's more line traffic, of course, because the FE (parts? all at once? i don't know.) has to be pulled to the user's PC, in addition to the data from the BE db. the important point is that each user opens a FE file that no other user opens.

bad news is having a FE file on a server, with more than one person opening that same file.

QUOTE
Another peculiarity that I have observed is that in one of the back-end files that Z sent to me some of the tables were links to tables in another file.

well, it probably doesn't matter, if the linked tables are Access tables, and if the data is being accessed by one of the prior-mentioned FE apps. in Access, you can't link to a linked table, you can only import the link.

say you have two BE dbs, dbA and dbB. Table1 resides in dbA. dbB has a link to Table1 in dbA. now you have a FE db. in the FE, you can create a link to Table1 in dbA. you cannot create a link to the Table1 link in dbB. you can import the Table1 link from dbB - and now you have a direct link from your FE app to Table1 in dbA - dbB is not involved.

QUOTE
My questions
1. Am I correct that some of Z's structure is possibly problematic?
2. What is the most succinct way for me to inform Z that he should reconsider how things are structured?

1. possibly. some clarification is needed (by me, anyway - it may be perfectly clear to others!)

2. well, this is a business issue, and i'd present it as such: "after a review of your setup, I have the following recommendations for changes, including the reason for each change and the benefit to be gained." in a written document, thorough and concise. if Z reacts on a personal level, rather than a business level, that can tell you a lot about how difficult fulfilling your role will be. you won't be able to control his choices in regard to the project, so that leaves you with making your own choices about what you're willing to deal with in order to keep this client and complete the project - especially how much effort you may have to put into CYA instead of putting it into getting the work done successfully.

anyway, those are my thoughts, for what they're worth. there are numerous folks here who do/did contract work for a living, so hopefully you'll hear from some of them, with solid experience and advice to share. good luck! :)

hth
tina
This post has been edited by tina t: Jul 30 2020, 10:12 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Jul 30 2020, 10:23 PM
Post#3


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


QUOTE
I also observed that in one of the front-end files he stores data. Since that front-end is stored in a network folder it is accessed by multiple users via shortcuts

Why?

--------------------
Robert Crouser
Go to the top of the page
 
dhapp
post Jul 30 2020, 10:34 PM
Post#4



Posts: 1,197
Joined: 17-November 03
From: Hamburg, NY


Tina,

Thanks very much for your answer; it helps a lot.

To clarify the FE, BE structure based on a single conversation with Z on this topic: The BE resides on the server. The front ends all live together in one or more locations on the server.
Each user has one or more shortcuts on their desktop (or in a folder on the desktop) that links to a front end that is in a server location. Each shortcut links to a specific front end.

I meet with Z plus a production manager tomorrow morning.

Doug

--------------------
Doug
When the eagle is away, the crow says, "I am the eagle."
Go to the top of the page
 
dhapp
post Jul 30 2020, 10:48 PM
Post#5



Posts: 1,197
Joined: 17-November 03
From: Hamburg, NY


Robert,

I'm not sure I understand what your "Why?" is referencing.

QUOTE
I also observed that in one of the front-end files he stores data. Since that front-end is stored in a network folder it is accessed by multiple users via shortcuts


I think the table in question should reside in a BE file and the FE should be linked to it. I also think that the FE should reside on the user's workstation, not have the user(s) opening an app in a network folder via a shortcut.

Why did Z set it up this way? Good question.

Doug



--------------------
Doug
When the eagle is away, the crow says, "I am the eagle."
Go to the top of the page
 
projecttoday
post Jul 30 2020, 11:04 PM
Post#6


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


So ask him "why?". And if he can't give a good reason then change it.
In my experience, only temporary tables go in the FE.

--------------------
Robert Crouser
Go to the top of the page
 
dhapp
post Jul 30 2020, 11:12 PM
Post#7



Posts: 1,197
Joined: 17-November 03
From: Hamburg, NY


Robert,

Yes, I thoroughly agree.

Doug

--------------------
Doug
When the eagle is away, the crow says, "I am the eagle."
Go to the top of the page
 
isladogs
post Jul 31 2020, 01:47 AM
Post#8


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


Just to add to the good advice already received, I would recommend that each user has a copy of their FE on their own hard drive rather than on the network. This should improve performance and keep users away from the server.

It is perfectly OK to have linked tables in more than one location, however of course it is impossible to set referential integrity on tables from different databases.
Most of my commercial apps will have linked SQL and Access tables.
In one particular app, there are 7 different linked databases, 3 SQL and 4 Access. That is unusual though not all links are in use at the same time.

There should NEVER be data tables in the FE with the possible exception of temporary buffer tables.
However even those would usually be better in a separate 'side end' database to prevent bloating the FE

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
tina t
post Jul 31 2020, 07:58 AM
Post#9



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


QUOTE
Each user has one or more shortcuts on their desktop (or in a folder on the desktop) that links to a front end that is in a server location. Each shortcut links to a specific front end.

hi Doug, i'm still not sure we're on the same page, here. the point is, do userA and userB each have a link that points to the same front end file on the server? or are there two copies of the FE file on the server - userA's link points to copy1, and nobody but userA uses copy1, and userB's link points to copy2, and nobody but userB uses copy2, etc, etc.

if it's just me being dense re your statement, sorry! but if Z is not being clear, recommend you go back to him for clarification. IMHO, this is the most important point you raised in your first post. a single copy of a FE file being shared by multiple users, is a recipe for corruption.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
dhapp
post Jul 31 2020, 10:08 AM
Post#10



Posts: 1,197
Joined: 17-November 03
From: Hamburg, NY


Tina,

Nope, you are not being dense.

QUOTE
if it's just me being dense re your statement, sorry! but if Z is not being clear, recommend you go back to him for clarification. IMHO, this is the most important point you raised in your first post. a single copy of a FE file being shared by multiple users, is a recipe for corruption.


I am quite sure that Z stores only one copy of each front end on the server and each user of that App has an identical shortcuts ,i.e. a recipe for corruption.
I will investigate this more when I can.

I very much appreciate your persistence on this issue; as my first sentence stated "I am looking for a reality check." .and you are providing that. In addition to dealing with Z, I also need to show management that I m not alone in my opinions on the db structure.

I also appreciate your suggestion regarding communicating with Z; it helped me frame an approach.

Doug

--------------------
Doug
When the eagle is away, the crow says, "I am the eagle."
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:27 AM