Changing Access Database table in excel

You'll need VBA to change the connection string.

The way to do it is to take the existing string and SPLIT it with the semicolon delimiter, find the pieces that point to the old database, replace them with the new pointers and then JOIN the parts back.

Here is an example of code that changes the password in a connection string

Please Login or Register  to view this content.

The example I gave looks at a connection string for an Oracle ODBC connection. If there is a saved password, it is printed IN THE CLEAR in the connection string.

I do know what a connection string looks like for Access. It looks something like this:
DSN=MS Access Database;DBQ=\\TSGVM01484\Common\Labor Analysis\Amazon\Database\Data\Amazon Data.accdb;DefaultDir=\\TSGVM01484\Common\Labor Analysis\Amazon\Database\Data;DriverId=25;FIL=MS

I don;t see a provision for a password in this connection string. If there is a password, it is managed within the database, most likely after the connection. If you are accessing this data now, then you should be able to access it when you point to it with the connection string.

It looks like the part you want to change is the DBQ=.

As for the code, put it in a regular module. As for the string to replace, go to the Data Ribbon, Select queries and connections. Click on the query in question. go to the second tab, copy / paste the connection string to a post, and I'll see what I can do with it.

This should work. I have a different kind of connection so I can't test the code, but the concept is the same.

Please Login or Register  to view this content.

This code is set up to go through ALL the workbook connections and reset them. It will break connections if they are not of this type. If you want to do just one connection, then eliminate the lines of code in red and replace the piece in green with the name of the connection in quotes.

The code is meant to take parameters for the user ID and the new database. The following shows an example where the User ID is in cell A1 and the new db name is in cell A2.

Please Login or Register  to view this content.

I just noticed an error. I have to make the strings look exactly alike so replace

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

Sometimes the entire connection string is NOT shown in the properties of the connection. There is sometimes a "Prefix" that is not shown.

Try this in in a module to see what Excel thinks the connection string really is:

Please Login or Register  to view this content.

Originally Posted by dummy777

Is there also a way to add in the code to change the connection file and connection name? or it is manually changed?

Explain to me a bit more. Verbally walk me through the process of how you change the databases.

I'm just speculating on how you do business. As you can see, with the Test macro in my OP, I read these values from cells in the workbook and pass them as parameters to the routine. The database has a name that can be generated by the current date. So, the cell that contains the database name could be calculated.