Nothing jumped out at me, so I asked GPT...

It looks like you are encountering an automation error while trying to open an Access database from Excel using VBA. This issue might be related to the way you're constructing your connection string and how it's assigned to the Provider property of the ADODB.Connection object.

Here's a corrected version of your script that ensures the connection string is properly formatted and applied:


Dim Cnn As ADODB.Connection
Dim DBConStr As String
Dim DBLocation As String

' Set the database location
DBLocation = "C:\Users\Richard W. Stein\Documents\Financial Planning\FPWizard.accdb"

' Construct the connection string
DBConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBLocation & ";Persist Security Info=False;"

' Create a new connection
Set Cnn = New ADODB.Connection

' Assign the connection string to the Connection object
Cnn.ConnectionString = DBConStr

' Open the connection
Cnn.Open


### Key Changes:
1. **Connection String Construction**: I've corrected how the connection string is constructed and assigned. The Provider should be part of the connection string, not separately assigned.
2. **Proper String Concatenation**: Ensure that the connection string is properly concatenated with the DBLocation.
3. **Instantiation of Cnn**: Using Set Cnn = New ADODB.Connection to properly instantiate the connection object.

Make sure that:
- The path to the database is correct and accessible.
- The version of the ACE OLE DB provider you specified (Microsoft.ACE.OLEDB.12.0) is installed on your machine.
- Excel has the necessary permissions to access the file path specified.

This should resolve the automation error. If the issue persists, it may be worth checking for additional details provided by the error message or ensuring that all references required for ADODB.Connection are correctly added to your VBA project (usually via Tools -> References in the VBA editor).

I closed Access and opened it up again and the code worked.

Thanks

Haha of course. Always run down the Troubleshooter first.