Deploy ADP and Stored Procedures to MSDE

[Guest]

I'm working with Microsoft Office 2003 Developer Extensions.

I need to upsize an mdb file to an adp project that will connect a msde
database.

The “mdb†file as some queries that the upsizing wizard converts to stored
procedures and views in the MSDE database.

Whit the mdb database I produce an mde file that I deploy to several
machines. If I made some change in the queries this changes are automatically
deployed inside the mde file.

Now, working with the adp project, the views and stored procedures are in
the development SQL server database and I need to do some extra work to
deploy them to the target MSDE's databases.

My questions are:
In general, what's the best and easiest way to accomplish this?

I’ve think about create a template.mdf that will be deployed with the
upgrade packages. However, this can be laborious because the most accurate
database is the development database in my SQL sever. So, to produce an
update package, I have to copy the views and stored procedures to the
referred template.mdf database, detach the databse from SQL Server and deploy
it inside the upgrade package. In the target machines, I have to produce code
to attach the template.mdf to the users MSDE and to copy the views and stored
procedures to the target databases where they are needed. I think this is
possible, but obviously complicated.

Another hypothesis that I pose to myself is to code in VBA everything I need
to create the views and stored procedures. This way, when I deploy an upgrade
ade project all that I need will go inside the ade project. But this way I
think that I will lost the advantages of creating views in the graphical
interface of Access or SQL Server Enterprise Manager since what really
matters is the VBA code that is the place where I have to do everything.

I try to clarify the problem. I apologise for the extent.
José António Silva

 

[Sylvain Lafontaine]

You can use DDL (Data Definition Language) statements (Create Table, Alter,
Table, Create Procedure, etc.) to update your database and run them from
your VBA application (or by using a batch file with ISQL or OSQL) but
obviously, the real problem is to generate the required SQL statements not
only to reproduce the entire schema of your database but also to keep the
data that may been already there in the client database.

There are many tools for doing this. Personnally, I use the soft from
RedGate Software but they are many other products available on the Net.
This will cost you some $ but it's a really good investment; otherwise you
will spend countless hours/days/weeks trying to synchronise all that stuff.

S. L.

 

[Wendell Bell via AccessMonster.com]

I'm curious why you feel the need to convert to an ADP from your current
MDB database. It appears you have already split your design into a front-
end and back-end and are deploying the front-end to individual
workstations. Why not simply upsize your tables to SQL Server and use ODBC
linked tables? It will be far less work, and your existing queries will
continue to work. The Upsizing wizard isn't perfect, but it will do a
decent job. Then if you have performance issues, you can create views or
stored procedures to solve them.

The current word from Redmond is that ADPs will continue to be supported
but will not be enhanced in future versions of Access. We find that ADPs
generally take longer to develop, and don't give any perceptible
performance benefit compared to an MDB/MDE front-end connected to a SQL
Server back-end. Finally, I should note that there is a conversion tool in
Access 2003 that will take a stab at converting an MDB into an ADP - it
works reasonably well with simple databases, but isn't very useful for
typical working systems.

 

[Guest]

The most important problem is that I have to support from now on a multi-user
environment and Access takes many minutes to relink tables when other users
are already working in the back end mdb. I’ve posted some questions about
this problem several months ago and I never reached any solution.
Since I have other concerns about future size of data, consequent
performance and so on, I decide that it is the moment to migrate to MSDE. ADE
has seemed to me the right choice just because apparently it is specifically
designed to work with MSDE. When I finally try to migrate I discover that the
changing of philosophy will become a nightmare (for me). I don’t appreciate
the obligation about to have everything in the server except the interface
and the VBA code. Because it’s hard to deploy stored procedures and views
that run perfectly as queries from client as I said in my initial question
and, as I will later discover, I lost the possibility to have some data in
the client side. I use this to implement metadata that became part of the
business logic and, it is always ready to deploy inside the client mdb. When
I frequently need to tune something, this is beautiful.

 

[Guest]

The most important problem is that I have to support from now on a multi-user
environment and Access takes many minutes to relink tables when other users
are already working in the back end mdb. I’ve posted some questions about
this problem several months ago and I never reached any solution.
Since I have other concerns about future size of data, consequent
performance and so on, I decide that it is the moment to migrate to MSDE. ADE
has seemed to me the right choice just because apparently it is specifically
designed to work with MSDE. When I finally try to migrate I discover that the
changing of philosophy will become a nightmare (for me). I don’t appreciate
the obligation about to have everything in the server except the interface
and the VBA code. Because it’s hard to deploy stored procedures and views
that run perfectly as queries from client as I said in my initial question
and, as I will later discover, I lost the possibility to have some data in
the client side. I use this to implement metadata that became part of the
business logic and, it is always ready to deploy inside the client mdb. When
I frequently need to tune something, this is beautiful.

Thank you,
José António Silva