Linked table

[Diogo]

Hello everyone,
I'm back with some new doubts that I hope someone can shed some light.

I created an Access DB for a client, divided it in FE and BE, created a
Pilot DB that on startup checks to see if there is a newer version of the FE
and updates it via intranet when client starts it.
I have several clients FE writing to one BE.
At the time I set up this configuration because if there were any needs for
updates I would just make the updates in the FE and send them via e-mail to
the client, which would then place the update in a share for every other user
to update automatically when they stared up theirs FE.
This was all working fine but lately the client has been asking for a lot of
new use cases that implicate the creation of new tables in the BE, which
means that I have to go personally to the client, create the new tables,
updating the new linked tables in the new FE and copying the FE to the server
for the other users to be able to update their own versions.
The client is self contained so I can’t access it from the outside so I
could update the linked tables or create the new ones. Only communication is
via e-mail. No VPN, no nothing.
What I need is:
1. A way to create some kind of program that would create the new tables
automatically in the client BE.
2. Update the linked tables in the new FE. Is there a way to manually
specify the way to a linked table??? Access allows this but I have to be able
to access the mdb file. Can I just type the address?

Sorry for all the talk, and hope to hear from someone soon.

Best regards.

 

[Diogo]

Update on this subject:

I'm now trying the following:

"Dynamic Path Assignment
One way to dynamically assign the connect string is to store the path for
each linked table in a local table list of links. This has the singular
disadvantage of having to be updated manually, unlike the TableDefs
collection whose membership is maintained by Microsoft Access. If you forget
to add a new table to your list of linked tables, it will be excluded from
your LinkTables routine and may cause you some embarrassment when your new
app is rolled out. Can you say, "It worked fine on my machine?"

Public Function RelinkByList() As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblLinkedTables", dbOpenSnapshot)

' Loop through the recordset, processing rows
Do Until rst.EOF
Set tdf = dbs.TableDefs(rst!TableName)
tdf.Connect = rst!DataFilePath
tdf.RefreshLink
rst.MoveNext
Loop

Set tdf = Nothing
Set rst = Nothing
Set dbs = Nothing
End Function
For this code to work there must be a local table named tblLinkedTables that
has at least two fields: TableName and DataFilePath and, as mentioned above,
this table must include a row for each table you wish to process.
"

I copyied the code and created the appropriate tables and activated the
appropriated references DAO, etc, etc...
But I get the following error:

"Run-time error 3170: Could not find installable ISAM "

Could someone help?

Thanks

 

[Douglas J. Steele]

What's in rst!DataFilePath?

If you're trying to link to another Jet database, it needs to be something
like:

;DATABASE=E:\Folder\File.mdb

If it's some other data source, additional information needs to be included
for the Connect property.

 

[Peter Hibbs]

Diogo,

Have a look at the Back End Update Utility at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4
which does just that.

HTH

Peter Hibbs.