Using Access with ODBC Linked table (from Google Sheets) - duplicate/synchronize tables? (1 Viewer)

[dlambert]

Hello,
I am building a database system form my company and i intend to use the CData ODBC driver for Google Sheets which allows the (almost) seamless link between a table in access and a google sheets document.

Link for info: https://www.cdata.com/kb/tech/gsheets-odbc-access-linked-tables.rst

The objective of the link with Google Sheets is so that i can have the full power of Access in the office, but certain aspects that i would want constant access by the employees can be done through the Google environment, with use of Google Apps etc.

The link between the online and local tables works nicely, but a limitation i have found is that Access does not accept to have relationships/links between the ODBC tables and the local tables. This of course has an impact on my database functionality.

• So i was thinking that a solution would be to have a local table that is duplicate of the ODBC table:
  • The local table would be used by the database locally with all relationships and data reads being done on the local tables
  • The ODBC tables would be only as a buffer between the Google Sheets and the local tables.
  • Ideally this duplicated table would be able to synchronize both ways so when new data is added on the ODBC it would update the local table, also the other way around
    • Alternatively the link could be just one way (from ODBC to local table)
      • This is not ideal for me but i could work around it.
• The tables that would be duplicated would not be particularly big so i am not worried about the extra storage required for the duplicated table.

So my questions are:

• Firstly: i am being ridiculous and there is a much better way to do this that i am completely missing?
• Any suggestions on how to do the table duplication and possibly two way synchronization?

Any advice is much appreciated.

 

[The_Doc_Man]

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.

 

[dlambert]

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)

 

[Pat Hartman]

Access does not accept to have relationships/links between the ODBC tables and the local tables

This is not an Access restriction. It is simply the way relational databases work. You can't define relationships between tables in two separate Access databases either or between two SQL Server databases, etc. ALL tables involved in relationships MUST exist in the same database or the database engine cannot properly enforce referential integrity.

As Doc suggested, keeping your data in a spreadsheet is a poor choice. The Access database should be the master. If your users do not work on the same LAN, sharing an Access application can be difficult but it is possible if you use Remote Desktop or Citrix. If you can't use either of those two products, I would suggest having each user email their time as logged in a spreadsheet and your app can import the timesheet into the master tables.

 

[The_Doc_Man]

Don't worry about lengthy comments. The longer you are here, the more you will see that I occasionally get in the mood to write a small book. I often get wordy. It's probably a congenital defect or something like that.

The second part of Pat's answer was also going to be part of my answer.

If you arrange for everyone to fill out a timesheet, their individual timesheet can only be definitive for their hours, but for the company, the "roll-up" HAS to be in the central location. You yourself expressed some concern over this point: "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."

If you can get folks to enter their information via spreadsheets, you can have them e-mail them or drop-box them so that you can import them directly. In which case, you wouldn't even need Google sheets and the ODBC driver. THAT functionality could be easily handled by Excel at the individual level. We can always want the best of both worlds, but the truth is that when you mix together things from two different methods, that interface between the methods is where you spend the time (and by extension, money... since we all know that time = money.)

I'm not going to say you cannot do what you want to do. My only comment is more along the lines of a two-fold question: How much longer will this hybrid method take to implement than using something like Pat suggested like Citrix or Remote Desktop? AND part two, what will people use until you get this hybrid option up and running? They still have to be paid, right?

 

[Pat Hartman]

The nice thing about Citrix and Remote Desktop is that they don't necessarily change the Access app. If the Access app has to change, it would be do to the app's interaction with OTHER applications such as Excel, Outlook, and Word or perhaps shared SQL Server databases. I have many clients who use one of these two solutions to share an Access app with people who are not always on site. In once case, most people were in the headquarters in Farmington, CT (suburb of Hartford) running the app on the LAN but we had remote users using Citrix located from San Francisco to Paris. The Citrix users actually had slightly better performance than the local LAN users on some days because the SQL BE and Citrix were on the same server so there was no LAN latency to interfere with performance whereas on reporting days or warehouse building days, the local LAN could be sluggish.

Not every company has the technical team who can set up and manage Citrix or Remote Desktop. That leaves you with going to a third party to host Citrix. The problem that causes is that ALL users must be via Citrix since there would be no connection at all with your LAN and if you need to use Excel, Word, and Outlook and share files, those would also need to be on the Citrix server or at least at the third-party site so they are on the same LAN.

 

[Isaac]

This thread caught my attention because of the mention of AppSheet. I have used AppSheet very happily for several simple apps and it is awesome. But remember that AppSheet supports sql server (and variety of other things, not just Google Sheets) as a back end - you might have to pay a few more bucks / mo licensing fees but well worth it. This way you could just have "one" back end..

 

[dlambert]

Thankyou for all your thought-full replies, much appreciated. Once i have my system going (whichever way that ends up being) i will post it here, if it can be of any interest to anyone..

 

[Isaac]

Certainly .. thanks!