Linking multiple databases - is this a good idea?

[ScottishFi]

Hi there



I need a steer on the best way to approach something. I have a split DB which contains all our company contacts, primarily used as an events invitation/attendance record. At the moment I filter contacts by team and send them Excel spreadsheets to say Yes/No on invites and then have a process to upload them again as an Append. But this does not work in practice as everyone changes contact details, deletes/adds people or my favourite - sends me back a completely different list in a totally different format. As I don't want to have to fix/update the data all manually this time I'm looking for a new approach.

The main criteria is that I want each team to access and update only their own contacts. How best to approach this? I'm vaguely thinking:

• Linked Excel spreadsheets with a locked format which automatically update DB tables. But how would I filter? A query that creates a table and link that table?
• A DB for each team which is linked to the main one - as above not sure how I filter?
• Or I have a log-in screen that checks passwords, should I add a team column to my staff table and filter on that at log-in? Just sticking with the single DB? This is blue-sky thinking, I don't know VBA and had to copy the code for my login screen so this option may be beyond me :-)

Any thoughts on best practise or absolute no-nos would be welcome, just to start me off down the right track

Ta v much

[Minty]

You could have a small "external" database they could edit just their records in and you then update your master records, the problems you'll have is that distributing it is an issue, you'll have to zip it if you email it...
Unless you could rely on them to collect them from a network share?

Locked spreadsheet is probably the simplest route. Get someone to sign off on a "Send this back or nothing" company policy.

End users are always a problem. Life would be much easier without them

[ArviLaanemets]

You must have:
a) in your contacts table some field, which allows to assign contact to certain team (one team per contact);
b) or you must have a separate table, where you can assign various contacts to teams (like tblTeamContacts: TeamContactID, TeamID, ContactID where TeamID + ContactID are components of unique index - same contact can be assigned to several teams).

And you have to split the database to front-end and back-end. For every team you create it's own front-end, where instead of whole contacts table only contacts assigned to this team are linked (p.e. you can create for every team a view in backend DB like

SELECT c.* FROM tblContacts c WHERE TeamID = c.ThisTeamID

or

SELECT c.* FROM tblContacts INNER JOIN tblTeamContacts tc ON tc.ContactID = c.ContactID WHERE tc.TeamID = ThisTeamID

or

SELECT c.* FROM tblContacts c WHERE ContactID In (SELECT tc.ContactID FROM tblTeamContacts WHERE tc.TeamID = ThisTeamID)

, and instead of tblContacts you link this view to team front-end).

[ScottishFi]

I think I see what you mean, is this code creating a query in the Backend for each team? Then I link the query to the team front end rather than the full contacts table?

[orange]

Why is Excel involved?
Watch this free youtube by Steve Bishop on User Access for ideas.

It might be helpful to readers if you told us more (even an example) of what is involved and what you really need.

[ArviLaanemets]

I think I see what you mean, is this code creating a query in the Backend for each team? Then I link the query to the team front end rather than the full contacts table?

Yes - you create a saved queries for ech team. Or you use this (edited) query text as Source for form instead of tblContacts (in which case you link the full tblContacts, but you use only team's contacts from it).

As always - in MS programs are always at least 3 different ways to do things

[ScottishFi]

Great, thanks. I think I had that idea roughly in my mind but was overcomplicating it. Really it's just a series of front ends accessing different queries. Ideal, I can manage that!

[ScottishFi]

Thanks for replying. I've used this before to help build a login for a different DB but am a bit daunted with trying to restructure the code. To be honest, I think I'm avoiding having to restructure what's already in place - I have to deliver this by tomorrow so needed a quick fix! Will bear it in mind for future development tho. cheers.