Updating backend db

[crboone via AccessMonster.com]

I am pretty much a novice programmer in Access. However, I have developed a
fairly nice application in Access '97 (quite a few years ago) using the
Developer Tools with Access runtime, setup, etc. I utilized 2 manuals that I
had bought, plus incorporated some code, tips, etc. that I found available on
the internet in order for the application to do all the things I needed
(along with a few nice custom touches I found).

However, one thing I've not seen in detail anywhere is how to update an
existing backend database. For example, if my application has 10 users and
the backend db resides on a network drive, then I update the app with new
tables, fields, etc. in my development environment ... How do I incorporate
those changes in the user's database without overwriting all of their
existing data?

One other question is -- if the backend db doesn't have much code, is there
any point to creating an .mde from it? I have just enough code to keep people
out of it should they try to access it (along with a backdoor entry).

Thanks in advance for any assistance.

 

[Albert D. Kallal]

How do I incorporate

those changes in the user's database without overwriting all of their
existing data?

The normal approach is to simply have some code that runs after you re-link
code (you have some re-linnk code...rigth???)

So, the solution to the above is that any addition you make to the back end
table, it must be done via a code routine. So, as you develop the next
great update for your customers, if you need a new field, or new table
then you write code to add this new table, or field.

My front end code (after the linking part is done - you have this..right??)
then runs a routine called Upgrade1. So, every time you need a new
field, or table in the back end, you actually write the code to do this.
The code will check for a field,
(or table) and add it if it is missing. (the code works this way, since the
end user might have several back ends..and link to anyone of them.
Further, those several backbends might be a old backup, or whatever.
Thus, your design assumes
that the back end can be at any revision level, but your upgrade code will
ALWAYS update the mdb to the latest version if a table/field etc. is
missing...


The code you write to upgrade the back end is not really that hard.

However, for the most part, there is 3 types of operations


1) increase the length of a field. This happens a few times during
development
and after you deploy. My upgrade code for that is


' check size of Anotes in tblGroupRemind


Set db = CurrentDb
Set dbTable = db.TableDefs("tblGroupRemind")
Dim intSize As Integer
intSize = dbTable("ANotes").Size


Set dbTable = Nothing


If intSize = 50 Then


Dim nField As DAO.Field


Set db = OpenDatabase(strBackEnd)


db.Execute "ALTER TABLE tblGroupRemind ALTER COLUMN Anotes text(255)",
dbFailOnError


End If


As you can see, it is not hard, but is DOES take code!!! Some of my upgrade
routines for software that is 6 years old is 100's of lines of code of the
above type stuff....


Adding a field is not hard either....


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


Adding a whole table is easy also!!. All I do is place a "copy" of the table
in the front end. this table is small, and will NEVER be used in the front
end, but remains there to be *COPIED* to the back end table. I use a table
copy as it is MUCH less code then trying to use code to create a table, and
then one field at a time. IF the table have 5, or even 25 fields, the code
to "copy" the table from the front end to the back end is rather small....


So, new tables are created in the front end. When I send out the new front
end, the code check for the missing table, and then COPIES it from the front
end to the back end!!. Very easy, and not much code...


AddDefaultTable:


strFromDB = CurrentProject.FullName
strToDB = strBackEnd


DoCmd.TransferDatabase acExport, "Microsoft Access", strToDB, acTable,
"tblDefaultsC", "tblDefaults", False


Return


All of my new default tables in the front end have the table name + "C"
appended to the end (this is so the table name does not interfere with the
actually linked table name!!!


So, the above code is only 3, or 4 lines of code, and yet it copies (well,
creates) a whole new table in the back end....


So, the above sample code shows increasing a field length, adding new
fields, and adding new tables. These are the most common updates to the back
end.

One other question is -- if the backend db doesn't have much code, is
there
any point to creating an .mde from it? I have just enough code to keep
people
out of it should they try to access it (along with a backdoor entry).

It likely don't have any code. However, a few of my backbends do have

some special "update" code, or some code to "fix" data. However, for the
most

part, I just leave it as a mdb...no big deal on this one....

 

[crboone via AccessMonster.com]

Thanks Albert! That sounds like quite a bit of work, but it makes sense.
Unfortunately, I've never seen anyone address this and the manuals I
purchased didn't get into it either. I guess you can only learn so much from
manuals.

The only linking code I have checks for the links, and when the app needs to
be re-linked a pop-up form allows the user to enter the path to the backend.
Looks like I have quite a bit more work to do in order to properly update my
backend db's.

I had been wondering if there is a way to rename a table in the backend that
was newly updated in development, then import the updated table and map the
fields over to the new table so that the data could be transferred over to
that new table. But after reading your answer that probably isn't the best
way to go.

 

[Mika Oukka]

You will need to import - only the data - to the tables in the new backend
db from the old one.

Start by creating backup copies ready from both databases.
Then, during this operations make sure that users do not add/modify in the
BackEnd file. You can do this by renaming the backendfile.

The tables that do not have any changes made to fields or the changes do not
concerne fieldnames or additions you can just copy paste the data from old
database table to new one. For the other tables (or if you are not sure) I
would use more precice method:

Create a copy from the old file as "Old.mdb"
Rename all the tables in the Old.mdb for example with method
"Table1name_Old", "Table2name_Old", etc.
Empty data in new tables.
In the new file, use command File-Get External Data-Import-command to import
all the old tables to new database.

For each table that needs the data from old database :
*create a new query in the desing view
*To Query, add "Table1name_Old" as source table
*Get all fields from table to query grid
*From menu change query type to append query and choose table "Table1name"
(the new table) to append to.
*Now query shows the sourcefields and the append fields in the grid. If some
of the fieldnames have been changed, Access is not finding the target field,
you can then select the append field manually.
*Save the Query.
*Double click the query and it will append the data from old to new table.
Repeat until all the new tables have the correct data

Delete the old tables, compress the database and move it to place where the
original file existed.

For the other question: .mde file might be ok, but you will have to create
it every time as you do changes to code and you are not able to edit/run the
code directly. So you might wan to consider one of the following:

For the backend just create a form in the desing view that gives a serious
warning: "Changes made to this database might cause the whole database to
etc...". Then in the Tools-Startup..., choose Form to be displayed as your
warningform. So as the backend database is opened the message will appear.

If the code is in the modules, you also can just hide the module from it's
properties. Tools Options - View - Show hidden objects, will once again show
them. This will though just slow the eager users down.

Copy your backend as new file, name it for example "Administrator". Delete
the tables from the file and then link them from backend file (File-Get
External data). Then you should be able to delete the code from you original
backendfile and instead use it from you arministrator file. For this file
you can create a password or place it somewhere that only you have access
to.

Regards,

Mika Oukka
Mika Oukka Consulting

 

[Albert D. Kallal]

The tables that do not have any changes made to fields or the changes do

not concerne fieldnames or additions you can just copy paste the data from
old database table to new one. For the other tables (or if you are not
sure) I would use more precice method:

You are forgetting that the question is how to update the back end for
systems that ARE DEPLOYED IN THE FIELD. This means that
you can't cut and paste since your not on the machine, nor can
you expect that the end user even has a full version of ms-access.

If I send out a update to my many customers using my application,
I cannot assume they know how to follow such a long and
lengthily list of instructions.

For my customers to update their software, all they have to
do is the following:

http://www.kallal.ca/ridestutorialp/upgrade.html

My customers NEVER see the table designs.

So, if you read the question at hand, the issue is how to update your
customers systems in the field when you don't have *physical* access to the
back end. further, you note that the poster mentions the runtime.

Thus the "reason" for this question is how does one update the back end when
customers don't have the full version of ms-access, and worse, you as the
developer will not have access to the physical back end location. So, how
does one update those customers in the field??

further, having a customer copy/paste/import that data is recipe for making
a mess.

To be fair, sure, if you have access to the physical back end, then there is
little if any problem to be solved. In those cases, you simply write down
the changes you make to the back end. When you ready for the new update, you
simply look at the list....

eg:

Add new table tblNotes - forced related to tblCustoerms

Increase length of notes field - tblCustomer

etc. etc.


So, you just modify the back end using your "list" of changes you made. for
new tables, you simply import the table from the "test" back end to the
"production" back end (the link to this table is already in your new front
end!!!).

 

[crboone via AccessMonster.com]

Thank you both for both of these responses. Since I have not written code to
update the backend, I probably will need to update the backend manually. If
necessary I'll create a copy of the user's backend db and pull it up so that
I can update it then copy it back down.

Would the query scenario work better than keeping a list? I'm just thinking
it would be easy to forget something if I'm just keeping a list (you know how
you can go from one thing to the next, or back out of a specific change, then
forget to notate the change). Granted -- for copying over new tables it's a
no- brainer, but for making significant changes to other tables it could be
easy to forget what you've done or undone.

 

[Albert D. Kallal]

Would the query scenario work better than keeping a list?

Hum, why create a query? I assume we are talking about the
case WHEN you have physical access to the back end.

Making a bunch of queries and append queries is really
ripe for you making mistakes here.

Why not just open and modify the back end. If you have few new
tables to place in the back end, and you need to move them
from your "test/developer" back end to the production
back end, why would you need a query? Why not just
go file->get external data after opening the production back end?
(you simply import that new table)

I am at quite a loss as to why a query is needed in this case???

I'm just thinking
it would be easy to forget something if I'm just keeping a list (you know
how
you can go from one thing to the next, or back out of a specific change,
then
forget to notate the change). Granted -- for copying over new tables it's
a
no- brainer, but for making significant changes to other tables it could
be
easy to forget what you've done or undone.

It sure is easy to forget..yes!!!
And, of course if you do have access to the production back end, then
you can tell everyone to go home for the day, or simply schedule a small
amount of downtime (often, just a few minutes is enough time for the
update). So, sure, it is a preferred approach to just modify the back end
(certainly MUCH less work then coding updates as I am suggesting).

So, sure, I suppose using some queries is another approach (we
always have many choices here). However, just opening up
the back end..and making the modifying seems like the most
easy approach here.

it could be
easy to forget what you've done or undone.

Yes, you do have to be careful. On the other hand, if you use code
for the update, then it not really possible to forget. The code approach
really is ONLY a recommendation from me when you WILL NOT have
direct physical access to the back end.

 

[crboone via AccessMonster.com]

Indeed, thank you. Writing code is certainly the best, most complete solution,
so I'll need to work on implementing that. It will especially be
necessary/convenient if the number of licensed institutions using the app
grows. Imagine trying to manually update a large number of backend db's for a
list of organizations using the program. I suppose it could be done, but it
would become unwieldy.

Would the query scenario work better than keeping a list?

Hum, why create a query? I assume we are talking about the
case WHEN you have physical access to the back end.

Making a bunch of queries and append queries is really
ripe for you making mistakes here.

Why not just open and modify the back end. If you have few new
tables to place in the back end, and you need to move them
from your "test/developer" back end to the production
back end, why would you need a query? Why not just
go file->get external data after opening the production back end?
(you simply import that new table)

I am at quite a loss as to why a query is needed in this case???

I'm just thinking
it would be easy to forget something if I'm just keeping a list (you know

[quoted text clipped - 6 lines]

be
easy to forget what you've done or undone.

It sure is easy to forget..yes!!!
And, of course if you do have access to the production back end, then
you can tell everyone to go home for the day, or simply schedule a small
amount of downtime (often, just a few minutes is enough time for the
update). So, sure, it is a preferred approach to just modify the back end
(certainly MUCH less work then coding updates as I am suggesting).

So, sure, I suppose using some queries is another approach (we
always have many choices here). However, just opening up
the back end..and making the modifying seems like the most
easy approach here.

it could be
easy to forget what you've done or undone.

Yes, you do have to be careful. On the other hand, if you use code
for the update, then it not really possible to forget. The code approach
really is ONLY a recommendation from me when you WILL NOT have
direct physical access to the back end.