Updating linked backend I haven't got access to

[Mark]

Hi All,
I created a database for a friend a few months ago which has been
working fine. However, he now wants a few changes implemented to accommodate
his growing needs. When I gave him the database, I split it in anticipation
of this so that I could give him a replacement fromtend without affecting
his existing data.

My problem is that one of the changes he has asked for requires one of the
tables altering. My plan was to run code the first time he opens the new
frontend which would add the required fields but I found that this cannot be
done as the tables are linked. Can anyone offer any idea on how I could
achieve this as getting to his location is somewhat of a problem!!!!!
TIA,
Mark

[Ron2005]

Look up in the Macro area

deleteobject - Table

Transferdatabase - link

That can do it from within the normal running app - however that does
not of itself allow changes to the table structure etc. but will unlink
it and allow you to link a different table in its place.

It would seem you need to get the new definition into the tables DB
then get the old data transferred from the old table into the new
table, then delete the old link and the new table link and recreate the
table link with the new BE table. Very last step not necessary if the
new table was created with a different link name (unlikely since you
would have to have changed EVERY query within the system.)

At some point, after everything seems to be working for a while, you
may want to re-establish a link to the old table and empty it since you
cannot really delete it from the FE (I believe).

[Lyle Fairfield]

>From your front end create and open a new access application. Open the
backend db with this. Run any code you want. Provided you don't have
something going that prevents the backend db from being opened
exclusively something like this should work.

Sub aircode()
Dim a As Access.Application
Set a = New Access.Application
With a
.OpenCurrentDatabase "northwind.mdb"
.DBEngine(0)(0).Execute "ALTER TABLE Employees ADD Whatever
CHAR(255)"
.CloseCurrentDatabase
.Quit
End With
Set a = Nothing
End Sub

[Br]

Mark wrote:

Hi All,
I created a database for a friend a few months ago which has been
working fine. However, he now wants a few changes implemented to
accommodate his growing needs. When I gave him the database, I split
it in anticipation of this so that I could give him a replacement
fromtend without affecting his existing data.

My problem is that one of the changes he has asked for requires one
of the tables altering. My plan was to run code the first time he
opens the new frontend which would add the required fields but I
found that this cannot be done as the tables are linked. Can anyone
offer any idea on how I could achieve this as getting to his location
is somewhat of a problem!!!!!

Simple. Your code should reference the backend database. You can determine
the backend's name/location from the link propertis of oen of the linked
tables. You then reference that database and it's table collection.

eg.

Instead of..

Set MyDb = CurrentDB()

use..

strBackendDBName = GetDataLocation()
Set MyDb = OpenDatabase(strBackendDBName)
Function GetDataLocation() As String
On Error Resume Next
Dim myDB As Database, tdef As TableDef, ConnectString As String
Set myDB = CurrentDb
Set tdef = myDB.TableDefs("tblPeople")
ConnectString = tdef.Connect
GetDataLocation = right(ConnectString, Len(ConnectString) - 10)
End Function
--
regards,

Br@dley

[Mark]

Thankyou all for your suggestions. Easy when you know how ;o)

"Br@dley" <do***********@google.com> wrote in message
news:e1**********@news-02.connect.com.au...

Mark wrote:

Hi All,
I created a database for a friend a few months ago which has been
working fine. However, he now wants a few changes implemented to
accommodate his growing needs. When I gave him the database, I split
it in anticipation of this so that I could give him a replacement
fromtend without affecting his existing data.

My problem is that one of the changes he has asked for requires one
of the tables altering. My plan was to run code the first time he
opens the new frontend which would add the required fields but I
found that this cannot be done as the tables are linked. Can anyone
offer any idea on how I could achieve this as getting to his location
is somewhat of a problem!!!!!

Simple. Your code should reference the backend database. You can determine
the backend's name/location from the link propertis of oen of the linked
tables. You then reference that database and it's table collection.

eg.

Instead of..

Set MyDb = CurrentDB()

use..

strBackendDBName = GetDataLocation()
Set MyDb = OpenDatabase(strBackendDBName)
Function GetDataLocation() As String
On Error Resume Next
Dim myDB As Database, tdef As TableDef, ConnectString As String
Set myDB = CurrentDb
Set tdef = myDB.TableDefs("tblPeople")
ConnectString = tdef.Connect
GetDataLocation = right(ConnectString, Len(ConnectString) - 10)
End Function
--
regards,

Br@dley