How to work on home laptop, then update client's Front End?

[lorirobn]

Hello,
I am in the process of bringing my Access application to client's
server, and need some help! The background is: I created application
(mdb) using Access 2003 on my laptop, converted it to Access 2000
(client uses both 2000 and 2003), split it into Front End and Back End,
and IT person brought Front End onto one test person's computer, and
brought Back End onto MS-SQL Server 2000. Also brought copy of Front
End on the server, as a backup. When he copied front end and back end
from my laptop, he had to do some work to change the linked tables'
addresses, to point them to the server.

I have many questions, but I'll start with this one: what is the
procedure to follow so that I can still work on my home laptop and make
changes, and then update the Front End application on this one test
computer (obviously there will be more users but I am starting simple)?
The IT person walked me through this once, but it seemed convaluted
and I'm sure there is a better way. He said if I copied my updated
..mdb to the workstation and overlaid the original, I would have to
re-link all the tables. To avoid this, he had me open the existing
database and do a file---->import of all the objects from my updated
version. This was a bear, as Access renamed the new objects (by
appending a '1' to their names), so I had to delete all original
objects, and rename the updated objects. This was very prone to
mistakes, and did not sit well with me. From reading posts, I know
there is a way to automate updating the application from the server,
but I haven't read anything about linked tables. On my laptop, I have
Front End and Back End linked via 'linked tables', but because the
client's linked tables are on the Server and so are different than
mine, what procedure do I follow from bringing updates from my laptop
to the client?

Should I be working on my laptop via MSDE, or something that mimics a
server?

I am sorry if this question is repeating what's already out there; I
saw a lot of postings on how to automate the update process, which were
really helpful, but they didn't mention linked tables and I suspect
that changes things. Also, I haven't hooked up my laptop to the server
yet.

Thanks in advance for ANY help!!!
Lori, former COBOL programmer and new to MS-SQL Server...

 

[Douglas J. Steele]

The Linked Table Manager allows you to quickly relink all tables.

Alternatively, put a VBA module in to let you relink. There's an example at
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web"

 

[Norman Yuan]

The problem I see is not how to bring your app (BE and FE) to the client
easily. The problem is you develop a JET DB app for client's SQL Server. The
two database engine are different enough to treat them differently. It is
very rare you develop something for JET and copy/import eveything into SQL
Server and it would work without issues.

IMO, you should stop developing you app on JET for this client and get MSDE
installed. Any thing you do with MSDE can be moved to SQL Server without any
change (well, maybe, login/password).

 

[Albert D.Kallal]

First, I think we need to clear up some terms here.

You have mentioned sql server, but as far as your information goes here, you
seem to be talking about a standard split database. That means you got a
front end ms-access, and you got a back end in ms-access.

The above setup as ABSOLUTE NO RELATIONSHIP to sql server.
The above setup is NOT a client to server setup.
The above setup is what we call a "file share".

Note that this simple split concept is how MOST ms-access applications are
setup. So, for you to bring up the issue of sql server 2000 into this
converting creates a good deal of confusing here. (since, people do often
in fact use sql server for their data, and NOT a mdb file for the back end).

The process of using ms-access with sql server is a VERY DIFFERENT process
then that of a linked table to a mdb back end. So, I just want to point out
that by loosely using the term sql server as part of your problem here is a
formula for mass confusing.

It is possible that you are in fact trying to move the data from a mdb file
to sql server, but then you would not be talking about linking tables to a
mdb back end. Note that you *can* use ms-access with sql server, but when
you do so, there is NO mdb file for the back end, but only sql server.

So, I going to leave it up to you to clarify the above. However, in reading
your post, and reading between the lines, it sounds like you are NOT
using sql server to store your data at this point in time.

Given this assuming, we can now start to answer some of your questions here:

server, and need some help! The background is: I created application
(mdb) using Access 2003 on my laptop, converted it to Access 2000
(client uses both 2000 and 2003), split it into Front End and Back End,

When you say split it, you mean that you have been developing with a split
database for a considerable amount of time...right?

You would NEVER split a database RIGHT before deployment, as that would not
allow sufficient testing. It would also not allow you sufficient time to
become familiar with a split setup. A split database is a normal setup, and
one that you will work with as a developer LONG BEFORE you decide to start
deploying the database. The reason here is that some things can work a bit
different with a split system, so you better well developing and test your
forms and code with a split system before you deploy. So, as you go along
and
find out a bunch of problems you can work around them. You don't want to
split the day before you deploy. And, you don't want to learn ms-word the
night before you have to write a large paper!


As a general rule, splitting does not requite you to change code, but you do
need to develop in this manor, and have a split system part of your
development process just like testing a report is going to be part of your
development process.

, he had to do some work to change the linked tables'
addresses, to point them to the server.

As mentioned, since you are the developer, I sure you use the linked table
manager all the time. If you are new to the linked table manager, then you
need to get up to speed with it, and this will fast become a "normal" part
of your development process.

I have many questions, but I'll start with this one: what is the
procedure to follow so that I can still work on my home laptop and make
changes, and then update the Front End application on this one test
computer (obviously there will be more users but I am starting simple)?

You simply compile your mdb application to a mde, and then have it copied to
the target pc. At this point you either use the linked table manger and
link the tables to the back end mdb.

Of course, it makes sense to have some code in your startup routines check
the link. In fact, you could even put in some code to re-link to the back
end location (assuming you "know" that location for the client). Regardless,
either way, you have to manage the re-linking problem in code, or part of
your install procedure (instructions) to the client.

The IT person walked me through this once, but it seemed convaluted
and I'm sure there is a better way. He said if I copied my updated
.mdb to the workstation and overlaid the original, I would have to
re-link all the tables. To avoid this, he had me open the existing
database and do a file---->import of all the objects from my updated
version.

Gee, you think that importing all the table links, and then deleting the
ones that got re-named with a"1" on the end is better them simply going
tools->linked table manager and browsing to the back end file? Using the
linked table manger is 100 times easier.

Should I be working on my laptop via MSDE, or something that mimics a
server?

Opps..there we go again, you are bringing in the issue of sql server, and as
far as I can tell, you are NOT using sql server. Don't confuse windows 2000
server, or windows NT server with that of a database product like Oracle, or
sql-server. They are applies and oranges. As mentioned, as far as I can
tell, you are NOT using sql server to store your data, but only a standard
mdb file for the back end.

To get you up to speed on linking, and splitting databases, you can read the
following article of mine:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm