Linked Front End database

[Guest]

A couple of quick questions on Linked databases :-

1. If the Front End of a split database contains a table linked to a Back
End database, is it possible to programmatically determine the name of the
Back End database from code in the Front End database?

2. Is it possible to programmatically determine the version of a database
(i.e 97 or 2K etc.) from code in a database? It happens to be a Front End
again in this case. The Back End is 97.

 

[TC]

1. If the Front End of a split database contains a table linked to a Back
End database, is it possible to programmatically determine the name of the
Back End database from code in the Front End database?

Yes. Check out the Connect property of any linked table. That property
contains something like this: ";DATABASE=blah", where blah is the full
pathfilename of the linked-to database. You'd better check the actual
format of the value; that is just from memry, I don't have Access handy
to check.

2. Is it possible to programmatically determine the version of a database
(i.e 97 or 2K etc.) from code in a database? It happens to be a Front End
again in this case. The Back End is 97

Try Application.AccessVersion.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

Sorry:

Application.Version

2 = Access 2
7 = Access 95
8 = Access 97
9 = Access 2000
10 = Access 2002 (XP)
11 = Access 2003

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Thanks TC.

1. Worked fine with :-

Dim dbs As DAO.Database
Dim strVersion As String

Set dbs = CurrentDb
strVersion = dbs.Properties("AccessVersion")

2. I keep getting Null in strB below :-

Dim tdfLinked As DAO.TableDef
Dim dbs As DAO.Database
Dim strB As String
Dim strTable As String

Set dbs = CurrentDb

strTable = "tblA"
Set tdfLinked = dbs.CreateTableDef(strTable)
strB = tdfLinked.Properties("SourceTableName")
MsgBox strB

Set dbs = Nothing
Set tdfLinked = Nothing

Any ideas? (tblA is definately a linked table to a valid database).

 

[TC]

Thanks TC.

1. Worked fine with :-

Dim dbs As DAO.Database
Dim strVersion As String
Set dbs = CurrentDb
strVersion = dbs.Properties("AccessVersion")

Ok, fine.

2. I keep getting Null in strB below :-
Set dbs = CurrentDb
strTable = "tblA"
Set tdfLinked = dbs.CreateTableDef(strTable)

Nooooo! You need to examine the existing tabledef objects - not create
a new one.

Here's the text of a function that I plan to use in an article that I'm
currently writing. I haven't tested it yet, but it should be ok. Give
it a try, & say how it goes:


' This function returns the name of the external database
' file containing all the actual tables. There must be at
' least one linked table in the current database. And all
' of the linked tables, must be in one external file.

Public Function LinkedDBName() as string
dim db as database, td as tabledef
dim n as integer, sName as string
set db = currentdb()
for each td in db.tabledefs
n = instr (td.connect, ";DATABASE=")
if n > 0 then
sName = mid$(td.connect, n+10)
if sPrev = "" then
sPrev = sName
elseif sName <> sPrev then
err.raise 5,,"More than one linked database!"
endif
endif
next
set db = nothing
if sName = "" then err.raise 5,,"No linked tables!"
LinkedDBName = sName
end function

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Excellent - you just missed declaring sPrev and it may be useful to pass back
td.Name as well.

Thanks TC.

 

[Guest]

Hi TC,
What is sPrev equal to? It is not mentioned and I can't figure it out.
Thanks. Your post and Andy's questions and answers helped me a lot.

 

[TC]

Aha, you're right! Thanks for finding that.

I would not necessarily want to "pass back td.Name as well" - for the
following reason. There would generally be *many* linked tables, and
thus, many relevant td.Name's. The purpose of the function is not to
return the names of the linked tables; it is to return the name of the
linked-to database file. There are many of the former - but just one of
the latter (under the design assumptions of the function, which are,
that all of the linked tables are in *one* external database file).

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

Hi Jac

sPrev is explained as follows.

In theory, you can have tables that are linked to *many* external
databases. Indeed, every single table, could be linked to a different
external database.

However, when I designed this particular function, I designed it for
the case where *all* of the linked tables are in *one* external
database. That is a design constraint of this function.

To program the function, we need to loop through all the tabledef's,
until we find one that is linked. (You might have a mixture of linked
tables and local (unlinked) ones in the same database.) We check for a
linked table by looking at its CONNECT property.

Once we find the first linked table, we get the name of the external
database file, from the CONNECT property.

The question is, what to do then?

Basically, we have two options:
(1) *Assume* that any further linked tables will be in the same
external file, or
(2) *Check* that any further linked tables *are* in the same external
file.

I chose to do (2). For the first linked table that it finds, it gets
the name of the external file, and stores that name in sName. Then it
says: "Oh, I'll just remember that for later", and copies it to sPrev.
Then for each successive linked table, it gets the name of the external
file (in sName), and then says: "Um, is that the same name that we got
before"? It does that by comparing sName to sPrev.

It may help to "hand simulate" the code. Say you have 3 linked tables,
the first is linked to external database file "a.mdb", and the second
and third to external file "b.mdb". Then execute the code as if you
were the cenbtral processing unit! Write down the value of sName and
sPrev on each loop. You'll soon see how the code detects the case where
there is more than one external file.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

PS. Looking at my code again: it is quite correct, but, sPrev is a
little misleadingly named. That name suggests that it holds a "previous
value". But, to be really pedantic, it actually holds the "very first"
value. So "sFirst" would be a better name.

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Hi Tim,
That is fine. I understand what you do with that function now.
My problem is that I cannot connect to the two linked tables in my system
when I'm at home. So the function returns an error ("No linked tables"). I
will have to try it at the office.
Thanks for your comment.