AutoLinking tables at startup

[QC]

Hello All,

i know this question has come up alot, and i have read the forumns and
looked at the sample code provided, but i have a slightly different request
that im hoping some more experienced users can help solve.

At startup, Id like to checks for valid links, if present, simply continue
to main form, else ask user for directory where all tables are located and
relink them, then goto main form

Assume the following:
1. There are multiple backend mdb's (each containing some of the tables)
2. All the mdb's are always in the same directory.

All of the solutions i have seen seem to mimic Access's built in table
manager in that they make you select each table for each link...since all
mine are in the same directory, isnt there a way to use the selected
directory as a constant in the relink process?

Thanks
The Dev A code seems to want to make the end user choose each mdb for each
table (eccchhh!)

 

[Eric Heinold]

Hello All,

i know this question has come up alot, and i have read the forumns and
looked at the sample code provided, but i have a slightly different
request
that im hoping some more experienced users can help solve.

At startup, Id like to checks for valid links, if present, simply continue
to main form, else ask user for directory where all tables are located and
relink them, then goto main form

Assume the following:
1. There are multiple backend mdb's (each containing some of the tables)
2. All the mdb's are always in the same directory.

Here's how we do it in our database:
We have a table in the front end that contains the path to each mdb that we
link tables to. (We multiple databases in different directories, if all
your back end are in the same directory, I suppose you could just store the
path, then append each database you need, as you need to link to them.)

Then in another table, we have a list of tables in each database that need
to be linked. When starting up, we test the link to on table in each
database to make sure they are still valid. If so, then we simply start,
otherwise we ask for the path to the database in question and relink each
table necessary for that database.

Here's the table for tables to link (tablenametable):
Code, tableName
AA,tblTable1
AA,tblTable2
BB,tblTest1
BB, tblTest32
In our paths table would be:
code, database
AA,C:\temp\test.mdb
BB, C:\temp\data\test1.mdb

Then we call a function with the Code from the table which links all the
tables in the table with that code.
Here is the part of the code we use - the targetdb is the CurrentDB() for
the example:

With rsAttachTables
.MoveFirst
Do Until .EOF

On Error GoTo ReattachTablesError

TargetDB.TableDefs.Delete AttachTables!TableName & EndChar
Set CurrTable = TargetDB.CreateTableDef(AttachTables!TableName &
EndChar)
CurrTable.Connect = ";DATABASE=" & Path
CurrTable.SourceTableName = AttachTables!TableName
TargetDB.TableDefs.Append CurrTable
Err = 0
CurrTable.RefreshLink

End If

If (Err <> 0) Then 'We have a problem
If EchoOn Then
SysMsg "Could not locate table " & !TableName & " in " &
Path, 1, gAppName
End If
.Close
GoTo Exit_Badlink
End If

On Error GoTo ReattachTablesError
.MoveNext
Count = Count + 1
ret = SysCmd(SYSCMD_UPDATEMETER, Count)
Loop

The function would have 1 parameter: Code
Whatever code was sent, we would first make sure it was a valid db from the
path table, then open the recordset rsAttachtable using SELECT * FROM
tablenametable WHERE Code = 'AA' (or whatever code), the loop through the
records linking each table.

There is some error checking. You would need to make sure the tables to not
exist already, since this will cause an error when you try to append to
TableDefs

All of the solutions i have seen seem to mimic Access's built in table
manager in that they make you select each table for each link...since all
mine are in the same directory, isnt there a way to use the selected
directory as a constant in the relink process?

Thanks
The Dev A code seems to want to make the end user choose each mdb for each
table (eccchhh!)

Hope that helps

 

[Tim Ferguson]

I don't think the algorithm is very hard, but I don't think you have
defined your needs:

At startup, Id like to checks for valid links, if present, simply
continue to main form,

For Each tdf in myDB.TableDefs
targetMDB = GetMDBPathFromConnectString(tdf)
if len(dir(targetMDB))=0 then
LinkIsOkay = False

Else
set db = dbengine.opendatabase(targetMDB, false, false)
if Not TableExists(db, tdf.Name) then
LinkIsOkay = False
end if
db.close

end if
Next tdf

if not LinkisOkay then

else ask user for directory where all tables
are located and relink them, then goto main form

All in one go, or one by one? What if some of the tdf links are valid and
others not? Go for a majority vote? Default the okay ones (you'll need to
remember which is which in the loop above) or get new ones from the user?
What about file names -- if you have a folder of MDBs, how will the
program know which file contains which tables? Why aren't they all in one
file anyway? What if the fields have changed in the target mdb... should
you be checking a Version property, or that the entire Field list is as
expected?

Just a thought...

All the best


Tim F

 

[QC]

Whew...

Addressing tim's notes, i guess you could say the following...

#1: The tables are in multiple mdbs for size sake, and recoverability. I
dont want multiple users accessing the same mdb all the time, when they only
need certain tables....otherwise it grows very large very fast, and if it
gets corrupted then all the tables are unavailable..... its a strategy ive
used with alot of success in the past...

#2: Although all your points are valid, they are beyond this particular
problem:
the tables (and mdb's) will ALWAYS be in ONE directory when installed....
(c:\data) i was thinking that if i put it on a network path or a mapped
drive (that's the same for everyone in that particular location, lets say
Z:\data or \\sharedfiles\data ) then relinking will be easier for all those
front ends out there that need access to the source if they could be told
that the c:\data location was invalid and they needed to specify a new
directory where all the data is......

and it would be less of a management headache for the local 'guru' then
manually relinking approx 45 tables across multiple mdb's...

there are several link managing apps on the utteraccess.com boards that let
you relink all the tables from a single mdb at once (as a group), but none
that i found that let you specify just the path and relinked on that
constant....

Thanks for the reply....

 

[Tim Ferguson]

there are several link managing apps on the utteraccess.com boards
that let you relink all the tables from a single mdb at once (as a
group), but none that i found that let you specify just the path and
relinked on that constant....

None of this is particularly difficult: it's just writing a .Connect string
and calling the .Refresh method. Your hardest job is deciding exactly how
you want to do the thing, what assumptions you can make about the files and
tables that are available, and so on.

What do you want from this group --? If you want someone to write the whole
sub for you, then you'll have to answer all the questions above; on the
other hand, if the hints above are enough to get you going them you'll have
to answer them for yourself anyway.

Best wishes


Tim F