How to update queries on BE with a new version of FE?

[David via AccessMonster.com]

Hello,

After conversion of my MDB project to ADP/MSDE I ran into a following problem
- how to update changed views and procedures after releasing a new version of
FE?

While developing an MDB project I split FE and BE so I had only tables in BE
which made it possible to develop locally and then ship only a new version of
FE to my Customer that linked to Customers’ BE. Keeping the table structure
unchanged it was always possible to ship just the new version of FE with new
forms and queries and since the queries were part of FE there was no need to
change BE.

The situation with ADP/SQL Server is different as the queries (views,
procedures etc) are now a part of BE. Is it possible to develop in the same
manner as with MDB? How can I easily update the views and procedures by
Customer with a new Release of FE?

Thanks in advance for your help

 

[Norman Yuan]

Typically, you write some scripts, which use "CREATE
[TABLE|VIEW\PROCEDURE]..." or "ALTER [TABLE|VIEW|PROCEDURE" to modify the BE
Sql Server in use. Then you use tool, such as isql.exe or Query Analyzer to
execute these sql script. You can also write your own DB update tool in ADO,
ADO.NET, DMO... to do the upating, so that the updating would be more
transparent to your client, espcially if your clients do not have a
qualified DB administrator.

 

[Norman Yuan]

Sorry, by "isql.exe", I really meant "osql.exe".

Typically, you write some scripts, which use "CREATE
[TABLE|VIEW\PROCEDURE]..." or "ALTER [TABLE|VIEW|PROCEDURE" to modify the
BE Sql Server in use. Then you use tool, such as isql.exe or Query
Analyzer to execute these sql script. You can also write your own DB
update tool in ADO, ADO.NET, DMO... to do the upating, so that the
updating would be more transparent to your client, espcially if your
clients do not have a qualified DB administrator.

 

[David via AccessMonster.com]

Unfortunatelly, I am using only Access to create views or procedures which
hides the CREATE/ALTER format of an SQL query. Using the Access you see just
SELECT, INSERT etc. so it does not make the job easy.

The way you recommend seems to be pretty complicated - using some kind of ADO
code (Can it be written in Access?) I should extract the SQL queries from my
local SQL Server version and then run it by customer if I want to make it
compatible with a new FE release.

Is there really nothing easier? Do I need to write everything by myself?

 

[Sylvain Lafontaine]

Personally, I use the Red-Gate SQL-Compare tool for this purpose. Great
product; had paid for itself after the first use: http://www.red-gate.com/