A major question here is, how many people will have access to the Google Sheets AND will they have rights to update them throught the Google interface? I am not familiar with the inner workings of Google Sheets but I have some (unpleasant) experience with using Excel workbooks for a similar purpose. I inherited that particular DB; I would never have designed it that way.
The problem is that when Excel workbooks are opened via Access AND directly opened through Excel at the same time, they don't synchronize so well. Access has mechanisms built-in to share its BE file, but when that file is based on Excel, "destructive interference" becomes an issue. The colloquial definition of that kind of interference is "the left hand doesn't know what the right hand is doing."
Also, you are looking at synchronization issues, and there is where you have a HUGE problem. Were you planning to allow users direct access to the sheets or did you have a form set up with some underlying code? Because if your users CAN get directly to the sheets, it will be that much harder for you to determine the relative ages of the row in the sheet vs. the record in your Access "shadow" table. Your shadow table can see what is in it and what is in the spreadsheet, but the ODBC link is not, I think, bidirectional; i.e. whatever is in the Google sheet cannot know if the shadow table has a newer entry pending. You mentioned this in your bullet points. Having multi-source backends equals a tailor-made "destructive interference" scenario.
You are NOT being ridiculous to ask the question. That is called "problem analysis." It is how you avoid mistakes.
This kind of problem is usually solved by making one and ONLY one data repository, which is then automatically the "authoritative" source. If you have more than one data source, you will be continually faced with trying to decide which source is authoritative. I read the article regarding the CData ODBC driver and can see that it allows updates to the Google Sheets items. But it is the reverse direction that is problematic.
I don't know your requirements but I have to think that this is not a good idea.
Hello The_Doc_Man,
Thankyou very much for your thoughtful reply.
Let me give a bit more background into my situation: My (still small but growing) company has reached the limits of what we can manage with Excel so we need to move to a database system like Access. At the same time we need a way for the employees to input their work hours into the system in such a way that we keep what i guess it referred to as Referential Integrity in Access, i.e. only be able to input work hours to a client with the correct spelling and to a job number that has been previously assigned to that client.
The main function of the database is to be able to quickly filter through (using queries and forms) the clients, job numbers, to determine how many hours have been used on each job by the various employees, and which ones of those hours have been invoiced or not. I will also do reports of various types to make the administration life easier like automatically generating a list of clients with un-invoiced work hours, of the weekly list of how many hours have been worked by each employee in a certain time frame.
Google sheets works really well for me because i can easily share it as needed and access it on any computer, and the Google Sheets seamless bi-directional update when various users are using the same sheet is great (almost immediate, every time, in any direction).
Also it allows me to use an online service such as AppSheet to generate phone apps that would be a great way for the employees to interface with the database (the employees are electricians, working away from the office). This allows me to select who has access to which data.
Google sheets can also be used to do some functions that that are readily done in Access like combobox type entry fields, queries and even makeshift forms (if scripts are used). This works well for me.
I see google sheets as being suitable for managing by list of clients, list of job numbers, list of employees, and list recently performed work hours.
However, i do not see Google Sheets being a suitable 'elegant' way to manage the much bigger data-set of the entirety of the work hours and work materials for every client over the years, which is where i want to use Access.
The CData ODBC Driver works quite well in my experience, but as you mentioned, it works particularly in the Access->GoogleSheets direction. When a field is updated in Access on a linked sheet the result appears on the corresponding GoogleSheet almost immediately without the need for any 'Refresh' action. The link works just as well in the other direction (GoogleSheets->Access), but it requires a 'Refresh' command to be done for the data to update (i will work out a way to periodically automatically refresh). Also it is a bit frustrating if (after linking) i add or change column names in GoogleSheets as the change does not appear in Access without deleting the cable and re-linking it (which is not that big a deal, just frustrating, i have been told by CData they will update the driver to make this better)
The main limitation is that i cannot use the ODBC-linked table as i would a normal Access table because a lot of the basic functions do not work, like links with referential integrity, and even queries with links to the ODBC-linked tables do not work. (from my experience)
I agree with your comments that i have to be careful about data clashes and 'destructive interference'.
So at the moment my plan is the following:
GoogleSheets tables
- tblClients
- tblJobs
- tblEmployees
- tblWorkHoursImportBuffer
- This table is so that the data that is inputted by the employees through the App go to a temporary table where the data can be checked by a project manager, and once it is all checked to be good I run a append query through a form in Access to put the data in the main WorkHours table (then a delete query to clear the temporary ImportBuffer)
These tables would be stored primarily in the Google environment, as they are what i need to do my App integrations.
Any and ALL modifications to these tables would be done ONLY through the Google environment.
Access Tables
- tblWorkHours
- tblWorkMaterials (this is managed completely independently form the work hours so i do not need any links with google to import data)
These would be the only two tables that primarily store the data in Access.
But there would also be a local copy of the GoogleSheets tables (listed above) which would be a duplicate of the OBDC synchronized ones with the sync only working in the GoogleSheets->Access direction.
That way i would have all my required tables to do what i need in Access (including the ability to do all links and access functions as i require). The only limitation is that Access would effectively not be able to modify the 'Clients','Jobs','Employees','WorkHoursImportBuffer' as it would be constantly over-written by whatever is on GoogleSheets, but that is OK as i have no need for the database program to modify that data, just read it.
So even though the primary data storage locations is split between Access and GoogleSheets, there is a clear demarcation between tables and which ones are modified from which side.
Is there anything above that seems to be a bad idea?, or that would lead to imminent problems?
Once again thankyou very much for any advice!
(Sorry for the lengthy response, but i admit that putting all this in writing has helped make things clear in my mind)