"I am new to this stuff so explain as much as you can.I am working on a project in which I have 40 access databases that are identical in and structure and purpose; each one is used by a different location. These databases all have to be combined in to one SQl database backend. So far I have been able to get the sql server table structure over, and I added a location ID to each table so that I can keep all of the data seperate.All at the same time I will need to keep the access front end, which will inturn read a file on the persons pc and allow them to only see data from their location. 1) how do I filter out the data on the access front end by location; so that I can add update and delete and create new records? 2) How do I create a file to be read on each persons pc so that access know how to filter that information? I have already tried inserting a select statement in the recordsource property through vb. Thats kinda of working But I need to know of some concrete ways of designing good row level security.

hey man, this will depend on how your database front ends work, for example are the forms displaying the data through data binding directly from a table or is the data displayed through setting properties of controls on a form using select statements.

What I'd recommend is essentially replacing the tables in the database's with queries, the queries contain all the columns of the tables. In the queries you then apply the filter by location. The queries only show the data for each location but still have the structure of the original tables. If you do this then there will be very little to change in the front ends.

There's a number of ways you can do this... the absolute best way to do it would be to create stored procedures on the server that returned result sets matching the original tables. Each stored procedure would require an location parameter. You'd call that procedure using ado recordsets and then, using them, set the forms recordset. As to storing the location to file well that again depends on the system's set up. You could just store on file in the directory of the database front end rather than on every users pc.

This depends on how your system is set up.