Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hello,

Is it possible to connect a Google Forms Survey directly to Microsoft Access database and pull the respondents' responses directly into a linked MS table? Currently, when respondents submit their responses to the Google Form, I generate a Google Sheet and I download the Google Sheet to an Excel Spreadsheet. Afterwards, I connect the Excel spreadsheet to the Microsoft Access table as a linked table.

Kind regards,

wirejp

User avatar
HansV
Administrator
Posts: 78039
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by HansV »

You should be able to export from Google Forms to a .csv file.
Access can open the .csv file.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hi Hans,

Thank you for your quick reply and also for the information. To clarify my question, I am asking if a Google Forms Survey data (i.e. URL website data) can be directly pulled into a linked MS Access table using VBA code?

Thanks,

wirejp

User avatar
HansV
Administrator
Posts: 78039
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by HansV »

Not as far as I know, but perhaps someone else will have a suggestion.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hi Hans,

Thank you for the information.

Kind regards,

wirejp

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hi Hans,

I created a MS Access module called "modExcelToLinkedDbTable" to check the linked Excel spreadsheet for any data to append and update the linked MS Access database table. In the form called "frmGoogleFormResponses", an "Update" command button was added and by clicking the OnClick event procedure will call the "modExcelToLinkedDbTable " module to check the linked Excel spreadsheet for any new data. However, I received the following Debug error message after clicking the Update command button: -

Code: Select all

Run-time error '1004':
Application-defined or object-defined error'
The the MS Access database and the linked Excel spreadsheet can be found in the attached OneDrive web link:-
https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YY ... ?e=o7aF2o

Thank you in advance for your assistance

User avatar
HansV
Administrator
Posts: 78039
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by HansV »

Yo don't initialize the variable i, so when you enter the loop, the line

Code: Select all

        Do Until xlWorksheet.Cells(i, 1).Value = ""
fails. Above this line, insert

Code: Select all

        i = 2
However, you should not run the code from frmGoogleFormResponses, since that form is bound to the Access table that you want to update.
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hi Hans,

Thank you for your help and for the information.

Kind regards,

wirejp

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hi Hans,

I made the changes which you had outlined above and I moved the "Update the Excel Table" command button (I changed the command button name from "Update" command button to "Update the Excel Table" command button) to the frmAppendToFormResponses Form. However, when I click on the Update the Excel Table command button, I received the following error message: -

Code: Select all

Run-time error '3027:
Cannot update. Database or object is read-only.
and the following code is highlighted

Code: Select all

accTable.AddNew
The MS Access database and the linked Excel spreadsheet file are found in the attached OneDrive weblink:-
https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YY ... ?e=vze7kw

Thank you in advance for your help.

User avatar
HansV
Administrator
Posts: 78039
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by HansV »

Ah - I didn't look closely at the code. You're trying to update tblFormResponses, but that is the linked Excel table itself. You cannot edit or add records in a linked Excel table.
Best wishes,
Hans

Gasman
StarLounger
Posts: 75
Joined: 22 Feb 2022, 09:04

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by Gasman »

Perhaps you can adapt code from one of these links?

https://www.google.com/search?q=access+ ... e&ie=UTF-8
Using Access 2007.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Is it possible to connect a Google Forms Survey directly to Microsoft Access database?

Post by wire_jp »

Hi Hans and Gasman,

Thank you for your help.

Kind regards,

wirejp