Create a Query that won't mess up when linking a new table

[DavidMichaelangelo]

It was hard to summarize this in a title so here's more details:

Basically I have a query that looks at a standard table that I have to update each week (I use Import > Access Database > Link to the Data Source by Creating a Linked Table).
So the table name in Access remains the same but the name of the linked database changes.

This causes problems for my queries (but oddly, not all the time).

How can I update a table with a different source name and not confuse any queries that work off it?



Thanks!

[Dal Jeanis]

1) Is this linked table actually an excel spreadsheet, or is it a real database somewhere?
2) The name of the DATABASE is changing weekly, really?
3) Is the data in the database changing during the week, or is it only being updated once a week?
4) How big is the database table involved?

[DavidMichaelangelo]

1. It is a database that resides on my desktop
2. The name of the database changes each week because a date is appended to it. I could always strip the date from the name but then I'll lose track of the date of the database :P
3. It is updated exactly once per week
4. The database is rather large (1.89 GB)

[Dal Jeanis]

Procedural Solution #1 - Keep a weekly backup copy of the database with the date on it, but let the database itself have no date. This requires one extra step when the database updates (creating the backup copy). Personally, I would also set the update process to create an internal "last updated" date in the database as well.

If that doesn't fit your needs, here's a couple of more questions -
5. Is the format of the database ever changed by anyone but you?
6. Are you the one that causes the data updates to occur, or is some automated process creating the updated & dated version of the db?

[DavidMichaelangelo]

Thanks. I have thought of that but would prefer to avoid that solution if possible.

5. The format of the database is only changed by me
6. I do not cause the data to update. An updated database is made available on a server each week - I simply take a local copy of that database (I'm always linking to the database on my desktop, not the one that gets updated on the server)

Thanks!

[Dal Jeanis]

I find it hard to reconcile your answers to 5 and 6. If your data source just "is made available" on a server once a week, how can you be the only person who updates the format of that data source? Did you set up the production process and now you are the end user of the result?

I'm not trying to lead you far afield, I'm just making sure I don't make any bad assumptions.

[DavidMichaelangelo]

Oh, sorry, I thought you meant if anyone touches the final database where I run my queries.
The source database is updated by one person, I do not touch it. However, the fields always remain the same.

[Dal Jeanis]

Okay, it's vaguely possible the the occasional query glitches are from someone making unannounced updates to the production database.

7. Since you are downloading the database weekly from a "production" database... can I assume the copy on your desktop is an exact copy of the latest production version? No mods at all? Or do you have an update process other than downloading the production db to your desk under a dated name?

8. Can you tell me the reasoning behind your reluctance to have a non-dated copy on your desk?

[DavidMichaelangelo]

7. The database is an exact copy.
8. I can use a non-dated copy but I figured I shouldn't need to. When I link in the dated database in Access, the table retains the same name ("Customer Master"). So shouldn't the queries still work as long as the table name remains the same? The issue has been that for some reason, some queries break, but not all. When I look at the broken queries in SQL view, I can see that the queries are referencing the linked database directly instead of the table for some reason.

[Dal Jeanis]

Hmmm. Nothing obvious in this one. I'd be interested in whether the queries occasonally break without the rename of the database.

9. When you say, "are referencing the linked database directly", how does that actually look?

[DavidMichaelangelo]

They don't break without a rename occurring.
I think my issue is that I need to relink the database and then rerun the query. If I try to run the query and it cannot find the database then it seems to mess the query up - perhaps permanently?

9. Instead of [Customer Master].[First Name] it will say [customermaster_20131115].[First Name] or something like that. I'm not very familiar with SQL so I don't know what is happening but it looks to me like it ends up referencing the database name directly instead of the linked table. I'm not sure I know what I'm talking about, however. haha

I think the issue relates to the "visual" access editor. I get an error that says the query cannot be represented visually and then spits out the SQL to me. My theory is that this "error" causes the SQL to spit out the direct reference to the now missing database source. I just verified that the SQL to my queries do in fact reference the linked table and not the datasource directly. Therefore, I thought as long as I relinked the changed database under the same name, the query would not have a problem.

Anyways, I do apologize for taking up so much of your time. The issue doesn't happen too often so I can probably just get by / figure it out as I go.

Thanks,

[Dal Jeanis]

Apologies aren't necessary - the only time you can take up is voluntary.

Yeah, it sounds like you need to make sure to relink before you use any of the queries.

You might be able to set up a VBA routine to do the relinking for you. See this http://blogs.office.com/b/microsoft-...s-tables-.aspx to start.

[DavidMichaelangelo]

Thanks for all the help Dal!