Update Back-end Database Tables

[box2003]

Hello,



I have a main Access database front-end (FE) linked to an Access DB back-end
(BE). I distribute the FE to 2 different DB BE's located in 2 different
locations. As I make changes to the FE, sometimes it is necessary to update
the DB BE to accommodate new FE features. Right now this is not a problem,
I merely request a zip file of the 2 DB BE's, make changes to BE table
fields, then send back the modified BE with new FE. Since the program may
soon be distributed to a wider audience, getting a zip file of the DB BE
from each location may not be the most efficient way to update an end users
BE. This application has no future plans to be a web based application and
remote access tools (PC Anywhere, VNC Server) are not a consideration from
the client standpoint.



Is there a way or method from within the Access DB FE I can create a utility
to add a field to a linked Access DB BE without having the DB BE sent to me
in a zip file, make the change, then send back? Is there a program
available that can perform this type of update?



Thanks for your assistance.

 

[Rick Brandt]

Hello,



I have a main Access database front-end (FE) linked to an Access DB
back-end (BE). I distribute the FE to 2 different DB BE's located in
2 different locations. As I make changes to the FE, sometimes it is
necessary to update the DB BE to accommodate new FE features. Right
now this is not a problem, I merely request a zip file of the 2 DB
BE's, make changes to BE table fields, then send back the modified BE
with new FE. Since the program may soon be distributed to a wider
audience, getting a zip file of the DB BE from each location may not
be the most efficient way to update an end users BE. This
application has no future plans to be a web based application and
remote access tools (PC Anywhere, VNC Server) are not a consideration
from the client standpoint.

Is there a way or method from within the Access DB FE I can create a
utility to add a field to a linked Access DB BE without having the DB
BE sent to me in a zip file, make the change, then send back? Is
there a program available that can perform this type of update?

My new front ends often have "one-time" code that runs the first time it is
launched that modifies or adds objects to the back end. I use DAO with
which I can add fields, change fields, add new tables and relationships,
etc.. Basically anything you can do in the GUI concerning table mods can be
done in code. Here in an example...

Sub ModifyTable()

' Modifies existing table in external database

On Error GoTo ErrHandler

Dim WrkJet As Workspace
Dim MyDB As Database
Dim MyTbl As TableDef
Dim MyFld As Field
Dim MyIndex As Index

Set WrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set MyDB = WrkJet.OpenDatabase("Path to back end MDB")
Set MyTbl = MyDB.TableDefs("testmod")

' Add a new field
Set MyFld = MyTbl.CreateField("TestField1", dbText, 20)
MyTbl.Fields.Append MyFld
MyTbl.Fields.Refresh

' Delete a field
MyTbl.Fields.Delete ("SalesMan")
MyTbl.Fields.Refresh

' Change a field's Name
MyTbl.Fields("DeliverTo").Name = "DelToChangeName"

' Cannot Change a field's DataType

MyDB.TableDefs.Refresh

Egress:
Set MyIndex = Nothing
Set MyFld = Nothing
Set MyDB = Nothing
Set WrkJet = Nothing
Exit Sub

ErrHandler:
MsgBox "Error Number " & Err.Number & vbCrLf & vbCrLf & Err.Description
Resume Egress

End Sub

 

[box2003]

Will it be necessary for me to break the links to the BE table before
running this code? It looks as if the changes can be made, then refresh the
table.

 

[box2003]

I just tested this out on a sample database I created, this works perfect.
Thanks so much.