Access not finding Linked Tables

[Internet Arrow Limited]

Hi,

I have a requirement to write an access application that must run
under access97 and access2K. Some users will use Acess2K to access
data that will also be accessed by Access97 users. The source data
must therefore remain in Access97 format

The user community has a mixture of access97 on WinNT; access2K on
winNt and access2K on win2K.

I have written the application such that the databases are Access97.
The application is in a seperate database. The application fetches
data via linked tables.

When the application is installed on an Access2K PC, I upgrade the
application database to Access2K, but leave the linked tables as
Access97.

However, when running on WindowsNT, access fails to see the linked
tables.
The application database (that contains all the code) is called
"carallow", the data is stored in a database called "carallowtables".
Both are .mde files.

Tthese files are in seperate directories (eg, the carallowtables is
saved on a network drive accessible by all; the application is saved
on the user's harddisk). As an example, the data can be stored on
G:\tables with the application on c:\carallow. The directory
locations are stored as attributes of the database in
Properties("AppsLocation") and Properties("DBLocation")

I use the linked tbale manager to specify the location of the tables
(g:\tables\carallowtables.mde) and can see the data fine in native
access.

However, when I open carallow.mde and run the code from within the
application as follows

===========
Dim wrkJet As Workspace
Dim dbsCarallow As Database
Dim rstTemp As Recordset

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsCarallow =
wrkJet.OpenDatabase(CurrentDb.Properties("AppsLocation") &
"carallow.mde")
Set rstTemp = dbsCarallow.OpenRecordset("SELECT * FROM batches
WHERE isnull(fileDate)", dbOpenDynaset)

===============

Access gives the error message "can not find table
c:\carallow\carallowtables.mde". In otherwords, it doesn't seem to
recognise that they are linked tables stored under g:\tables.

This only happens when the apps database and data database are in
seperate directories. It works fine when they are in the same
directory.

Am I doing something wrong or is this a bug that I need a fix for.

Any help would be gratefully appreciated,

Thanks,
Paul

 

[Albert D. Kallal]

While you are using linked tables, your code example does not seem to use,
care, or rely linked tables as far as I can see (did I miss something
here?).

I not sure why you are using your own workspace and opening the back end
directly here.

However, the first thing I would check is your custom property.

I would put a msg box command in there to see what the actual value
AppsLocation is.

msgbox currentdb.Properties("AppsLocation")

You just want the above to see and double check what the above value is.

Further, of cause each user does get their OWN COPY of the front end, right?
(this standard and recommend). Further, you probably dump the used of mapped
disk drives. Mapped drives are a pain, and they are subject to change. I
would use a full unc patch name to the file share

\\servername\AccessApps\youMdbName.mdb


You can certainly check if the back end is NOT in the location you expect,
and then offer the user the chance to re-link to the back end.

The user community has a mixture of access97 on WinNT; access2K on
winNt and access2K on win2K.

Yes, you can do this. The back end stays in a97, and you simply distribute a
front end that is linked to this back end. Each user gets this copy of the
front end their own computers. It is a simple matter to distribute a front
end that matches the version of ms-access on each workstation. (this does
seem what you are doing now..I just making sure we are on the same road
here!).

However, when I open carallow.mde and run the code from within the
application as follows

In your example that follows, you don't use any of the linked tables, and go
off and use some custom value from the Properties collcton. That custom
value will NOT change if you move the location of the mde.

Why not just use the linked table?

You can go:

dim rstTemp as dao.recordset
dim strSql as string

strSql = "SELECT * FROM batches WHERE fileDate is null"
Set rstTemp = currentdb.OpenRecordset(strSql)

The above will work just fine if the table is linked.

Note how I also removed the isnull function. You don't want to use VB
function in the sql unless you really have to. (the query processor can't
optimize VB functions, and you force jet to use VB for each record).

Now, since the table batches has already already been linked via the linked
table manager, then the above code should work fine. You don't need to use a
work space.