Possible to access... Access database table if file name changes (fixed prefix)?

Hi all,

I tried looking online but couldn't find anything on this. Basically, I have an Excel spreadsheet where I pulled data from an Access database using the "From Access" button and following the instructions. The database was imported successfully, I added the macros that were needed in the spreadsheet, and making changes in the .accdb data and refreshing works fine too. H

However, it seems the accdb file's name is supposed to change, which means connectivity cannot be maintained. The accdb file's name format is simply "Stuff Access Database [Month] [Day]", for example "Stuff Access Database March 23". Is there a way to maintain the connectivity by, say, checking only if the file has "Stuff Access Database" as a prefix, rather than the whole name? No other file in the directory has this same prefix. I understand this is a long shot, but thought I'd try asking any way.

Thanks!

There is a way. It involves using MS-Query (More primitive, but can be manipulated more easily) and then changing the connection string.

To get to Ms-Query select Data -> Get Data -> From other sources -> From Microsoft Query.

As soon as the dialog box comes up, check off the box that asks you to use the wizard. The wizard is more of a hindrance than a help.

MS-Access is one of the data sources set up. Select it and navigate to the directory and database you want. Then select the table or query you want and you will get an Access-like user interface where you can drag and drop the fields where you want them. I suggest creating a query in Access that does what you want. However, do NOT prompt for anything. If you need to pass a parameter, that can be done on the MS-Query side. In fact you can read parameters from cells and these cells ma even have formulas like =Today() -30.

The connection string can be manipulated with code. So if the database has a date stamp and the name can be calculated automatically, the connection can be updated automatically. You can also play with the command string and rewrite the SQL on the fly. We had two tables with identical structures: one held mid-month data and the other end-of-month data. I was able to switch between the tables with a drop down list in a cell.

Check out MS-Query and let me know if you want to go down that path. I will dig up and send you some more advanced documentation. If so, I should be able to help set you up.

Microsoft isn't peddling MS-Query as much anymore. It's kind of like an old automobile; it works and it works well but more importantly, it's easy to work on the engine. The modern data access may be flashier, but it's more difficult to manage.

Here is what I am talking about. This was done for an Oracle database. The pieces of the connection string are different than Access, but the technique is the same. I've done Access before.

http://www.utteraccess.com/wiki/ODBC...ction_VBA_Code