Question Is there an ODBC driver for Google Spreadsheets?

[MarkRennes]

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?

 

[MarkRennes]

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.

 

[marlan]

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

 

[david howard]

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

Private Sub Commandxx_Click()
Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "C:\Users\base\Google Drive\blahblah\mystuff.xlsx"
appexcel.activeworkbook.refreshall
Set appexcel = Nothing
End Sub​

(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

 

[spikepl]

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

 

[rssbus]

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