Linking to certain fields of a table

[bmoore]

Greetings,

This seems like it should be a simple problem, but I'm new to Access and my digging hasn't yielded a solution. I would like to create a table in a non-protected database that contains only certain (non-sensitive) fields selected from a table in a password-protected database. I need the fields in the new table to update automatically when changes are made to the original table. I need the unsecured table so that I can query it from MATLAB without having to hard-code the password to the secure database.

I've tried linking to the table from the non-secure database, but could not delete the sensitive fields, and hiding them does not provide the level of security I need.

I don't think a relationship between the two is appropriate, as the second table will be only a subset of the first. I also tried using a sub-datasheet, unsuccessfully.

At present I don't have administrative rights to apply user-level security, though so far this looks like it may be the only long-term solution. Is there something I'm missing? Thanks so much! (Access 2003)

[FishVal]

Greetings,

This seems like it should be a simple problem, but I'm new to Access and my digging hasn't yielded a solution. I would like to create a table in a non-protected database that contains only certain (non-sensitive) fields selected from a table in a password-protected database. I need the fields in the new table to update automatically when changes are made to the original table. I need the unsecured table so that I can query it from MATLAB without having to hard-code the password to the secure database.

I've tried linking to the table from the non-secure database, but could not delete the sensitive fields, and hiding them does not provide the level of security I need.

I don't think a relationship between the two is appropriate, as the second table will be only a subset of the first. I also tried using a sub-datasheet, unsuccessfully.

At present I don't have administrative rights to apply user-level security, though so far this looks like it may be the only long-term solution. Is there something I'm missing? Thanks so much! (Access 2003)

Hi.

Actually you don't need to link external table to query it. You can query external table using SELECT...FROM.. .IN... syntax.
Example for password protected external db query

SELECT * 
FROM tblTable IN 'Z:\db1.mdb'[MS Access;PWD=Password;DATABASE=Z:\db1.mdb];
 

Good luck.

[bmoore]

Thanks for the reply, FishVal,

I did follow your advice and get the query to run, which was more than I knew how to do before. My concern with this is the same as with running the query from MATLAB, however. The password to the protected database is exposed in the un-protected database to anyone who looks at the SQL-view, so it's not an acceptable security situation. When I link to the table, on the other hand, it seems that the password is not stored in a way that can be viewed by other users. This is what I'm after. Any ideas?

[FishVal]

Thanks for the reply, FishVal,

I did follow your advice and get the query to run, which was more than I knew how to do before. My concern with this is the same as with running the query from MATLAB, however. The password to the protected database is exposed in the un-protected database to anyone who looks at the SQL-view, so it's not an acceptable security situation. When I link to the table, on the other hand, it seems that the password is not stored in a way that can be viewed by other users. This is what I'm after. Any ideas?

Sorry Bmoore, so far it seems to me that user-level security in intermediate db is the only way.
BTW password could be more or less easily retrieved from linked table.

? CurrentDB.TableDefs("LinkedTableName").Connect 

in VBA immediate window.

[bmoore]

Sorry Bmoore, so far it seems to me that user-level security in intermediate db is the only way.
BTW password could be more or less easily retrieved from linked table.

? CurrentDB.TableDefs("LinkedTableName").Connect 

in VBA immediate window.

Thanks - that's what I needed to know. If linking isn't necessarily a secure solution, either, then it looks like user-level security will be the best way for us to go, even if it takes some work to get there. I appreciate your help!

[FishVal]

Thanks - that's what I needed to know. If linking isn't necessarily a secure solution, either, then it looks like user-level security will be the best way for us to go, even if it takes some work to get there. I appreciate your help!

Thanks.

I've actually meant this

At present I don't have administrative rights to apply user-level security, though so far this looks like it may be the only long-term solution. Is there something I'm missing? Thanks so much! (Access 2003)

You can apply user-level security on db containing one above mentioned query not dealing with the main db. The only disadvantage is that performance may be lower.

[bmoore]

Okay, that sounds like a good solution - and faster to implement. I can work on getting the necessary permissions, but setting up security on the intermediate db will be a good start. Thanks...