query problem with linked SQL tables when importing to new mdb file

[Keith G Hicks]

I'm using Access 2k with linked tables from MS SQL Server 2k db. (everythign
is fully updated)

This system has been running just fine for over a year. No problems. Just
recently it appeared that perhaps the mdb might have become corrupt so I did
what I used to do with my older Access apps. I created a brand new empty
front end mdb file and imported all the local tables, queries, forms, etc.
into the new one from the old copy. Everything imported just fine (no errors
came up during the import).

Here's the problem. All the queries that had 2 or more tables in them with
links between fields, the links were blown away AND many fields now were
showing things like "Expr1: CustName" in the top row instead of just
"CustName". Man this really fouled things up. I've never seen this before.
Whenever I've had to import a front end mdb into a new one where the backedn
tables were jet tables, all was fine.

Does anyone know why this would happen? I tested it several different ways
and alwasy got the same result.

One other note. I'm running Access 2003 but the system I'm referring to is
all in Access 2000 format. When I create the new empty mdb file it's also
showing Access 2000 file format so I wouldn't exepct that that could be the
problem but I just figured I'd point this out. I'm more convinced that it
has something to do with the fact that the linked tables are SQL and not
Jet.

Also, of course when I import the old front end ojbects into the new mdb
file, I'm not importing the linked tables. they just get relinked in the new
front end once it's got all the other objects imported. I run my linking
code and all is fine except for teh fouled up queries. It's almost as if
during the import, Access opens up each query and because it can't find the
tables it changes the layout of each one as if the talbes were not there
(like when you forget to link your tables and open up a query it's goofed up
and then when you go to close the query, Access asks if you want to save the
changes (of course you say no because you didn't make any changes, it's just
because your tables are not linked). Again, I never have had this happen
when the linked tables are Jet.


Thanks,

Keith

 

[Keith G Hicks]

Here's something interesting. I just tried this. If you import everything
except the queries, then link the talbes, then import the queries, all seems
to be ok. But I'd be very concerned about embeded queries in form or report
recordsources or combo or list boxes. It will take some testing to see if
those get fouled up.

Keith

 

[Keith G Hicks]

Well, I added a report to the original mdb file that has a complex select as
its source (2 tables with an outer join) rather than a stored query and also
added a combo box to a new form where the combo box has the same source as
the report (so it's not a stored query either). When I imported those
objects along with the everything else and then rellinked the tables AFTER
the import, their queries were NOT messed up. So from what I can see, it
only seems to affect stored querys.

So there seems to be a work around here, but I'd sure like to know what the
problem is and if there are any other hidden issues to be considered that I
might not be aware of.

Thanks,

Keith