Add field to LINKED table (1 Viewer)

I need to add on a field to a clients existing backend for a number of clients. I devised a small code in a new db to do this however since the tables are only linked to this new db, it is not permitting the ALTER TABLE statement. So how can I do it?
Thanks

Happy,

You're gonna have to change the table on the back-end.

Then modify the software on the front-end(s) to use the new field(s).

Wayne

Thanks for reply but I find it hard to believe it cannot be done!
What about creating another table with the same fields, add on some more fields, copy over all data from the original table and then delete original table and rename the new one to the name of the deleted one. What sort of code would that use?
Thanks!!!

Happy,

I find it hard to believe that there are apps out there that need this sort of
maintenance.

Personally, I don't see the need to change the structure of tables from what
is obviously an "end-user" interface.

To me, any design changes should be handled from the perspective of the
"back-end". I really wouldn't want to extend to the user software the
ability to change anything!

Just my two-cents,
Wayne

Happy,

Are you intending to send your users an update utility bundled in a separate .mdb that they run to add the extra field?

You don't say what the backend DB is.
Is it another .mdb or linked via ODBC or what?

The method of adding your extra field is depenedant on what the backend is.
If its an Access DB your code needs to open the backend DB and execute the ALTER TABLE query on that database. If its linked via ODBC a pass-through ALTER TABLE query may do the trick.

Hope this helps.

The following is an example from Help.

This example adds a Salary field with a data type of Currency to the Employees table.

Sub AlterTableX1()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

' Add the Salary field to the Employees table
' and make it a Currency data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"

dbs.Close

End Sub

Replace the reference to the Northwind.mdb with the targetted backend db, e.g. C:\myFolder\myBackendDb.mdb

Thanks Keith
Actually I've been working a whole afternoon on this problem. I devised the following
Import the table into current db
Do the additions using Alter Table sql
export back to the original db (it overwrites the original table with the same name)
I am happy to post my efforts if anyone is interested!
One problem I had was that if the original table is related in any way to another, as it inevitably is, one cannot overwrite it and so the export part will not work.
Your way however will probably take care of that - Thanks

And a footnote to wayne - with all due respect. Sometimes a programmer needs to alter a backend in order to improve a programme, and wants to do it remotely from another file!!!

Happy,

Glad to hear that all is well. I just still can't see why anyone programmatically
makes changes to database structures.

Again, happy to hear that things are working fine.

Wayne

Its to save yourself time when going around to all your clients who have the old backend. You just run your utility file which you have built and all neccessary changes are made whilst you sit back!
Anyway at least we got it going! Perseverance does pay off!
Thanks for your interest

Hey Happy, i know these posts start annoying you, but changing database structure from the front-end is not so highly recommended.
Anyway, if you want to update the db structure from the front end application you should use:

Dim db as Database
Set db=OpenDatabase(strDbName)
db.Execute "ALTER TABLE... "

as Keith wrote.
However, the db.execute doesn't provide a common solution since db.Execute proceeds Access Jet commands, that doesn't correspond the full list of standart sql commands (supported by ms sql server at least). For instance, you can not define cascades in relations, change primary keys, etc...
These issues are resolved with ADO or DAO technolgies.
For DAO, addition of columns works something like:

Dim db as DAO.Database
Dim tDef as DAO.TableDef
Set db=OpenDatabase(strDbName)
Set tDef=db.TableDefs(strTableName)
With tDef
.Fields.Append .CreateField(strFieldName, [dbDouble/dbLong/etc])
End With
Set tDef=Nothing
Set db=Nothing


In addition if you don't want to use constant strDbName (i.e different tables linked to different sources) you can find out it by:

Public Function GetLinkedDBName(TableName As String)
Dim db As DAO.Database, Ret
On Error GoTo DBNameErr
Set db = CurrentDb()
Ret = db.TableDefs(TableName).Connect
GetLinkedDBName = Right(Ret, Len(Ret) - (InStr(1, Ret, "DATABASE=") + 8))
Exit Function
DBNameErr:
GetLinkedDBName = 0
End Function


For this you need to add a refference to DAo 3.51/3.6 in your project
Good Luck Happy!

..almost forgotten, to see the results of the back-end update in the front-end you should relink the tables. It can be done from code by:
Public Sub reLink(strFileName As String)
Dim db As Database
Dim tDef As TableDef
Dim tDefs As TableDefs
Set db = CurrentDb
Set tDefs = db.TableDefs

For Each tDef In tDefs
If tDef.SourceTableName <> "" Then 'If the table source is other than a base table
tDef.Connect = ";DATABASE=" & strFileName 'Set the new source
tDef.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Sub

Many thanks for these last two posts! They certainly will be used in future to perform tasks on the backend remotely as I still desire to do!