Automate Make Table Query

[fnwtech]

I have an access database (2000 format) that I added a make table query to the login to limit user access to their particular school. When this query runs, users see a warning that the current data in the table will be overwritten and do they want to continue. I have two questions.

First, I have very limited vb skills, but is there a way to automatically accept Yes so users won't see the warning?

Second, this is a shared database, will this effect users currently logged in?

Thanks!

[fnwtech]

Oh, and yes I have unchecked Confirm Action Queries...

[ADezii]

I have an access database (2000 format) that I added a make table query to the login to limit user access to their particular school. When this query runs, users see a warning that the current data in the table will be overwritten and do they want to continue. I have two questions.

First, I have very limited vb skills, but is there a way to automatically accept Yes so users won't see the warning?

Second, this is a shared database, will this effect users currently logged in?

Thanks!

First, I have very limited vb skills, but is there a way to automatically accept Yes so users won't see the warning?

Yes there is:

DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryMakeTableQueryName", acViewNormal, acEdit
DoCmd.SetWarnings True

Second, this is a shared database, will this effect users currently logged in?

If your Users are accessing the data in this Table either directly or indirectly when the Make Table Query executes, and the current data is being overwritten, I do believe you will experiencing a score of unwelcome problems!

[fnwtech]

You were right ADezii, this will not work. I tested this out and because I have record locking set and it locks everyone out until the first person logs out. Guess I need to do more research!

Thanks

[Curben]

I have an access database (2000 format) that I added a make table query to the login to limit user access to their particular school. When this query runs, users see a warning that the current data in the table will be overwritten and do they want to continue. I have two questions.

First, I have very limited vb skills, but is there a way to automatically accept Yes so users won't see the warning?

Second, this is a shared database, will this effect users currently logged in?

Thanks!

I would like to question WHY?
This seems related to an item i dealt with and may have a different path for you to take should your reasoning be similiar enough

[ADezii]

You were right ADezii, this will not work. I tested this out and because I have record locking set and it locks everyone out until the first person logs out. Guess I need to do more research!

Thanks

No problem. Take a few minutes and look at these 2 Tips of the Week. They may point you in the right direction.
Passive Shutdown
How to Generate a User List

[fnwtech]

I originally created this database for use by one school to track local assessment scores. This year four schools will be using it. I was hoping to use the make table query to allow access to the subset of students within a particular school. This database is set to record lock as several people need to add grades for each student. So, with the make table query, the first person who logs in locks the table to other users. Basically, only one person could be logged in at a time.

For this year I have finally decided that I will need to use four copies of the database while I learn more vb!

[Curben]

I originally created this database for use by one school to track local assessment scores. This year four schools will be using it. I was hoping to use the make table query to allow access to the subset of students within a particular school. This database is set to record lock as several people need to add grades for each student. So, with the make table query, the first person who logs in locks the table to other users. Basically, only one person could be logged in at a time.

For this year I have finally decided that I will need to use four copies of the database while I learn more vb!

ok well here is an idea in the mean time if you can use Record-level Locking instead.
I would assume that somewhere in the tables a field contains the specific school. Base queries on that as either a filter or criteria and lock the forms to only be accessable by users of that school.

using the switchboard you can have a main user interface where you select your school, and then have a selection of forms.

Combining the two, users would only see the forms for their location and if the choose the wrong location would be denied access as well. With simple VBA you can change the error message to specifically say "You are not authorised for this form at this school, Please Verify that you have the correct school. If you feel you received this message in error, please contact your administrator."

[fnwtech]

so basically duplicate all the queries, forms for each school?

[Curben]

so basically duplicate all the queries, forms for each school?

Aye
On one hand its messy, on the other, It works for now. There might be a better way to filter the form based on user ID using a complicated VBA that i do not know the method, nor how easy it really is.