Connect MS Access with Google Sheets

[daniel]

Introduction

This article guides you through connecting Microsoft Access to Google Sheets using ZappySys ODBC PowerPack. This integration allows you to seamlessly transfer data between Google Sheets and a local Microsoft Access database.

Prerequisites

• ODBC PowerPack: Download and install the ZappySys ODBC PowerPack.

• Spreadsheet: Create and fill a sheet on Google’s side

• MS access installed

Use Case

Using Google Sheets Connector, you can connect, read, and write data from within MS Access.

Steps

Create/Select a Project in Google API Console:

1. Navigate to Google API Console.

2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.

   

3. Once the project is set, click ENABLE APIS AND SERVICES.

4. Enable both Sheets API and Drive API by searching for them and clicking ENABLE.
   

5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

6. Move to the Credentials tab.

7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.
   

   

Create/Select a Project in Google API Console:

1. Open ODBC Data Sources (x64).
   

2. Create a User Data Source (User DSN) based on ZappySys API Driver.
   

   

3. Select Google Sheets from the list of Popular Connectors.
   

   

4. Fill in the connector fields with your Client ID, Secret ID, and Spreadsheet ID.

5. Generate the token and test the connection
   

   

6. Move to the Preview tab and configure the settings for data preview.
   

   

Read data in Microsoft Access from the ODBC data source

1. First, open MS Access and create a new MS Access database.

2. In the next step, start loading the ODBC data source we created:
   

   

3. Then click next until the data source selection window appears. Select the data source we created in one of the previous steps and hit OK:
   

4. Continue with tables and views selection. You can extract multiple tables or views:
   

5. Finally, wait while data is being loaded, and once done, you should see a similar view:

   

Considerations

• Save the credentials for future use to streamline the connection process.

Tips

• Retrieve a list of Spreadsheet IDs from your Google Sheets account for reference.

Conclusion

Following these comprehensive steps, you can effortlessly connect MS Access to Google Sheets using ZappySys ODBC PowerPack, enabling smooth and efficient data transfer. For any issues or further assistance, reach out to our support team via chat on the ZappySys website or through email at support@zappysys.com.

References

You can check our MS Access Google Sheets Connector article here.