Access to SQL server migration

I am in the process of migrating 40 access databases to SQL Server. I am migrating the tables to the backend. All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to stay seperated by each location. So I added a location field to each table in the database; so that each locations data will be seperated by location. How do I go about filtering out the the data so that each location can only update, delete, an view their own data? (I dont mind a long answer.)

Hi,

If the data is seperated into different tables based on regions, then you can control access to that table using group based priviledges and add the appropriate users to that group. However, it sounds like the data is interspersed to various tables and you're using a column to keep track of the location for each row. In this case, I'm not sure what the best way to control access to the data at the row level. I'll move this thread to the security forum -- Maybe someone there has an idea.

Il-Sung.

|||

Have a look at the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=679308&SiteID=1

Thanks
Laurentiu

Access to SQL Migration

At work we are migrating several Access databases to SQL Server 2005 standard edition. These Access databases have no user interface forms. They do however have a series of macros that execute make table queries.

I'm trying to determine what are options are for replacing the make table queries in the macros. My first thought was to do the work in a SQL Server stored prodecure. I am asking for advice on what our options are. Is a stored procedure the way to go? Any suggestions on how to do it?

Thanks.

Stored Procedures and Views are probably your best bet. Have you tried using the SQL Server Migration Assistant for Access?|||Thanks for your answer Euan.

Do you know if stored procedures can be scheduled to run? I believe SQL Server has some sort of job scheduler.

Thanks.|||Yes, you can schedule SQL Server stored procedures to run as a job. You can specify the owner of the job, the frequency and the task done by the job - in this case, run a stored procedure|||Thanks bass_player.|||One thing to note, there is no scheduler as such in Express, you need to use the windows scheduler and then have it call sqlcmd (command line script utility) to perform this