Front end update backend

[Msam137]

I recently split my database so that multiple people can work on it. Or
not a problem but a little confirmation, if I or another user who is a
power user creates a new table on the font end side or create a new
query is there a way to make sure that any new tables that a user
create on the front end be updated to the back end database.

 

[Tom]

As I understand your post this is purely an application design matter. The
application must make sure the back end data is updated properly. Access has
no way of ensuring a table at the front end is created at back end.

 

[Albert D.Kallal]

no, have to create the tables in the back end.

Your code, quires etc of course goes in the new front end.

Remember, EACH workstation has THEIR OWN copy of the front end.

You are free to write new code, add new features, add new reports. You of
course are working on the next great release of your software, and you can
do this while all users are happy and working away.

The above is one of the great reasons why you split. (you can' t possibly
work on a airplane, or software while users are working!!).

However, if you need to add a new table, then you do have to kick everyone
out, and add this table to the back end. Often, I will work on a copy of the
back end, and create the new table. Then add my new forms, code etc. After I
done my testing, and am satisfied that it works well, then I will copy this
table from the test back end to the production back end. I then of course
re-deploy the front end (usually a mde) to all users at that point.

So, most of the time, you are free to develop, but when you make changes to
the table structures, then you do have to kick out all users. As mentioned,
often if I am just adding a new field to a table, I will do this on the test
back end, and simply have a word document of the changes I made

-- add new field CustNotes to tblcustomer
-- add new field InvoiceDate to tblInvoice

So, I make a nice little sheet of the changes I make. When it is time to
deploy the new application, then I re-link my front end to the production
back end, add the above fields to the table, and then re-deploy the front
end..and I am good to go.

So, if you do make changes to table structures on a copy of the back end,
you need to keep track of this.

At least with a split system, you can work on a copy of everything. Without
a split system, when you work, everyone gets a holiday, and can' t use your
software (not a very productive way to work...is it?).

 

[Msam137]

Well for me it is not much of a developing software I just modify and
create a lot of tables and I wanted to be able to give people easy
access to the new tables that I add, I know that if they relink the
database they will get the updates but the new tables is what I would
like them to be able to receive.

From my understanding the only way to do this is too make the changes

in the back end and then redistribute the front end with the updates?

 

[Albert D.Kallal]

From my understanding the only way to do this is too make the changes

in the back end and then redistribute the front end with the updates?

yes, the above is the normal approach here.

I have in the past re-distributed a new front end that would check the back
end, and make modifications to it. So, you might put a few tables in a new
front end, and when it runs, it can check the back end for these tables, and
if the don't exist..then you copy the tables from the front end to the back
end (or, use code in the front end to add the new table, or even sql ddl
statements such as alter table to add fields). So, you can programmatically
put tables and code in the new front end that will modify, and add tables to
the back end, but it takes quite of bit of coding skill to do this, and if
you can just modify the back end, and re-deploy the front end it is WAY less
work.

However, for remote users,a nd people that I will not have the ability to
touch, or have the back end sent to me, I most certainly do write code to
update that back end.

Of course over time as you add new tables to the back end, you must go to
the front end and add that table link.

Adding linked tables will occur quite often, and will over time become 2nd
nature to you.

So, after you add the new table to the back end, you then simply in the
front end (table design), add a new linked table. You then re-distribute the
front end to each pc.

Note that as your application gets more mature, adding of tables will be a
very RARE event.

 

[Msam137]

Ok, I understand what you are saying the problem almost every time i go
into the database I have to add a new table that is the only reason but
I do not want to be messing with the main database a lot. What if I
copy all my new tables on to the back end create a new front on the
server that is housing the back end and then redistribute the front end
as a shortcut to all the users who need to access it. So we all will be
using the same front end.

 

[Douglas J Steele]

That's a very bad idea. Each user should have his/her own copy of the
front-end, ideally on the hard drive of their workstation. Sharing a
front-end significantly increases the risk of database corruption.

 

[Albert D.Kallal]

Ok, I understand what you are saying the problem almost every time i go
into the database I have to add a new table

no, what I was saying is something is seriously wrong with your application
and data designs if you have to be adding new tables all the time.

Sure, at the very start of the development process you likely will be adding
tables quite frequently (in fact, so much so, that you should NOT split).
However, as you complete your design, and start allowing users to work on
the application, then adding tables will OCCUR VERY rarely.

And, if you have to add new tables frequently, then your design is wrong,
and not normalized. So, for those rare (or less frequent) times when you
have to add a table, you simply add the table to the back end, add a table
link to your front end.You then simply re-deploy the front end to each
computer. This is no different when you need to deploy the next great
version, or simply some bug fixes.

I do not want to be messing with the main database a lot.

Ah, good, so in your case you have reached the point where it is going to be
RARE to add new tables.

What if I
copy all my new tables on to the back end create a new front on the
server that is housing the back end and then redistribute the front end
as a shortcut to all the users who need to access it. So we all will be
using the same front end.

No, you can NOT use shortcuts to the front end on the server. MOST of the
reason why we split is to place the front end on EACH computer. That was
most of the whole idea here. That way, the application part does not have
to travel to each workstation.

Further, the application part MUST be placed on EACH computer for reliable
operation. I will say this again:

You put the application part on EACH workstation.

For reliably operations of ms-access, each workstation must get their OWN
copy of the application part (front end).

Most of us developers usually create some means to automatically update the
front ends on each computer. (I wrote a few lines of code to check a version
number I have in a table in the front end,a nd also in a table in the back
end. If they don't match, then I copy the new back end from the server.

You can also use the free front end updater here if you don't want to roll
your own updater.

http://www.granite.ab.ca/access/autofe.htm

 

[Msam137]

I just would like to say thank you for everybody help and input.
Unfortantly I think the design of the database is a little construed to
the point that it would take some time to make sure everything is ok. I
am currently working a way to fix the design with out disabling access
and with out trying to redo the whole entire database. But I just
wanted to say thank you for everything,