Question Prevent linking to / importing from a database without setting a database password?

[BadSikander]

Hello.

I have an unsplit Access 2003 database. I don't want people to be able to link to its tables or import them into other databases, Excel, etc.

I know I could "lock" it with a database password, but I don't want users to have the inconvenience of typing this in whenever they open the database directly. And I wouldn't really want them to know the password in any case.

Is there any way to either:

(i) Prevent linking / importing without setting a Database password

Or

(ii) Set a database password, but have it input automatically when users open the database itself?

 

[DJkarl]

If I were you I would split the front-end out and password protect the back-end . If you lock down the front-end database correctly the users can open it and use it, but won't have access to the back-end tables.

 

[BadSikander]

If I were you I would split the front-end out and password protect the back-end . If you lock down the front-end database correctly the users can open it and use it, but won't have access to the back-end tables.

Thanks for the reply. Unfortunately, this database is going out to numerous sites on different networks. So to have it FE / BE would be a last resort only. I would still have to hand over the password so users could link FE & BE correctly. Either that or go out to every site individually to set it up for them and that wouldn't be practical.

 

[DCrake]

Are more than 1 user be using the app at the same time at each of the sites? If so, and it is not split, then be prepared for corruption.

 

[BadSikander]

Are more than 1 user be using the app at the same time at each of the sites? If so, and it is not split, then be prepared for corruption.

Hi. At this stage the db is intended to be a read only resource that individuals save on their desktops. They are being advised not to save to shared / network drives.

 

[DCrake]

Then the easiest way to help you is to first set the properties for all the tables/queries to hidden. Then convert to MDE as deploy that version.

 

[BadSikander]

Then the easiest way to help you is to first set the properties for all the tables/queries to hidden. Then convert to MDE as deploy that version.

That certainly prevents importing / linking from Access, but Excel still seems to be able to see and retrieve the tables.

 

[DCrake]

You are correct, I have never tried it from Excel, big problem then.

 

[BadSikander]

OK, reluctantly will have to split DB and password protect BE.

But users are on remote sites with their own network / drive mapping etc, so they will have to link the FE to the BE themselves and I still don't want them knowing the password when they do that.

There seems to be no way to include the password automatically with the Transferdatabase process, but I found and adapted this from an old thread elsewhere:

Set dbs = CurrentDb
Set tblDef = dbs.CreateTableDef("Table1")
tblDef.Connect = "MS Access;DATABASE=" & "MyDatabase" & ";" & "PWD=PASSWORD;"
tblDef.SourceTableName = "Table1"
dbs.TableDefs.Append tblDef
Set tblDef = Nothing
Set dbs = Nothing

The actual linking seems to work fine, but I don't really understand what it does differently to Transferdatabase (other than including the password!)

Are there any disadvantages to linking tables via TableDefs.Append rather than the usual Transferdatabase?

 

[BadSikander]

You are correct, I have never tried it from Excel, big problem then.

Is it not standard practise to keep your queries in the FE of a split database? Presumably if only a system password can prevent Excel importing information from Access, then you need to always apply a system password to your FE? Otherwise queries can be imported to Excel from the FE, and bring with them information from your tables in the password-protected BE.

And... even if you password protect the FE, that doesn't stop the user themselves being able to import via Excel (as obviously they will know the password). So if you have fields / rows you normally keep hidden from the user in the FE, you have no choice but to keep all queries in the BE with a separate system password. Surely that can't be correct?