SQL Server needs help from MS Access (or something)

[javelin]

I know, that statement can make me lots of enemies. I am sorry, but
I've worked with a SQL Server back end with an MS Access ADP on the
front end, and the process of creating queries, and especially update,
insert and delete queries is SO much easier in Access. I didn't think
I'd miss it when I moved over to an environment managing about 160
"nearly identical" databases across several servers. I was wrong.

I suppose I can create a front end file for each of the back end
databases, but it's not practical timewise to create them all. I wonder
if it's possible to create one and run some VBA code to change the
connection string. I realize that's finally a question in all of this
ranting. I love SQL Server, I just with it had intellisense. I've been
using SpeedSQL in this environment, and it's awesome. If anyone has
additional tools they can suggest, please let me (and the whole group
as well) know.

Thanks!!!

[David Portas]

Re: SQL Server needs help from MS Access (or something)

javelin wrote:[color=blue]
> I know, that statement can make me lots of enemies. I am sorry, but
> I've worked with a SQL Server back end with an MS Access ADP on the
> front end, and the process of creating queries, and especially update,
> insert and delete queries is SO much easier in Access.[/color]

Is it a query "designer" you are looking for? I don't know anyone who
uses those things. Take the trouble to learn to write your own SQL.
You'll write much better, faster code and you'll *understand* what you
are doing instead of wasting time drawing pretty diagrams and filling
in grids :-). Quite honestly, Query Analyzer or SQL Server Management
Studio is the best place to do any serious work.

[color=blue]
> I didn't think
> I'd miss it when I moved over to an environment managing about 160
> "nearly identical" databases across several servers. I was wrong.[/color]

That doesn't sound like a great architecture to me. What's the
rationale for having 160 near identical databases?

[color=blue]
> I suppose I can create a front end file for each of the back end
> databases, but it's not practical timewise to create them all. I wonder
> if it's possible to create one and run some VBA code to change the
> connection string.[/color]

That's an Access question. Certainly it is possible in .NET. All the
noises coming out of Microsoft now seem to stack up against using ADP
however. I don't keep in close touch with Access product developments
and things can always change but at the moment it seems like MS expect
you to move away from that technology.


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[David Portas]

Re: SQL Server needs help from MS Access (or something)

javelin wrote:[color=blue]
> I know, that statement can make me lots of enemies. I am sorry, but
> I've worked with a SQL Server back end with an MS Access ADP on the
> front end, and the process of creating queries, and especially update,
> insert and delete queries is SO much easier in Access.[/color]

Is it a query "designer" you are looking for? I don't know anyone who
uses those things. Take the trouble to learn to write your own SQL.
You'll write much better, faster code and you'll *understand* what you
are doing instead of wasting time drawing pretty diagrams and filling
in grids :-). Quite honestly, Query Analyzer or SQL Server Management
Studio is the best place to do any serious work.

[color=blue]
> I didn't think
> I'd miss it when I moved over to an environment managing about 160
> "nearly identical" databases across several servers. I was wrong.[/color]

That doesn't sound like a great architecture to me. What's the
rationale for having 160 near identical databases?

[color=blue]
> I suppose I can create a front end file for each of the back end
> databases, but it's not practical timewise to create them all. I wonder
> if it's possible to create one and run some VBA code to change the
> connection string.[/color]

That's an Access question. Certainly it is possible in .NET. All the
noises coming out of Microsoft now seem to stack up against using ADP
however. I don't keep in close touch with Access product developments
and things can always change but at the moment it seems like MS expect
you to move away from that technology.


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[javelin]

Re: SQL Server needs help from MS Access (or something)

David, I appreciate your reply. First, I should say that I'm quite
fluent at SQL coding by hand, what I'm not that good at is remembering
the names of tables and fields, so having a drag and drop interface
helps a great deal. Second, no matter how good you are, I believe that,
given equal knowledge of a data structure and predefined goals, I can
drag and drop my views at least as quick as you or anyone else can type
them. I know some very advanced SQL Server developers that would rather
pay for their own licenses to use Speed SQL or other design tools. BTW,
Speed SQL is not that great for graphical drag and drop design of
queries, it's rather slow, but it's awesome for intellisense. Just
alias a table, type in the alias followed by a dot, and the field names
pop right up. Gotta love that!

As for the 160 nearly identical databases, that is for 160 different
clients, each on having about 400 identical tables, some having
specialized tables, functions, views, etc., for their company. Thus, we
need to manage each one independently. To put them all in a single
database would complicate things in ways this environment is not
prepared to handle.

Thanks.

[Erland Sommarskog]

Re: SQL Server needs help from MS Access (or something)

javelin (google.1.jvmai l@spamgourmet.c om) writes:[color=blue]
> As for the 160 nearly identical databases, that is for 160 different
> clients, each on having about 400 identical tables, some having
> specialized tables, functions, views, etc., for their company. Thus, we
> need to manage each one independently. To put them all in a single
> database would complicate things in ways this environment is not
> prepared to handle.[/color]

Indeed, putting 160 clients in one database is likely to be bad idea
of security reasons.

To maintain and support these databases, reqiures well developed roll-out
routines and strict configuration management. Version control is an absolute
must. It's probably a good idea to add some tables to the databases that
keeps track of what is installed.

You had a question somewhere, but I will have to admit that I did not
understand what the problem really was.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[javelin]

Re: SQL Server needs help from MS Access (or something)

Erland, you're right about the security issues. As for updates. I
believe there is a well thought out script that updates all consistent
objects across all databases, and certain data is universal (zip code
info, etc) and is kept in a common database.

As for the question, I was suggesting that Access is a good front end
option to enterprise manager and Query analyzer, as it's quicker and
easier to create views and stored procedures. The only thing I don't
like is that you must save a view prior to running any SQL statements,
unlike the query analyzer, which allows you to run code, and then just
throw it away.

Ultimately, I'd like to see an add in for query analyzer that allows
for intellisense features, as well as a strong and easy to use code
repository.

What are your thoughts?

[Erland Sommarskog]

Re: SQL Server needs help from MS Access (or something)

javelin (google.1.jvmai l@spamgourmet.c om) writes:[color=blue]
> As for the question, I was suggesting that Access is a good front end
> option to enterprise manager and Query analyzer, as it's quicker and
> easier to create views and stored procedures. The only thing I don't
> like is that you must save a view prior to running any SQL statements,
> unlike the query analyzer, which allows you to run code, and then just
> throw it away.
>
> Ultimately, I'd like to see an add in for query analyzer that allows
> for intellisense features, as well as a strong and easy to use code
> repository.
>
> What are your thoughts?[/color]

Comparing Access to EM and QA is a bit like apples and oranges. Access
is a tool for writing applications, EM and QA are developer/admin tools.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Runner]

Re: SQL Server needs help from MS Access (or something)

javelin wrote:[color=blue]
> As for the question, I was suggesting that Access is a good front end
> option to enterprise manager and Query analyzer, as it's quicker and
> easier to create views and stored procedures. The only thing I don't
> like is that you must save a view prior to running any SQL statements,
> unlike the query analyzer, which allows you to run code, and then just
> throw it away.
>
> Ultimately, I'd like to see an add in for query analyzer that allows
> for intellisense features, as well as a strong and easy to use code
> repository.
>
> What are your thoughts?[/color]

Have you looked at CampaignRunner? It has a graphical query
environment, a central repository, and works great in multi-database
environments. It instantly combines them all into a federated data
network so you can work on all your data. Regardless of whether it is
spread over Excel, Access, SQL Server, Oracle, Teradata, DB2, etc., you
can query and modify your data as if it were all in one single place.