Excel's Get External Data feature to create a connection string

Question

I am using Office 365. When I try to import an Access table into Excel I get the following error. I haven't done anything other than open the Access database. I'm working my way through the book Access 2016 Bible and am currently in the 28th Chapter "Accessing Data with VBA".

Answer 1

I apologize for the confusion. It seems that you're using Excel's Power Pivot instead of Power Query to import data from your Access database. In Power Pivot, the process for viewing the connection details and extracting the connection string is slightly different. Here's how you can do it in Power Pivot:

1. **Open Power Pivot**: To access Power Pivot, go to the "Power Pivot" tab on the ribbon. Click on "Add to Data Model" in the "Tables" group, and then select "From Access" (the options might be slightly different based on your Excel version).
2. **Connect to Access Database**: In the "Table Import Wizard" dialog box, browse and select your Access database file (.accdb or .mdb). Click "Next" to proceed.
3. **Import Data**: In the "Select Table" step of the Table Import Wizard, choose the tables you want to import from the Access database, and click "Finish."
4. **Open Power Pivot Window**: After importing the data, you should see a new "Power Pivot" tab on the ribbon. Click on "Power Pivot Window" in the "Power Pivot" tab to open the Power Pivot window.
5. **View Connection Details**: In the Power Pivot window, go to the "Home" tab and click on "Existing Connections" in the "Get External Data" group.
6. **View Connection Properties**: In the "Existing Connections" dialog box, select the connection that corresponds to your Access database and click "Open."
7. **View Connection String**: In the "Connection Properties" dialog box, you will find the connection details, including the "Connection String" you need for your VBA code.
8. **Copy the Connection String**: Click on the "Definition" tab in the "Connection Properties" dialog box. There, you should see the "Connection String" field. Click on it, and you should be able to copy the connection string to your clipboard using Ctrl+C or right-click and select "Copy."

Now you should have successfully extracted the connection string from Power Pivot. You can paste this connection string into your VBA code to establish a connection to the Access database.

Again, I apologize for the earlier confusion. If you still encounter any issues or have further questions, please feel free to ask.

Answer 2

In order to get the Power Query Editor I have to choose Transform. Then I see a gear.

Clicking the Source 'Gear' I get this:

Is what is showing above what I'm looking for? Because it looks nothing like either of these examples from the book

![Image](https://learn-attachment.microsoft.com/api/attachments/42fab274-c9f5-45e2-b845-40a719e62e88?platform=QnA

Answer 3

You may need to do a bit more trial and error as you keep referencing a book from 2016 which is likely going to have a few discrepancies.

Try the troubleshooting steps that I posted where you got lost at the gear step and see if you can follow through the steps to get the VBA code.

If not then try the second set of steps I included for Power Pivot versus Power Query.

Answer 4

This is what I get

Answer 5

Do you have a teacher helping you with the study of this book that might be used to the differences in dialogue and content, or are you studying the book alone?

As you are also having issues translating the information into a system 7 years later you can understand this is even harder to do remotely.