Question Efficient way to handle linked databases?

[jonathanchye]

Hi all,

I have a question about linked databases which I hope someone could help me answer.

What I have now is a master User database containing fields like User Name, Password, Access Level, System Name (type of program they have access to), Logged On/Off flag, LoggedOn Date and Time and also LoggedOff Date and Time.

This sits in the central server and I am trying to make it so only I have access to this. I am in the process of creating a DB (let's call this DB1) using MS Access 2010 that has a login form which would grab info from the central User DB.

I got it running by linking the tblUserList from the Master DB and creating a local query to this tbl to filter out only the relevant Users in the correct System Name (DB1 in this case). This works fine at the moment but I noticed DB1 would have the UserList table open and thus I can't implement any design change on Master User DB if this is the case. I also feel that local users having access to DB1 would be able to temper with the user name/passwords etc which ultimately defeats my point of having a Master User DB...

So, question is : Is it possible just to run a query to an external DB without having to link the table from the external DB? I can't seem to do this. I can create the query but no information is shown as it needs to read from the UserList table from Master DB...

Any help much appreciated. Thanks!

(Summarised version : I am trying to create a central DB which contains admin data which I want remote DBs to read from but not write/change. I would also like to filter the information that they would ne able to read from the central DB)

 

[Galaxiom]

Opening a recordset is quite discrete because it is done entirely in VBA which can be hidden reasonably securely in an Access front end.

Proper security really requires the table being moved to a database server. This allows tight control of connection and editing rights. You could use Microsoft SQL Server or MySQL. MySQL and the Express version of MSSQL Server are free.

 

[jonathanchye]

Thanks for the answer. The whole project is still in its infancy but I hope to design the DB in a way that allows easy integration into SQL Server 2008 in the future. My plan is first to create the individual DBs as Phase 1, Split FE/BE as Phase 2 and final phase would be moving DB into SQL Server 2008 in the future.

I believe if I keep doing things I am doing now the extra security features could be easily implemented when moving into SQL Server 08 without major need of system redesign or compromising data? I just don't want to implement a feature now that won't be supported in future upgrades.

 

[Galaxiom]

My plan is first to create the individual DBs as Phase 1, Split FE/BE as Phase 2 and final phase would be moving DB into SQL Server 2008 in the future.

... I just don't want to implement a feature now that won't be supported in future upgrades.

Splitting should not be delayed. Multiusers in unsplit databases invites corruption. Having no code makes backends much more robust than front ends so it is a good idea to split a database even with a single user.

This way the front end can easily be developed and substitutes over the same data. Unsplit databases are impractical to develop without losing data because the data is integrated with the front end.

Also there are methods that only work in local tables and not in linked tables.

 

[jonathanchye]

yes but I thought splitting should be done ONLY when all the tables are finalised? I am still in developement stage so I can't say for sure if the tables are already finalised for the DB I am developing.

 

[Galaxiom]

yes but I thought splitting should be done ONLY when all the tables are finalised? I am still in developement stage so I can't say for sure if the tables are already finalised for the DB I am developing.

It is convenient to work with the single database until the basic structure has been developed but I think "finalised" is too strong a word.

If a table in the back end is modified just relink it to the front end. It isn't a major deal.

 

[jonathanchye]

Ah, I haven't worked with split databases as yet but are you saying it is save to start splitting once I get the basic structure completed?

At the moment, my db (DB1) also has an external table link with another database. What will happen when I split DB1?

 

[Galaxiom]

Splitting really just makes two copies of the file. Then the tables are deleted from the one that becomes the front end and the objects other than tables are deleted from the back end.

Like many developers I split by doing this process manually then linking the tables to the front end. So I don't know what the Wizard decides to do with existing linked tables.

Some tables such as developer controlled row sources can be better in the front end and doing the split manually allows complete control.

 

[jonathanchye]

Thank you for your excellent reply