Question Is there an ODBC driver for Google Spreadsheets? (1 Viewer)

I continue to tear my hair out that standalone Microsoft Access is not really web-accessible.

I've come to like and depend on Google Docs, now Drive; Since I work in a business school we use them via Google Apps for Education. All quite useful. We also teach our students basic Microsoft Access skills and even a bit of database analysis and design. But we can't easily make their databases web-accessible; I can't impose Office 365 on penniless students, nor does it serve dynamic public web pages.

There is no end user accessible database within the Google Apps world - the Google Cloud SQL offering is really only suitable for use by developers (and is expensive). People get round this by using well structured Google Spreadsheets as tables in the cloud. There is even a QUERY function which works a bit like an SQL SELECT statement - but only on one worksheet at a time. Ho hum.

So it occurs to me that there "ought" to be a way of making a well-structured Google Spreadsheet worksheet act as an ODBC data source, so that users can put data into a Google Spreadsheet - e.g. via the rather wonderful Google Forms facility, and that data can be visible in a Microsoft Access database.

Turn this scenario on its head, and there must be a significant need for bridging Microsoft Access into the Google ecosystem.

Is anyone aware of existing software that enables a Google Spreadsheet to be a server to Access? and / or for an Access database to be a server to Google Docs?

I don't thing there is even an ODBC driver for Access to Office365 Excel.

ODBC drivers are created by the developer of the data source so Google would need to create the GoogleDocs driver.

Point taken, Pat: normally it's the owner of the data source that needs to create an ODBC driver.

But since Google has not done so - and has other priorities! - might a third party take an interest?

Because the underlying problems are these:
1. Google Docs / Drive does not include a relational database
2. The only relational database which is in widespread use and which has a reasonably end-user interface is Microsoft Access - but Access is not for now a web database.

I'll go back to tearing my hair out. And, pragmatically, sharing my Access database using Office 365 with my close collaborators, while using Google Forms to get distant collaborators' data into Google Spreadsheets - whence I move the data into Access.

It depends on what you mean by "web database". Both Jet and ACE are available for use by websites as data stores. Access tecnnically isn't a database at all. Access is a front end development environment that can use any ODBC compliant data store.

Access, the development environmant, can link to MySQL, SQL Server, etc. databases via the internet by linking tables using their IP Addresses.

A2010 has some enhanced features that use Sharepoint tables and A2013 when it is released will be more capable and actually use Azure but still be tied to SharePoint. The web implementation for A2010 is different from the new functionality in A2013 so I wouldn't invest a lot of time in it since it is already old technology. Plus both are pretty weak since they don't include a programming language but instead rely on macros which are quite limited in functionality.

Hi,
Google spreadsheet can be exported to Excel via API, witch Access can link to.
can any one know give me a hint on how to use Google drive API in Access VBA?

TIA

There is no .com interface provided so you would need to write your own. You will need to create something with VB.Net that your app can talk to that will call the Google API. Look for "com wrapper". That might get some code you can start with.

Here is my solution.

My situation, I run a sporting comp
- I want potential players to register using google forms.
- Have the form saved in a googlesheet
- Have the information from that sheet available to Access
- Having manipulated information publish the draw etc to the web.
I have seen a solution where the info from the form is published, I have one where it is private.

Solution
(1)Set up Google drive so documents had a path in my local computer.
(2)Created the form in Google docs
(3)The form can be placed in my google site.
(4)The Gsheet with form responses is visible on my C drive, when I click on it my browser opens with
"https://docs.google.com/spreadsheets/dblah blah blah #gid=1112223333"
(5)Opened a new excel sheet clicked on [data] [from the web] and used the above https:
The result was a spreadsheet with a bit of guff around it but with the data I wanted
Note:the Gsheet isn't a public document so only I can do this

(6)Set up the excel spreadsheet as a linked table in my Access database
(7) I needed an "event" to refresh the spreadsheet. You might want it to refresh on open but I have a button with the following

(8)I had to filter a bit because some guff came with it but I just used the timestamp field, ie if it doesn't have a timestamp then it isn't something I am interested in.

(9)I haven't done the last bit yet but I'll have the draw come out as a html file saved to my googledrive and then embedded in my googlesite.
Hope this helps some people

For the benefit of the subsequent readers: http://www.access-programmers.co.uk/forums/showthread.php?t=260840

RSSBus offers an ODBC driver for Google Spreadsheets that you can use to connect with Microsoft Access: rssbus.com/odbc/google-spreadsheets/