Is it possible to connect a Google Forms to a linked Microsoft Access table using VBA code?

Question

Is it possible to connect a Google Forms Survey (URL website) directly to a linked table in a Microsoft Access database and pull the respondents' responses directly into a linked MS table using VBA code? 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.

Thank you in advance for your help.

Answer 1

Directly connecting a Google Forms survey to a Microsoft Access database and pulling responses into a linked table using VBA code might not be straightforward due to the different ecosystems involved (Google Forms, Google Sheets, Excel, and Access). However, you can automate the process to some extent. Here's a possible approach:

1. Google Forms and Google Sheets:
   • Continue using Google Forms to collect survey responses.
     • Set up the Google Form to automatically create a Google Sheet with responses.
2. Google Sheets and Excel:
   • Use Google Sheets to Excel integration. You can automatically export data from Google Sheets to an Excel file using Google Apps Script.
     • Write a script in Google Apps Script that triggers when a form response is submitted. The script can copy the new response data to an Excel file stored in a location accessible by your Access database.
3. Excel and Microsoft Access:
   • Link the Excel file to Microsoft Access as you're currently doing.
4. VBA in Microsoft Access:
   • Write VBA code in Microsoft Access to periodically check the linked Excel file for new data.
     • Import the new data from the Excel file into the linked Access table.

Here's a simplified example of the VBA code you might use in Access:

Sub ImportDataFromExcel()
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object
    Dim accTable As DAO.Recordset
    
    ' Path to your linked Excel file
    Dim excelFilePath As String
    excelFilePath = "C:\Path\To\Your\File.xlsx"
    
    ' Open Excel
    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Open(excelFilePath)
    Set xlWorksheet = xlWorkbook.Sheets("Sheet1") ' Change to your sheet name
    
    ' Open Access table
    Set accTable = CurrentDb.OpenRecordset("YourLinkedTable", dbOpenDynaset)
    
    ' Loop through Excel data and append to Access table
    Do Until xlWorksheet.Cells(i, 1).Value = ""
        accTable.AddNew
        accTable.Fields("Field1").Value = xlWorksheet.Cells(i, 1).Value ' Adjust field names
        ' Repeat for other fields
        accTable.Update
        i = i + 1
    Loop
    
    ' Close connections
    xlWorkbook.Close
    xlApp.Quit
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
    accTable.Close
    Set accTable = Nothing
End Sub

This is a high-level overview, and you may need to adjust the code based on your specific requirements and data structures. Keep in mind that automation like this requires careful error handling and security considerations, especially when dealing with external data sources.

If you can you should use a Microsoft Form instead, saving you some steps.