Query Changes

[Jeff]

Hi,

I have a query and in the query is a linked table. I went to design view in
the query, and the table was not linked. So then I closed without saving,
linked the table and tried to rerun the query. But the query failed because
the link that was there was magically erased when I went to design view. I
did not save the query.

Is there any way to fix this? Dont you think this is a major pain with
Access?

Thanks for your help.

 

[Jeff Boyce]

Jeff

Perhaps it's a matter of semantics ... what do you mean by "linked table".

The way Access handles this is that a table not "local" to the database
you're working in is "linked".

What do you mean by "query in a linked table"? What do you mean when you
say the table isn't linked in your query? Are you describing "joins"
between tables in your query design view?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff]

Sorry my post wasnt that clear.

QueryX:
My query has two tables linked by a "bar", so the field is linked.

So - Table1 is linked to Table2 in the query by fieldA.

Problem:
The Table1 was a "Linked table" so the table was not in Access, the table is
on my C: drive. But - here is the big issue, when I viewed QueryX, the Table
was not on my C: drive, so it was not linked to anything, so the QueryX had
no "link" between Table1 and Table2 by fieldA. So I closed the query AND DID
NOT SAVE.

Then I relinked the table and the query did not have the "link". So the
query changed. Also some fields in the query had "exp1" "exp2" in the column
headers.

But basically the problem is that if you view the query and do not save, it
still changes automatically and breaks itself. That seems like a problem.

 

[Jeff Boyce]

Jeff

Some "Access" terminology for the future... (see comments in-line)

Sorry my post wasnt that clear.

QueryX:
My query has two tables linked by a "bar", so the field is linked.

Access queries "join" tables. The term "linked" is reserved for tables that
live in other dbs but are visible in the one you're working in.

So - Table1 is linked to Table2 in the query by fieldA.

Problem:
The Table1 was a "Linked table" so the table was not in Access, the table
is

So this is using the standard Access definition of "linked".

on my C: drive. But - here is the big issue, when I viewed QueryX, the
Table

"When I viewed QueryX" ... do you mean open the query in design view?

was not on my C: drive, so it was not linked to anything, so the QueryX
had

How would you know from within a query in Access that the table wasn't where
it should be? By the way, tables don't exist on their own, but only inside
an Access .mdb (or other source). Are you saying that the Access .mdb file
that was supposed to be on your C: drive is no longer there? If so, you'll
need to go to the Tables in your database and either re-link (Access
definition) to the new location or your query will fail!

no "link" between Table1 and Table2 by fieldA. So I closed the query AND
DID
NOT SAVE.

Just because you didn't say SAVE doesn't necessarily mean Access didn't (do
you mean you chose the don't save button?). Access can get its 'warnings'
turned off and you won't be notified.

that if you view the query and do not save, it
still changes automatically and breaks itself. That seems like a problem.

It may be that your query has gotten corrupted. If this were mine, I'd
first ensure that both TableA and TableB were available in the .mdb, then
I'd create a new query joining them on FieldA (to use your description).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff]

Jeff -

OK

QueryX: is composed of two tables, TableA and TableB which are joined.

TableA is linked to a csv file on my c drive. To do this you right click in
"Tables" and choose "Link". Right?

In the "Queries" portion of Access I highlighted QueryX and chose Design.
I did not save the query, the warnings were not turned off.

So getting over the terminology and semantics the question remains -
Why does access change the query when you view the query in Design View.

Most people I talk express similiar concerns. This has happened multiple
times and it makes working with Access very difficult. A table is deleted or
not linked. (To test this you could just link to a table - you could create
a csv file and link to it) the query changes, even if you just open "Design"
view.

Thanks!

 

[Jeff Boyce]

Sorry, I can't help with this. In my experience, if I have a table in an
Access .mdb, whether linked or local, I can create a query.

If I open a query in design view and include a table that I have in that
..mdb, it doesn't come/go.

Perhaps other newsgroup readers have more experience with something like
this.

Have you tried creating a new query and seeing if it has the same issues?

Regards

Jeff Boyce
Microsoft Office/Access MVP