Re: Updating backend db
- From: "crboone via AccessMonster.com" <u29280@uwe>
- Date: Sun, 19 Nov 2006 09:20:45 GMT
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.
Albert D. Kallal wrote:
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....
--
Message posted via http://www.accessmonster.com
.
- References:
- Updating backend db
- From: crboone via AccessMonster.com
- Re: Updating backend db
- From: Albert D. Kallal
- Updating backend db
- Prev by Date: Re: Updating backend db
- Next by Date: Re: Storing birthdate with year optional
- Previous by thread: Re: Updating backend db
- Next by thread: Re: Updating backend db
- Index(es):
Relevant Pages
|
