SQL Server needs help from MS Access (or something)

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!!!

javelin  wrote:
> 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.

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.


> 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.

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


> 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.

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  wrote:
> 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.

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.


> 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.

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


> 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.

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, 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.

javelin (google.1.jvmail@spamgourmet.com)  writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx