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

Hello Clifford C, thank you for reaching out to us today and I'm sorry to hear that you are getting this error.

The error message you provided indicates that the Access database you are trying to connect to is in a state that prevents it from being opened or locked. This error typically occurs when the database has been placed in a read-only state or when there are permissions issues that restrict access to the database.

Here are some steps you can take to troubleshoot and resolve the issue:

1. **Check Database Permissions**: Ensure that you have the necessary permissions to access the Access database. If the database is password-protected or restricted, make sure you have the correct credentials to open it. If the database is located on a network drive or shared location, ensure that you have the appropriate access rights.
2. **Database File Status**: Check if the Access database file is currently open or being used by another application or user. If the database is open in Access or any other program, it may be locked for editing. Make sure the database is closed before attempting to import it into Excel.
3. **Copy the Database**: If possible, make a copy of the Access database file and try importing the data from the copied version. This can help rule out any potential issues with the original database file.
4. **Use Power Query**: Instead of using the Get External Data feature in Excel, try using Power Query (Get & Transform Data). Power Query provides more flexibility in establishing connections and may help bypass certain limitations.
5. **Check Database State**: Open the Access database directly in Microsoft Access and check its state. Look for any messages or alerts that indicate the database is in a read-only state or if there are any other issues preventing it from being opened.
6. **Office 365 Updates**: Ensure that your Office 365 installation is up-to-date. Software updates may include bug fixes or improvements that could resolve the issue.
7. **Contact Administrator**: If you're working in a corporate environment or with a shared database, reach out to the database administrator or IT support for assistance. They may be able to provide insights into any specific restrictions or issues with the database.

Answer 2

I am using Office 365 and the files I downloaded from www.wiley.com for the book Access 2016 Bible.

Answer 3

First of all, remember that all of this is just to get the connection string to paste into VBA. I was able to import an access table once I closed the Access database. What I am looking for is the Connection String, The book says "One way to get the correct connection string syntax is to is to use Excel's Get External Data feature. I'm did that but nothing looked like the dialog boxes in the book and the connection string was greyed out so I couldn't copy it.

Now maybe you can walk me through this import process. Here is the first dialog box I see after choosing the Access database file.

If I choose Transform Data I get the data in a Power Query Editor. If I choose Load To I get the following:

If I choose Load it gets confusing and fooling around with what I do get only puts me back to the Power Query Editor. I'm lost. What I'm looking for is a Connection String I can paste into VBA.

Answer 4

I apologize for the confusion.

It seems that you are trying to retrieve the connection string from Excel's Power Query Editor.

While the process can be a bit convoluted, I can guide you on how to extract the connection string from the Power Query Editor. Please follow these steps:

1. **Open Power Query Editor**: To access the Power Query Editor, follow these steps:
   • In Excel, go to the "Data" tab on the ribbon.
   • Click on "Get Data" in the "Get & Transform Data" group.
   • Select "From Database" and then choose "From Microsoft Access Database."
2. **Connect to Access Database**: In the "From Microsoft Access Database" dialog box (the one you showed in [image 1]), browse and select your Access database file (.accdb or .mdb). Click "Import" or "Load" to proceed to the Power Query Editor.
3. **View Connection Details**: In the Power Query Editor, you should see the data from the Access database. On the right side of the window, you'll find the "Queries & Connections" pane. Within this pane, there will be a section called "Queries" with a query name (usually named "Query1" by default).
4. **View Query Options**: Click on the gear icon (settings) next to the query name (Query1) to access the Query Options.
5. **View Connection Properties**: In the Query Options dialog box, go to the "Usage" tab. Here, you'll find the connection details, including the "Connection String" you need for your VBA code.
6. **Copy the Connection String**: In the Query Options dialog box, you'll 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 have successfully extracted the connection string from the Power Query Editor. You can paste this connection string into your VBA code to establish a connection to the Access database.

Keep in mind that the connection string may look something like this (an example for an Access database):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Your\Path\To\YourDatabase.accdb; Persist Security Info=False;

Remember to replace the path with the actual location of your Access database file.

If you are using an older .mdb format, the connection string might be slightly different.

I hope this helps you get the connection string you need for your VBA code! If you encounter any issues or have further questions, feel free to ask.

Answer 5

At number 4 I lose you because this is what I see: And there are 0 Connections. I see NO gear or settings. If I click on what looks like a piece of paper with the top right corner missing I see the image below this one.

Microsoft is making this process way too confusing.