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

[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

[HansV]

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

[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

[HansV]

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

[wire_jp]

Hi Hans,

Thank you for the information.

Kind regards,

wirejp

[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

[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.

[wire_jp]

Hi Hans,

Thank you for your help and for the information.

Kind regards,

wirejp

[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.

[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.

[Gasman]

Perhaps you can adapt code from one of these links?

https://www.google.com/search?q=access+ ... e&ie=UTF-8

[wire_jp]

Hi Hans and Gasman,

Thank you for your help.

Kind regards,

wirejp