[AccessD] Access to SQL Server

Jim Lawrence (AccessD) accessd at shaw.ca
Mon Nov 24 21:15:09 CST 2003


Hey Francisco...don't scare the fellow off just yet. :-)

MS SQL is simple to use but can take a life-time to master. My first
exposure to MS SQL was when the box appeared on the top of the desk. A
couple of us then received a quicky, few days of training then it was off to
install and write the front-end... :-\ and it was up and running in less
than a month, with a few hicups of course....

...and it is still running.
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Francisco H
Tapia
Sent: Monday, November 24, 2003 5:20 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access to SQL Server


Don't talk about SQL Sever as Access big brother.  SQL Server is a robust
engine which specifically addresses the need for Very Large Databases
(vldb).  Things are handled a lot more diffrent in SQL Server in that you
now will normally not want to allow users or processes to touch the actual
table via a simple ODBC connection, instead you will create VIEWS which are
like queries but cannot take parameters.  To use parameters you will need to
use Stored Procedures which are written in TSQL which should be easy enough
for you to grasp if you've ever used VBA.  Lastly TSQL is an awesome tool.
Using Sql Server 2000 and Query Analyzer you can even debug a stored
procedure.

In Sql Server you can write scripts (in TSQL) that run on the SQL Agent, and
you can schedule this to fire on a trigger, alert, or schedule.

lastly if you are trying to find the shortest point between 2 lines then the
view option mentioned below is probably the most optimal.  If your Acces
Front End is simply for data import then this is very acceptable, if not,
then it is a plausible solution for the interim, while you work on a more
robust interface using an Access Data Project which is simply a front end to
Sql Server.

lastly sign up to the dba-sqlserver list and also the Sql Server Central
Home website, it is very informative and has a very robust library of SQL
Scripts.

http://www.sqlservercentral.com/default.asp

--
-Francisco


Developer wrote:

>Well, not exactly ... Tables are tables, but ... In SQL Server, the
>eqivalent of a query is a View.  SQL Stored Procedures are roughly
>equivalent to Modules (but they are written in T-SQL, not VBA ...
>Although they are saying that in some future version you can write SP's
>in VB.net ... I can't wait for that, I hate T-SQL!)... And SP's can
>return data (like views) or execute insert/update/delete commands.
>
>BUT ... There are no Forms/Reports; you still need Access (or VB, or ASP
>pages) to be the Front End for your users.
>
>You can use Access as the FE, and link tables (or views) as you would a
>back-end .mdb.  So, you *might* get away with a copy of your current
>.mdb, linked to a View of the same name(s) as you current tables, with
>no other changes to the current Access application.
>
>Hope this is a good start,
>
>Steve
>
>
>
>
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tom Keatley
>Sent: Monday, November 24, 2003 6:38 PM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Access to SQL Server
>
>
>Hi all...
>
>I contract to a large company producing some goods which require me to
>process 450 individual orders each month. These orders come from 450
>different sources and are (currently) sent to me via 450 individual
>excel spreadsheets. To this end I have developed an Access database to
>process these orders and also produce the goods.
>
>In the New Year the company is going to change their ordering system to
>SQL server and all orders will be placed over the Web therefore
>centralising their ordering system. They have asked me what information
>I need to produce what I normally do from the server. I think the move
>is WELL overdue BUT...
>
>I  know little (OK... NOTHING) about SQL server and its capabilities and
>would not know the most efficient way of extracting the data.
>
>The data I need is Customer name and address (approx 450), Description
>of goods (Up to 700 items Per customer), and quantity to buy.
>
>My head tells me that read only access to a query via ODBC into SQL
>Server
>is the way to go   but many of you are working with Access AND SQL
>server
>and I thought I would ask within the group for advice.
>
>Can I talk technically about SQL server the way I talk about Access
>....ie is the terminology the same as in Tables, Queries, Forms and
>Modules etc (certainly dont want to appear stupid when talking about it)
>
>What connection options are there (I am physically 3000 miles from the
>server)
>
>The little I have gleaned about SQL server suggests that it is like the
>"Big Brother" to Access which means that I can talk about it the way I
>would talk about Access but ......am I correct?
>
>As always any help would be appreciated
>
>Regards
>
>Tom Keatley
>
>
>


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list