Adding tables / fields to a remote backend database
Kiers
i have an application (Access 2007), split into FE and BE which is now
being used by several clients. I now have reason to add new
functionality.
The additional functionality requires new tables and new fields adding
to existing tables. I understand that i will need to write SQL code to
do the work.
The piece I am unsure about is how i execute the code on the customer
back end database.
The options I have considered so far are;
1) develop a specfic front-end that just runs the SQL on start up that
is only ever executed once - effectively a patch process ?
2) put the SQL code in the new application front-end which would have
to execute every time the app started (not sure i want to do that -
seems rather inefficient)
any advice on how i should proceed would greatly aprpeciated
Rgds
Kiers
Keith Wilby
news:9fe3d941-e30d-4e20...@r5g2000yqb.googlegroups.com...
I presume by SQL you mean VBA. The only problem I can see with that
approach is that all users need to be out of the BE so you'd need to handle
that error and abort if the file is in use. Another approach might be to
deploy a new BE file and then run some queries to import the data into the
new file.
Keith.
Albert D. Kallal
To add tables, there is SO MANY settings like defaults etc, that I do NOT
try to use sql ddl commands to create the table. What I do is check for the
table in the back end, and then go:
strFromDB = CurrentProject.FullName
strToDB = strBackEnd
DoCmd.TransferDatabase acExport, "Microsoft Access", _
strToDB, acTable, _
"tblActionTypesC", "tblActionTypes", False
So, I actually place a copy of the table in the front end (with a "C"
appended to the name). If the check for the back end table fails, then the
above code is run and simply copies the table from the front end to the back
end. (note that the front end already has a link to this non existing
table..and that saves me have to write code to setup a table link).
For adding new fields, I simply go:
Dim nF As DAO.field
Dim nT As DAO.TableDef
Dim nR As DAO.Relation
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set rst = CurrentDb.OpenRecordset("Locations")
On Error goto next
strTemp = rst!Capacity.Name
if err.number <> 0 then
rst.Close
strToDB = strBackEnd
Set db = OpenDatabase(strToDB)
Set nT = db.TableDefs("Locations")
nT.Fields.Append nT.CreateField("Capacity", dbLong)
nT.Fields.Refresh
db.Close
Set db = Nothing
else
rst.Close
end if
In the above, I check for a column existing, and if not, then I write code
as above. What this means is that during your development process you NEVER
add a new column by hand, but go into your "upgrade" routine, and use code
to add the column. You then run that code and then get back to work....
>
> The options I have considered so far are;
>
> 1) develop a specfic front-end that just runs the SQL on start up that
> is only ever executed once - effectively a patch process ?
That quite much what I do. Remember, it best to "check" for missing tables
since they might restore some older backup that NOT been updated. So, make
your code check + update the back end, and thus if they attempted to use
some older back end, then all of your update code (which might have code to
update the last 5 versions you sent out over the years, then you still be
ok).
>
> 2) put the SQL code in the new application front-end which would have
> to execute every time the app started (not sure i want to do that -
> seems rather inefficient)
As mentioned, step #1 approach is better since no additional instructions
are required by the customer...they just run the update and they are good to
go. in fact, my table update routines don't even tell the customer a update
is occurring anymore..and frankly they don't care.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>The additional functionality requires new tables and new fields adding
>to existing tables. I understand that i will need to write SQL code to
>do the work.
No, I use DAO tabledefs and such. I also use Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEMscreens/CompareEM-About.htm
which creates VBA code containing the differences between two MDBs
>2) put the SQL code in the new application front-end which would have
>to execute every time the app started (not sure i want to do that -
>seems rather inefficient)
I put a version number table and field in the FE and the BE. Then, on startup I
compare the version numbers. If required I execute code appropriate to what version
the backend is in. So if the user is down three versions the code executes each of
the three versions in turn.
Along with any delete/insert queries as required to normalize data.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
Kiers
> Kiers <kie...@leasemanager.co.uk> wrote:
> >The additional functionality requires new tables and new fields adding
> >to existing tables. I understand that i will need to write SQL code to
> >do the work.
>
> which creates VBA code containing the differences between two MDBs
>
> >2) put the SQL code in the new application front-end which would have
> >to execute every time the app started (not sure i want to do that -
> >seems rather inefficient)
>
> I put a version number table and field in the FE and the BE. Then, on startup I
> compare the version numbers. If required I execute code appropriate to what version
> the backend is in. So if the user is down three versions the code executes each of
> the three versions in turn.
>
> Along with any delete/insert queries as required to normalize data.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
> For a free, convenient utility to keep your users FEs and other files
> updated seehttp://www.autofeupdater.com/
> Granite Fleet Managerhttp://www.granitefleet.com/
Hi,
Thanks for the responses - very helpful.
on the question of adding fields, i cannot find a way of setting all
the parameters assigned to the field i wish to add to the existing
table; a check of the tabledefs of the original field produces the
following list, but i can see no way of ensuring the new field gets
the same settings. Will the TableDefs procedure do the job?
i did use the Compare'Em database, but this did not apply all the
parameters either.
any pointers as to how i can replicate the field definitition
especially the row source, would be appreciated.
Properties of AcceptCustToldBy; nr:42 True
nr:0, Value (no value) ;Type=4
nr:1, Attributes = 1;Type=4
nr:2, CollatingOrder = 1033;Type=3
nr:3, Type = 4;Type=3
nr:4, Name = AcceptCustToldBy;Type=12
nr:5, OrdinalPosition = 50;Type=3
nr:6, Size = 4;Type=4
nr:7, SourceField = AcceptCustToldBy;Type=12
nr:8, SourceTable = tblOrders;Type=12
nr:9, ValidateOnSet (no value) ;Type=1
nr:10, DataUpdatable = False;Type=1
nr:11, ForeignName (no value) ;Type=12
nr:12, DefaultValue = ;Type=12
nr:13, ValidationRule = ;Type=12
nr:14, ValidationText = ;Type=12
nr:15, Required = False;Type=1
nr:16, AllowZeroLength = False;Type=1
nr:17, AppendOnly = False;Type=1
nr:21, GUID = ??????c?;Type=9
nr:22, ColumnWidth = 2085;Type=3
nr:23, ColumnHidden = False;Type=1
nr:24, AggregateType = -1;Type=4
nr:25, ColumnOrder = 0;Type=3
nr:26, Description = Who told the customer?;Type=10
nr:27, DecimalPlaces = 255;Type=2
nr:28, DisplayControl = 111;Type=3
nr:29, RowSourceType = Table/Query;Type=10
nr:30, RowSource = SELECT [tblSalespersons].[ID], [tblSalespersons].
[Forename], [tblSalespersons].[Surname] FROM [tblSalespersons] ORDER
BY [Surname];;Type=12
nr:31, BoundColumn = 1;Type=3
nr:32, ColumnCount = 3;Type=3
nr:33, ColumnHeads = False;Type=1
nr:34, ColumnWidths = 1441;0;0;Type=10
nr:35, ListRows = 16;Type=3
nr:36, ListWidth = 2880twip;Type=10
nr:37, LimitToList = True;Type=1
nr:38, AllowMultipleValues = False;Type=1
nr:39, TextAlign = 0;Type=2
nr:40, AllowValueListEdits = True;Type=1
nr:41, ShowOnlyRowSourceValues = False;Type=1
Chuck Grimsby
Admittedly, I haven't done this myself in a rather long time, but what
I did was to have a bit of code in the main form that checked the
database properties of the backend database, where I'd set a "version
number". If the version number was lower then the one in the code,
then the "update database" code would run. Step 1 of which was to
make a backup copy of the backend! Do Not omit that step!
I remember on various times I use either VBA code to change the tables
or SQL statements (ALTER TABLE....). I don't remember why I'd choose
one over the other, though there are things you can do with DAO
statements that just aren't available in SQL.
Once the code completed OK (and the back-end's version property was
updated) the user was given the option of deleting the backup copy and
off they could go.
Do remember that your users are going to do things you might not
expect at first pass, such as "recovering" a back-end database several
versions back, or trying to use a "old" front-end on a "newer" back-
end. You can't guess in advance *all* of what they'll do, but do your
best to prevent them from injuring their data too much, and your
reputation in the process!
John von Colditz
The easy and quick way to do it is develop a simple mdb that either you
or the customer can execute. All it will do is open, open the back end,
transfer the tables you need to the existing back end and close. If
your new front end is attaching to those tables, you are done. I
usually VPN into the client site, and do it myself in the middle of the
night.
Tony Toews [MVP]
>on the question of adding fields, i cannot find a way of setting all
>the parameters assigned to the field i wish to add to the existing
>table; a check of the tabledefs of the original field produces the
>following list, but i can see no way of ensuring the new field gets
>the same settings. Will the TableDefs procedure do the job?
>
>i did use the Compare'Em database, but this did not apply all the
>parameters either.
>
>any pointers as to how i can replicate the field definitition
>especially the row source, would be appreciated.
In my opinion, and I've told the auther of the Compare 'Em utility, he should be
*exactly* duplicating *all* the properties on the new table. I don't recall his
response but he didn't feel the need. <sigh> I would suggest you also email him and
maybe he'll fix that up.
OTOH maybe I should just write the utility myself and be done with it.
Now you can add properties yourself. So just update the code the utility produces to
do whatever it is you want to do.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
Tony Toews [MVP]
>The easy and quick way to do it is develop a simple mdb that either you
>or the customer can execute. All it will do is open, open the back end,
> transfer the tables you need to the existing back end and close. If
>your new front end is attaching to those tables, you are done.
Trouble is what about added fields and indexes to exisitng tables and relationships
between the new table and existing tables?
Kiers
> John von Colditz <johnv...@earthlink.net> wrote:
>
> >The easy and quick way to do it is develop a simple mdb that either you
> >or the customer can execute. All it will do is open, open the back end,
> > transfer the tables you need to the existing back end and close. If
> >your new front end is attaching to those tables, you are done.
>
> Trouble is what about added fields and indexes to exisitng tables and relationships
> between the new table and existing tables?
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
> For a free, convenient utility to keep your users FEs and other files
Hi,
i wrote to the author of Compaer'Em back in March 2009 on a different
matter (the fileopendialog filter is restricted to *mdb and *mde and i
wanted an update to include *accdb), he responded by saying that he
longer supports it or indeed uses Access.
Rgds
Kiers
Tony Toews [MVP]
>i wrote to the author of Compaer'Em back in March 2009 on a different
>matter (the fileopendialog filter is restricted to *mdb and *mde and i
>wanted an update to include *accdb), he responded by saying that he
>longer supports it or indeed uses Access.
Cr*p. Hmm, now what would be nice then if he put it onto an Open Source site.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files