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