Should I use multiple instances of MS Access
I hope to build a Microsoft PowerApp that will make data from a local access database available to field staff. So far I've managed to get the data to transfer the small subset of data needed from the MS Access database to a SQL Azure table on SharePoint. From there I can develop the app needed to make the info available to field staff.
I am now in the process of writing a function that will synchronise the data from the Access database to the SQL table. My table is currently about 1/3 of its ultimate size containing 195 records and each record has 22 variables. I have written a VBA module that synchronises existing records, then adds new records and finally deletes old records but unfortunately, because of the slow speed of the link to SharePoint, the process takes about 82 seconds to complete. I was hoping to run this in a timer event of one of the forms in the Access front end but since MS Access won't run multiple threads, I don't think my users will be happy about such a long lag time before they can continue using the system.
The only other way I can think of is to have a second instance of MS Access running on one of the machines to run the sync every few minutes but I fear that will chew up excess memory on the machine and slow it down as well. Therefore I am after suggestions on how to manage this.
I am now in the process of writing a function that will synchronise the data from the Access database to the SQL table. My table is currently about 1/3 of its ultimate size containing 195 records and each record has 22 variables. I have written a VBA module that synchronises existing records, then adds new records and finally deletes old records but unfortunately, because of the slow speed of the link to SharePoint, the process takes about 82 seconds to complete. I was hoping to run this in a timer event of one of the forms in the Access front end but since MS Access won't run multiple threads, I don't think my users will be happy about such a long lag time before they can continue using the system.
The only other way I can think of is to have a second instance of MS Access running on one of the machines to run the sync every few minutes but I fear that will chew up excess memory on the machine and slow it down as well. Therefore I am after suggestions on how to manage this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much for all your support and comments. Especially the detailed explanation on how to sync my files.
In answer to questions raised, most of the data only needs to travel one way - to the field. Any information flowing back will be in separate fields so that the office staff can monitor/approve it before it gets posted back to the main database, making it easy to identify. I may even look at a using a Data Flow option to make that happen.
I have only ever once worked with an Access FE linked to SQL BE but that was just a SQL Server Express backend and it was about 10 years ago. It seemed to work easy enough but I'm not sure if SQL Server Express is still available and if it will work with SQL Server replication, or if I could build a Power App that will link directly to it.
Anyway, thank you very much for the detailed explanations making my decision easier.
In answer to questions raised, most of the data only needs to travel one way - to the field. Any information flowing back will be in separate fields so that the office staff can monitor/approve it before it gets posted back to the main database, making it easy to identify. I may even look at a using a Data Flow option to make that happen.
I have only ever once worked with an Access FE linked to SQL BE but that was just a SQL Server Express backend and it was about 10 years ago. It seemed to work easy enough but I'm not sure if SQL Server Express is still available and if it will work with SQL Server replication, or if I could build a Power App that will link directly to it.
Anyway, thank you very much for the detailed explanations making my decision easier.
I don't believe that the Express version, which is still available and free, supports replication. You will probably need the full version of SQL Server for that. Or MySQL might support replication if you need a free option.
The Express version does support replication, but as a subscriber only.
/gustav
/gustav
ASKER
Do you know if I can link a PowerApp directly to an Express version on my own server without having to replicate back to the cloud?
No. Not heard of that option.
/gustav
/gustav
ASKER
I am not sure what Truncate is/does so I am not sure how to apply your suggestion in that regard.
The system that resides in MS Access has multiple tables, the biggest containing about 13000 records providing years of history and is growing at about 5000 records each year. All I need to provide the field staff with is a small subset of the data (jobs completed in the past 4 weeks and future jobs) so they can look up details (customer, address, phone number, job details and the like, coming from various linked tables) and enter information in a few fields like Job started, Job completed, Completed by, and Comments. At present all this happens from paper Job Cards or a phone call to/from the office. I want to make it easier but keep it simple.
If I were to put the entire database on the cloud it would be far too slow for office staff. So the plan is to create a simple PA that will give them access to up to date data using a single, flat table. As your comments note, every time I run a sync it brings down the entire table to link to the Access tables and it seems like that is a major part of the problem so I am wondering if I should keep a mirror copy of the SQL table on the local server and check for required changes against that. When a change is identified I can just change/add/delete that record on the SQL database. Once a day (after hours) I could run a total sync to ensure integrity.
Does that sound like a better option? If so, does MS Access download the entire SQL table if I use a query to update/delete/add just one record?