Update .accde files with multiple users

[konaan1]

I have a .accde file that I placed on my company website for supervisors to use to view tons of data. I need to update this database every morning from 0700-0730. I'm running into a problem, if there are any users in the database it will not allow me to update the file. This is a big problem because then I have to try and find out who has it open and ask them to close it before I can save the updates.

Can anyone please help with this??

Also is there anyway to set a timer of sorts to say

Lock the database at 0700 and unlock at 0730?

I have found some stuff on TWait = Time. Just need to know what I can do to make this database run smooth. Thank you for any help!

 

[spikepl]

What does "need to update" mean exactly? Coding? Forms? Or just data?

 

[spikepl]

Ooops - this post seems to be a continuation of this http://www.access-programmers.co.uk/forums/showthread.php?p=1118346#post1118346 or perhaps this http://www.access-programmers.co.uk/forums/showthread.php?p=1118362#post1118362 both without any response from OP.

 

[konaan1]

Well the first link -- post1118346 has been fixed. I worked the bugs out finally.

As for the second link -- post1118362 -- saving the file as a .accde file allows as many users as I need so far.

To answer your question from earlier-- its a combination of both, data and form changes.

I have a procedure that runs each morning out of QMF. Once the procedure has finished I run a macro to delete the old data on the form and import the new data from today (via import specification).

I work off of 3 different databases:
1) Back-end
2) Front-end (for design mode)
3) Copy of Front-end saved as .accde (copy that everyone sees)

 

[spikepl]

It is good form to close of you question by providing feedback to your helpers -also if you resloved the quesiton yourself.


What is QMF?
And data "on the form"? Data is normally held in tables-
So you are changing your design each day ? That sounds utterly strange to me - or have I misunderstood something?
If you just change data, then I don't quite see why a frontend must be closed down. Can you explain what exactly you change each day?

 

[konaan1]

QMF is a Quality Management Facility or System. It is an SQL program that is linked to our system at work. Provides data directly from our system.

Here is a breakdown of what I am trying to do:

I have a Database called the "Supervisor Dashboard" is has approximately 20 Tables, 20 Queries, 20 Forms, and 1 macro.

-The 20 tables are .CSV files, Reports that my office used to run each morning and took hours to complete competeing for server time and manipulation.

-The 20 Queries are to Delete yesterdays data (which is completed production for the prior day, we have new data every day). There is one Delete Query for each table on the database.

-The 20 forms are pop up datasheets that can be viewed or filtered. Each form has an Export to Excel button for the supervisors to use as they desire.

The 1 macro is used to run the query (delete old data) then import the new data (Import Specification).

Every morning I come in, Run the procedure in QMF. It kicks off the 20 reports in order and saves them to my personal folder as a .csv once all the reports have been ran, I click the button for run macro and it updates each button for the 20 different reports on my database with the new data.

This saves my office tons of money and time because now only 1 person is on the server at once, its speeds our mainframe system up, and we no longer inundate our Outlook inboxes with tons of emails.

I need to be able to click the run macro button and the update the buttons even if someone is in the "old" copy of the database.

Is this possible?

 

[DJkarl]

QMF is a Quality Management Facility or System. It is an SQL program that is linked to our system at work. Provides data directly from our system.

Here is a breakdown of what I am trying to do:

I have a Database called the "Supervisor Dashboard" is has approximately 20 Tables, 20 Queries, 20 Forms, and 1 macro.

-The 20 tables are .CSV files, Reports that my office used to run each morning and took hours to complete competeing for server time and manipulation.

-The 20 Queries are to Delete yesterdays data (which is completed production for the prior day, we have new data every day). There is one Delete Query for each table on the database.

-The 20 forms are pop up datasheets that can be viewed or filtered. Each form has an Export to Excel button for the supervisors to use as they desire.

The 1 macro is used to run the query (delete old data) then import the new data (Import Specification).

Every morning I come in, Run the procedure in QMF. It kicks off the 20 reports in order and saves them to my personal folder as a .csv once all the reports have been ran, I click the button for run macro and it updates each button for the 20 different reports on my database with the new data.

This saves my office tons of money and time because now only 1 person is on the server at once, its speeds our mainframe system up, and we no longer inundate our Outlook inboxes with tons of emails.

I need to be able to click the run macro button and the update the buttons even if someone is in the "old" copy of the database.

Is this possible?

Hmm, your tables should be in a separate database from your forms and queries, they should be linked to the front-end, it sounds like the only thing you are really updating is the data in these tables.

That being said there are many opportunities for you to optimize and improve this process, unless the 20 files are completely unrelated data there may be a way to combine multiple files into one table and reduce the overall complexity of the design.

 

[konaan1]

Yeah I have a front-end and a back-end and yes all 20 files are unrelated. We are a large organization and have many operational areas.

 

[spikepl]

Restating DJkarl's question:

Where does the .accde come into all this? If you have an accdb linked to the backend, what prevents you from using it to update the backend data?

 

[konaan1]

3 different databases:

1) Back-end ((Supervisor Dashboard_be.accdb))

2) Front-end (for design mode) ((Supervisor Dashboard.accdb))-- For whatever reason, I cannot have multiple users in this file at the same time. I then save this file as: **see below**

3) Copy of Front-end saved as .accde (copy that everyone sees)-- ((Supervisor Dashboard.accde)) -- This copy allows 20 + users to access the file at the same time.

I've expained the just of what my database does and I still am not able to make updates to the .accde or .accdb is users are viewing the file.

I guess I need to ask how can I lock this file out at a specific time so that I can complete the daily update or is there a way to override/ boot people off of the database to give me enough time to complete the update? I only need 30 minutes a day to complete the updates.

 

[spikepl]

I still don't quite get the issue:

1. does the .accde contain data? Unrelated to the backend or links to the backend? This would be the only condition explaining the so far mysterious term "updating the .accde"
2. Or is the .accde linked to the same data as your accdb frontend, but you cannot access your backend from your .accdb while .accde has acces to backend?
3. or is the issue that you don't want people to run the .acccde WHILE you are updating the backend, because of perceived data consistency issues?

 

[konaan1]

Yes, the .accde does contain data. It is a mirror image of the .accdb file. The only reason I use the .accde is because it is a executable file only and allows multi users.
The .accde and .accdb is linked to the backend. Your #3 is almost on point. I don't want people to be in the .accde file while the macro is running (the backend data updates with no problem). My macro uses the delete query and then imports the newest data from the backend (via import specification). I'm trying my best to explain my problem, I'm fairly new to Access and am learning new stuff everyday. I would post a screenshot of my dashboard but the forum will not allow me to until I have more post.

 

[spikepl]

I am still confused - what is data doing in your accde (or frontend accdb file) ? Data is supposed to be in the backend, and only program logic is in the front end. If that is not so in your case, than consider fixing that as the first thing.

My macro uses the delete query and then imports the newest data from the backend ????? (via import specification)

I think we need to clarify something -why is there data in your accdb/accde frontends? part of the reason for a front/back split is exactly to keep data in one place - the backend. What is the reason for you having data in the frontend?

The only reason I use the .accde is because it is a executable file only and allows multi users.

huh? An .accdb also allows multiple users.

If yours doesn't then some setting is messed up. But in any case, multiple users should preferably each use own copy, not share one. If it is for viewing purposes only, then you might be able to get away with it for a long time.

 

[konaan1]

I am apparently referring to information or links as data when indeed it is not. When I keep saying data I am actually meaning update the forms. My forms are basically mirror images of the data from the Tables. Every single day we work the data on each form will differ. So I run the macro to update the Form.

I agree there is a problem with the fact that I can't allow multiple users in the .accdb file. But for whatever reason it will not allow multiple users in the database. My database is for viewing purposes only. The data on the forms have record locks so that the data cannot be altered even if accidental. By making it a .accde file even if someone knows how to backdoor their way into the file they cannot make design changes at all (which I like).

 

[konaan1]

Once I have met my post limit I will try to attach screenshots.

 

[spikepl]

forms dont change - the are predefined objects in the db and change only when you change them on purpose in either code or design. Data in tables does change. Data is contained in a backend. A linked table is a phony table that leads to the real data, just like a short cut on your desktop -it does not itself contain data as such. Data is displayed in the forms. You do not "update" "forms" when you change data.

What is this "whatever" reason? Did you set exclusive access in the database options, frontend and/or backend? Then unset it.