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

wire_jp 206 Reputation points
2023-11-22T14:21:09.4966667+00:00

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.

Access
Access
A family of Microsoft relational database management systems designed for ease of use.
341 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
857 questions
Microsoft 365 Publishing
Microsoft 365 Publishing
Microsoft 365: Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line. Publishing: The process of preparing, producing, and releasing content for distribution or sale.
606 questions
{count} votes

Accepted answer
  1. Akshay Kothari 85 Reputation points
    2023-11-27T16:42:52.7666667+00:00

    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.

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful