Design view (select query with link table issues)

[Guest]

When creating a query, using the simple query wizard the linking of all
tables are done correctly. However, when designing an existing query the
tables that I add are not automatically linked. The database is split (all
forms, queries, reports are in one database and all the tables live in the
other). As far as I can see the relationships look good. I've tried resaving
the relationships (no changes made) and used the repair option. There are
many users with their own "front end" copy, pointing to the shared "backend".
Not all users are having this problem. I am at a loss as to how to correct
the issue. Any help would be most appreciated. Thank you in advance.

 

[Allen Browne]

Kim, you are talking about a query that draws from tables that are not
linked, so presumably you mean this kind of thing:
SELECT * FROM Employees IN '\\Server1\MyPath\Northwind2003.mdb';

If that works for some users and not others, you are trying to trace what is
different about the machines where it does not work.

The issue could have to do with permissions to use the data--either Access
permissions, Windows user permissions, or file-share permissions. It could
also have to do with the names used, e.g. you connnect to a database on
drive P:, but a user does not currently have a drive P: defined.

If none of those ideas work, it is possible that some users need to update
their JET 4 service pack. Track down msjet40.dll, typically in
windows\system32. Right-click and choose Properties. On the Version tab, you
should see the 8 in 4.0.8xxx.0. If not, down load and install SP8 for JET 4
on those machines, from:
http://support.microsoft.com/gp/sp

 

[Guest]

Hi Allen,

The tables are linked tables, and link properly when using the wizard. My
problem is when modifying an existing query, trying to add a known "linked"
table the link doesn't happen automatically for all users. As a test I
removed the table from the relationship view, added it back, created the
link, and saved the relationship. After doing so the links were properly
added while in design view.

What could have caused the problem? Throughout my tests I used a user that
had full admin rights and the drive mappings/shares were fine. I did not
check the file, but will if you think that is the root cause. Thanks.

 

[Allen Browne]

So these are linked tables, but they do not work on some user's machines?

Presumably you have copied the mdb file from one machine to others, and if
you try to open the table directly in the database window, you get some kind
of error message? And if you relink the tables, they work?

If that is the case, you will need to run some code on startup to check that
the links work correctly. Solutions.mdb (sample database) has an example of
how to check links, and if not found pop up the File Open dialog so the user
can select where the back end file has gone. You should be able to download
the sample from:
http://msdn.microsoft.com/library/officedev/bapp2000/mdbdownload.htm