Update Query

[shart00]

There are 2 databases (DB1 & DB2)
Since there are security issues only a few people have access to DB1. However multiple people some of the info, therefore "append" and "update" queries were made in DB1 to update "non critical data" tables in DB2. A form in DB1 automatically opens when the DB is open and on a timer of 120000 milliseconds (every 20mins, atleast that is what I think it is) runs the queries to update DB2.

The question...

What would happen if someone was in DB2 running reports/queries when DB1 tried to run its update and append queries?

Would there be any significant errors and, if so, is there a way to prevent this?



Having everyone open DB1 is not a possiblity due to security and limitations on the SQL server.

 

[johnherman]

What is the brand name of DB1 and DB2? Are they both SQL Server?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[SQLSister]

In SQL Server, different users should be able to update the same tables without a problem. SQL Server will lock the record while the update occurs and the next update will wait for the lock to be released. There could be timeout issues if one is running a major update that locks all the rows of a table for a long period of time. But if you are running this sort of process, generally you don;t want users also trying to update the records at the same time anyway. If the updates concern differnt rows or tables, likely there won't even be a noticale delay. I would however make sure all my inserts/updates and deltes were wrapped in transaction processing with some type of error notification if an error occurs. This is particularly important if the users are updating related tables at the time as the original tables. That way if one part of the transaction fails, the whole thing is rolled back.

I'm not sure why on earth you need a form to run the update queries every 20 minutes. This can be done easily in SQL server by using a job and then no one has to open the database for the updates to happen. Further, you can have near realtime updates by using a trigger on a the DB1 tables to automatically send data to the other database. By scheduling every 20 minutes, you may create the potential for data inaccuracies.

Let me explain what I mean. Suppose user 1 makes a change at 9:30 to record 100 in db1. The next scheduled update is 15 minutes later at 9:45. At 9:40, another user makes a differnt change to the same record in db2. Depending on how the update happens in the scheduled query (i.e. if you update all fields rather than figureo ut which have changed), then the first update will overwrite the second update. If you have a trigger do the update, the first update is complete or running before the second update occurs even if they occur very close in time. There fore the information from both updates is available. And if they updated the same field you would want only the second update to take effect, which would happen with the trigger, but not with the scheduled query.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[shart00]

Here is what is going on. DB1 is in Access97 linked to the SQL server. (Conversion to 2003 is not economically possible at this time - this DB was created with outsourcing and they want too much to convert to 2003.)

IT says there are server issues if we tie more people into the SQL server so we need a way to let more people SEE the data.
Therefore.....
The queries in DB1 run to make tables in DB2 so that all can have access in a 2003 database that is maintained inhouse. The users of DB2 DO NOT UPDATE, only run queries and reports for downloads.

Problem.....
IT has disabled the Task Scheduler so we can not use that to open DB1- run queries and close automatically.
We are also unable to download any "assistance" programs that would do the same.
I guess IT likes its control...

Therefore... Solution....
Someone with SQL access open the DB and keep it open all day
Create a hidden form and on timer have it run the queries to update the data in DB2

Possible Problem.....
If someone is in DB2 running a query when the 20min comes up and DB1 tries to run the update queries?

Thank you for any help.

 

[SQLSister]

First things first. In no way would I put up with IT denying access to people who need to read the data. I would go to the CEO of the company if I had to. All they need is one application login which has read only rights to the database. Providing this is NOT a big deal and the fact that they won't is proof of their incompetence. They need to be smacked down hard for this by a senior manager.

Do you have direct access to the SQL Server or only through the Access interface? SQL Server has a way to schedule jobs.

AS far as too different users doing differnt things to the final Access database. What will probably happen is that the users will get their information and it will remain on the screen until they retreive new information or requery the data. The update will happen behind their backs and while it may slow down access, it should not stop select queries from running. If they have info on the screen that is affected by the update, they won't see it until they refresh the data. Since they can't change the data, this is not a big deal. Where you can run into problems is when two users try to change the data and one is working off a disconected recordset whic no longer matches the data in the tables.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[shart00]

I agree, however it has taken me months to get approval to create DB2. Use to be that DB1 would run the query once a day and download the data into an excel file then the other users would copy the file and use excel to create the reports. I was able to prove that this was taking time away from their other duties and by using Access the file would not have to be copied and when a new report is needed this would ensure conformity among all areas (instead of each area creating their own in their individual excel files).
Then if any changes were made after the morning download the information was not available until the next morning.

As for access to the server, I don't have access. Even though I have been given this task.

In short, let me see if I understand...
If someone is in DB2 and running queries and reports at the same time DB1 is running its queries no errors will occur?
It is just that the user of DB2 will need to refresh their query to see the changes?
The problem would be if someone is looking at a record that has been changed by the update they will receive an error? But wouldn't this happen only if they are attempting to change the record? If so, that is not actually a problem since DB2 is only for READING not changing the data.
There is one thing,
The link to the SQL Server is via ODBC through access. Since I do not have access to the server when I try to open DB1, I receive ODBC errors. However the user that we are planning for to open and keep DB1 open all day does not receive such errors, therefore they should have access to the server, correct? If so, is is possible they may be able to find the scheduler on the SQL Server?

I may be making this harder than necessary but I would like to CMA it questions are asked.

I must thank you again for your help!