SQL SERVER OVERVIEW

SQL Server is Microsoft's Enterprise level server database product.
We'll begin our introduction to SQL Server by discussing how the database engine and storage differs from all "Access" implementations, with which you may be familiar.

Server-Based vs. PC-Based Database Engines

Whereas the Jet database engine (the "native" database format which ships with Access prior to 2007 - whose data resides in MDB files), SQL Server runs as a separate service on a server on your network. Your application submits requests to this running service and the server returns the results from the database to your application.
Jet (now updated as the Access Database Engine found in Office 2007 and later with ACCDB files) is what many people think of as an "Access database". It runs its data engine as a separate instance on each user's PC – even though a single data file MDB can reside in a shared central server location.

The guiding principle behind server based database management software (such as SQL Server) is that the server engine processes all data requests in a single location.
The advantages of this are many – not least of which is that the data is easier to maintain in a consistent state. Some of the reasons for that include:
• There aren't many running instances of the engine each trying to update the same data file.
• If an error occurs during a write operation corruption is far less likely.
• The server is able to create detailed logs. Therefore, even if data loss does occur, a restore from backup is that much easier.
There are a wealth of other functionalities some of which we'll discuss later.

The switch to Client Server should not be undertaken lightly. Jet and Access provide much "hand-holding" in creating databases; they were born to bring databases to the masses. SQL Server asks a bit more in return for what it offers. For example – if you don't already have some experience with writing or at least looking at SQL (Structured Query Language) code then you might want to consider looking in to it.
[The penultimate section of this FAQ "Core SQL Dialect Differences" shows some of the SQL differences between Jet's SQL syntax and SQL Server's dialect "Transact SQL" (T-SQL).]

Other Data Objects
One major difference worth mentioning between Jet and SQL Server are the data objects other than tables. In a "native" Access application you can create and save a variety of queries. These may comprise a simple selection of data from a table, possibly ordered specifically for use in lists.
Queries might return only a subset of data from a table or may join several tables. They can include parameters for filtering the results. Queries can also contain action statements – for updating, inserting or deleting data.
An advantage of a stored query over ad-hoc SQL is that the execution plan (the process of deciding how the database engine is going to run the query) is stored rather than being recalculated upon each request.

Server databases such as SQL Server split some of this query functionality up among distinct objects. Views are saved select statements involving one or more tables with fixed criteria – they do not accept parameters or support sorting.
Stored Procedures (SP) occupy a huge proportion of the remaining functionality. They are stored T-SQL statements which can accept parameters and may select or alter data but, unlike Jet SQL, T-SQL supports a wider variety of syntax including multiple statements per procedure, conditional logic (IF statements), iterations (Loops) and even basic error handling. Like Jet queries Stored Procedures store their execution plan to optimise performance.
You can't select from a stored procedure as you would a View or a Query in Jet – but for that SQL Server provides User Defined Functions (UDF's - available since SQL Server 2000). UDF's offer much of the functionality of Stored Procedures but they can return scalar or table type data.

Even with all this functionality (and some accompanying powerful reporting and data tools), ultimately SQL Server is a data engine – to which we must still provide a front end interface. And Access very often plays that role.


A Note to the Newly Upsizing
Many Access users are aware of SQL Server as an option to which they can "upsize" their application's database.
It's often suggested in forums as a solution when an Access database using Jet is encountering performance problems. The "upsizing" implied is based on the assumption that SQL Server is a larger, more robust product. Moving "up" to it can help solve issues.

So say you’ve built your Access application based on a Jet backend (an MDB or ACCDB file). You feel you’ve moved "beyond" it; it’s not fast enough for you or can’t hold enough data and you want more power.
You're advised to upgrade to SQL Server and all your problems will be gone? The raw power of it will supply you with data in the blink of an eye…?
Sorry no – it doesn't work that way.
Sometimes it just might, but generally speaking it's unrealistic to expect that kind of result.
One of the things that the Client Server uninitiated must understand is that the restrictions haven’t all lifted when you upsize. Indeed – you should now be more careful than ever.

As previously mentioned, the key distinction with client server development is that the database engine runs on the server – receiving requests from multiple client applications and returning the data matching the requests of those clients.
A lot of resources can be thrown at this single location - a powerful server, fast disks, huge amounts of RAM. But weighed against this is the fact that the server is solely responsible for supplying all your users' requests, handling each application's request for data: every update, delete or insert command.

Consequently, in a client server scenario, need to think small (even if you don't want to accept that).
You should select the very minimum amount of data that you can in a way that minimises the processing the server must carry out to satisfy that request. Give it as little to do as possible, because it's doing the data crunching for every other user too.
This includes:
• requesting the fewest fields that you need to work with
• requesting the fewest records that are required right now (you can always fetch a different set in a moment’s time)
• helping the engine by making the most appropriate use of indexes.

There could be several, several hundred, or many more users making demands on the same database.
And even if they're not using your database – they can be working with a different one on the same server, perhaps even a business critical database that your server also hosts.
Whatever subset of your table data you want to select has to come across the network to your application – and that doesn’t get any faster because of your backend choice. The wire isn’t any thicker.
And if you make lazy data requests then the server has to process and return that much more, harming concurrency (which is the support of all those other users) and your own application's performance.


SQL SERVER TOOLS

Management Studio ("Enterprise Manager")
Management Studio (MS) as it has been called since SQL Server 2005 is the most commonly used management tool with SQL Server. (Previous versions were known as Enterprise Manager which worked in conjunction with Query Analyser but is now incorporated into MS as one product)
Management Studio is a graphical user interface into the server's databases, objects and security. You can visually create databases in a fairly similar way to which you would in Access.
[The SQL experienced can still use DDL (Data Definition Language) SQL statements and built in stored procedures to work with SQL Server objects and MS is actually just a convenient GUI which sits over the top of such functionality.]
Management Studio offers much functionality – but to begin with it's about as good a way as you'll get to obtain an insight into the SQL Server world and the objects you have at your disposal.

Books Online
SQL Server's help system is so famous it even has its own name!
Books Online (BOL) is a very comprehensive implementation of a Help system.
It's also entirely available as an online resource.
Any function of SQL Server that you're unsure of described in either this FAQ or other reading then BOL is your first port of call.

Data Tools
Though beyond the scope of this FAQ it's worth mentioning the considerable power offered by both Reporting Services and the Integration Services (IS) which replaced the previously named Data Transformation Services (DTS).
They extend the power of SQL Server not only beyond the engine – but often beyond what any other similar tools could provide.

SQL Server versions
Rather than detail the releases in recent years (which, over the last 10 years, have been SQL Server 7, 2000, 2005, 2008) the main distinction to highlight here is that between what editions exist of a given product.
If you want a business/enterprise solution then there are several levels of licensing versions with which Microsoft sales staff will, no doubt, be only too happy to help you.
Since SQL Server 2005 there has been an Express edition available (previously available under the guise of the MSDE - Microsoft SQL Server 2000 Desktop Engine, though it enforced more limitations).
This is a freely available and distributable version which offers very much the same functionality as full SQL Server, but with some limitations such as a relative lack of packaged tools; a 4GB database size limit; using only a single processor and 1 GB RAM on the server.

Installation and Setup
In an office location this should be performed by IT staff but there are occasions where interested Access users will want to do it themselves – particularly when they're using the Express edition. You don't need to install SQL Server onto a physical server – especially if you're just learning. Your own PC is perfectly capable of running this (assuming you're permitted to install software there) and is a great place to get your feet wet.
The 2005 express edition can currently be downloaded from Microsoft – with the larger download option to include the advanced services (you'll want this option as it includes Management Studio).
Once downloaded and installing you'll be prompted for information like the Server Instance name. This would often be just the name of the machine, but make sure you choose a name that will be easy to remember because you won’t be able to connect to it without using the correct name.

To make use of the server fully you set definable options which are set from the Configuration Tools which are part of the standard installation. You'll need the SQL Server service running as this is the engine (it's up to you if you want this to commence at system start-up) and the Browser service too – to ensure easy external accessibility is possible.


Upsizing
The act of Upsizing is where your existing database stored in a MDB (or ACCDB) file is recreated as a SQL Server database.
A single SQL Server instance will typically contain many databases. The server keeps track of them in its own Master database. Adding another one can be accomplished in several ways – such as manually through Management Studio or via SQL DDL commands.
Of course when you have an existing database in an MDB file you often want to make use of this as your starting point (rather than re-creating it from scratch). There is more than one way to accomplish this task too.
Access now has the built-in Upsizing Wizard to help with this task. It walks through the process of creating a new database on the server you specify and recreating your local data objects on there.
The Wizard is not to every developer's satisfaction, but is capable in many instances and is at least worth checking out.
With the introduction of SQL Server 2005 Microsoft offers the SQL Server Migration Assistant (SSMA) which will also take your MDB and create a SQL Server database based on it. The SSMA is very configurable and offers a lot more validation and analysis of your tables than the Upsizing wizard.
You can also perform the import from the SQL Server side using its own tools – i.e. Information services. (Prior to the SSMA, DTS was often preferred – indeed many will still prefer IS now).


Security
Often this consideration alone is sufficient reason to upsize from Jet. With Access 2007 user level security has been removed from the database engine in Access (ACE). Even before that Jet's user security has been compromised over time with utilities available to crack the security it offers.
SQL Server security is a topic you'll want to get to grips with. It's much more robust than Jet security and ever more comprehensive in its implementation.
The system administrator (sa) login exists by default. The user account sa logs in to, dbo (database owner), has full rights to the server and can be used to create any objects. Therefore, even if you have access to it, you'll want to password off that login (you must do so since SQL Server 2005)
[You'll notice names of many objects in T-SQL are prefixed by the owner, or owning schema, such as: dbo.TableName.FieldName, which identifies the creator (or owner) of that object.]

You'll want to create your own set of Users for your database – and choose what level of permission each has on the objects therein. You also create the login credentials to which you want to map these users, so that your users login with the appropriate permission on objects and nothing more.

Crucially SQL Server offers both its own authentication for users (using the login usernames and passwords you create on the server) and also Windows authentication whereby your PC or network user credentials will allow login and consequently map to a given user. This can be a substantial advantage when working within a Windows domain and allowing users to log in to their applications transparently.


CONNECTION METHODS

In the following sections we'll look at the ways in which you can connect to SQL Server from Access.

Linked Tables

In Access applications you'll use linked tables all the time if you connect to external sources or just if you operate a properly split Jet database. If you're not accustomed to such a setup then you should read about doing so.
The drivers that are available to create linked tables include a wealth of database formats accessed through ODBC (Open Database Connectivity). SQL Server is supported through (at least) two such drivers – the SQL Server and Native Client drivers.

Linked tables with a Jet backend behave very much like local tables (with relatively minor differences).
Linked tables against a server database can have a bad reputation among some areas of the development community. Indeed at times it’s based on some truth (experience caused by certain predictable implementations). But linked tables certainly have their place if properly used. The misconception that they always pull over an entire server table's content and then filter on the client is completely false. Jet, in its SQL request wrapping role, is much more intelligent than that.
[For a more thorough discussion and proof that linked tables can be efficient see the FAQ here and the links in "Further Reading" below.]
They’re an excellent tool - especially for the newly upsizing user as they allow continued functionality so similar to what you've used previously that you can get off and running surprisingly quickly, allowing you to maintain much of your application exactly as it used to be.

[Bear in mind that the upsizing or link table wizard can give you linked table names with a "dbo_" prefix. This is only the linked table name (think of it as a wrapping table definition) and is not the name of the table on the server which is still as you originally named it. You're free to rename these linked tables back to the original name, using code if you choose – this will not affect the server tables at all but better allows your existing objects to carry on as before.]

When using linked tables the local Jet engine is still there initially processing your requests. It needs to access data from the server in a way that it can understand – and the ODBC driver provides that translation.
A request for data made from your Access application – from a form or query – is translated into a neutral dialect and then to T-SQL. For simple requests of data the translations are trivial and go well.
For example
SELECT * FROM Orders WHERE OrderID = 1
is perfectly valid syntax in both Jet and T-SQL.
Many of the basic SQL constructions have direct (or very close) equivalents (See "Core SQL Dialect Differences" at the end of this FAQ).

The ODBC provider makes the data request (submitting the SQL statement to the server) which may or may not be formed precisely as asked by the client application – and the server will send down the wire the records which match this and only those records. Remember the process of selection occurs on the server now– so using appropriate indexes is still very important – but it’s the server you’re helping to process now not just Jet.
If the query includes syntax that can’t be properly used to limit that result set then Jet will issue requests for the parts of statement which it can have the server parse and then filter the rest locally before returning it to the application - potentially this could mean all the rows from the tables concerned comes across the wire initially. This is exactly the kind of scenario that you need to bear in mind as a possibility and avoid.

For example consider a query
SELECT * FROM Orders WHERE OrderDate = Forms!frmCriteria!txtFilterDate
or an awful query such as
SELECT * FROM Orders WHERE CStr([OrderDate]) Like "*2007"

There is no way that the SQL Server itself can have any knowledge of Access objects or VBA functions. Access and Jet are the technologies which make this possible, by venturing outside of that arena you're losing interpretation and efficiency.
As it stands there is much Jet and ODBC can do to reform such requests into T-SQL equivalent statements, but if you add enough complexity the server will have to return all rows so that Jet (using it's Access/ VBA aware expression service) can then limit that result set to appropriate rows to be displayed.
So construct your queries based on linked tables as carefully as possible. Build or alter them in code if needed.

[Updatability through linked tables can be assisted by protecting against certain issues. For example Jet doesn't support Null values in a Boolean (Yes/No) data type; however, SQL Server does support it in the mapped Bit field. Setting the default value of Bit fields on the server is an important step to allow Access to use the linked data. Similarly a TimeStamp field in your SQL table can be of benefit, the SSMA inserts these during upsize by default.]


General Concepts to Consider
• Make small data requests. Fewer fields and fewer records - ideally just the one row you need and the exact fields you wish to work with or view!
• Cache lookups. When you need a range of records (usually for a lookup list) consider keeping a copy of the records locally. Either create a local Jet table in your Access application or perhaps maintain recordsets that you open at application start-up and use throughout wherever required.

Linked Table Specific Considerations
• Keep your query requests interpretable by the Server.
i.e. don’t include irresolvable expressions in Joins or Where clauses of SQL statements.
You could make use of functions to operate upon or format the query results after they are returned but be careful that you’re not including some criteria based on it.
• Perform Joins and aggregations on the server.
If you create a View on the server and link to that as you would a table then it has better optimisation options than joining on two linked tables in the Access FE.
If you want aggregated data (Groupings/Sums etc) then it is an even better idea to do so in a view. Guarantee that the server will do the number crunching and send back just the summary records over the network.
• If you want a simplistic method of passing locally held table data to a server table then a linked table and a native query offers an immediate solution (however the work Access does behind the scene is not a single batch operation).


Passthrough Queries

If you either know enough syntax for T-SQL or keep to simple ANSI standard SQL statements, you can employ Passthrough queries in your application. These are queries which make direct ODBC requests to the server. The results they return are read only but they’re efficient. Jet has no hand at all in their execution, which is why you must construct them directly in the dialect spoken by the server (T-SQL), and you write that SQL code directly – no QBE grid help as there is for native queries.

Though the data returned in a passthrough is read only, if you only display that data to the user (rather than rely on default binding) you can use another passthrough query to execute Update and Append SQL statements for writing the data back to the server in an equally efficient manner – including executing Stored Procedures on the server.
This requires code to fill the forms and gather the data. Also because Passthroughs don't currently accept parameters you must construct your SQL statement in VBA and amend the SQL of a passthrough in code at runtime.
If you think about it, with a bit of effort, there are few situations where you couldn't use Passthroughs to implement in some way.
As with native Access queries, if you create a passthrough in code it doesn't necessarily need to be persisted as a saved query object in the database and so temporary passthroughs are an option.
[Passthroughs currently cannot be used in the same manner as native tables and queries for the bound source of SubForms and SubReports as the Master and Child link fields cannot be enforced. This is hopefully something we can look forward to Microsoft correcting in a future version of Access. In SubForms, for now at least, you have the previously mentioned option of altering the query's definition in code as the parent form navigates to accomplish the same task]

Even in an application which makes use of Linked tables consider using Passthrough queries where possible to more efficiently obtain data (like lookups) for display and to write records back to the database using Update and Insert SQL statements.


Code Connections and Going Unbound

If you're used to working with DAO code in your Access applications it is still possible to do so. Linked tables and Passthroughs would allow that.
Even if you want to code against the server directly, this is still possible in DAO although from Access 2007 onwards it is impossible to completely bypass Jet while doing so.
By its nature, ADO offers a wealth of methods of connecting to a range of data sources. You may have used it to access your MDB data and it is equally (or even more) capable of connecting directly to your SQL server data.
ADO has a relatively flexible object model which can make it seem more complicated than it really is at first. A good reference source on the subject is invaluable if you intend to make a lot of use of ADO with server data from Access.
Fundamentally you'll use the OLEDB provider to establish a connection to the server database and use it to open and retrieve recordset data, execute SQL statements and stored procedures which you can do as the connection to the server is direct. You’re not going through Jet first before executing these commands and retrieving this data, hence all processing is performed on the server.
The efficiency rules already discussed still apply of course – select/update/append only those few records that you need.

A persistent ADO connection can be a good idea, opened upon application launch and maintained until you quit. The act of opening the connection is an overhead that you can then do without for each of your subsequent calls and writes.
[ADP’s do this for you in the CurrentProject.Connection object – but you can’t be sure that Access has maintained the same connection object – and a copy is returned to you in each reference to CurrentProject.Connection.]
However if your requests for data are relatively infrequent then such a connection can be an unnecessary resource held open on the server and you might choose to open and then close a connection upon each data requirement. It's just one of the decisions that have to be made during development based on each application's requirements.

Once you've used ADO to execute or retrieve records you then need to use that data in Access (without the convenience that Linked Tables provide – the RecordSource property of a form isn't of help to you now). A long standing option is to use an unbound form and fill its controls via code walking through the recordset and updating through a recordset or update command afterwards. However you can also choose to bind a form to a recordset which you’ve opened based on your server data (available since Access 2K and generally improved since then) whereby updates are handled for you.

A real benefit that Access has over other development platforms is its close integration with Jet (and hence housing of local tables). Client Server development certainly need not mean completely Jet free. Jet is efficient and quick and very good at what it does.
You can read data from the server, store it locally in Jet tables – view and update it, and then write those records back to the server. The disconnected nature of that method helps the server support all the more users.

ADO Connection Considerations
• Open and reuse the same connection object if it's of benefit
• For connection string syntax – the definitive sources are: connectionstrings.com and Carl Prothman's site.
• If your IT infrastructure already has a DSN in place for connections to the server then you can use ADO to open an ODBC connection instead of an OLEDB one. There’s a subtle performance disadvantage – but potentially less maintainability issues than building your own connection strings.
• Open the most efficient type of recordset that you can for your requirements.
If you’re only reading a data row then consider a read only forward only recordset.
If you want to bind to a form so it's updatable, you either specify a client side cursor or just use the data shape provider (required in Access 2000). And you’ll also need a Static, Keyset or Dynamic cursor type recordset. You’ll also always need a Client Side cursor if you plan to disconnect the recordset.

Further Considerations
• Use Stored Procedures for as much of your work as is reasonably possible for both reading and writing data. The stored execution plan offers worthwhile processing reductions.
• On the fly SQL might seem tempting, and executed through the connection object it is indeed all run on the server, but leaving as much logic on the server and not in your application as possible leaves your database more versatile.
• Use Update/Append statements to make changes to data where possible.
If you’ve filled an unbound form by opening a recordset to read values it’s perhaps tempting to open another to write the changes – but you’ll achieve greater efficiency by constructing a SQL statement to execute the write – or of course, better yet, using a stored procedure.


Access Data Projects (ADP)

ADPs were introduced in Access 2000 and are an alternative format file in Access that offers quick and direct access to SQL Server data. The connection to the server is established by Access upon application start-up using stored specified settings and is maintained by Access for your application's use as required. The connection employs the Access shape provider – which allows direct, updateable binding of recordsets to forms.
Working with the OLEDB data provider – ADPs actually operate in a somewhat unbound way, filling controls on your bound forms and then disconnecting the data behind the scenes until you need to write back (similar to how you might choose to code a form yourself in an MDB).

In an ADP the Jet engine is not present. There can be no local tables hence use of DAO code makes little sense. If you want to work with data in code then you really need to be familiar with ADO.
[CurrentDb is still a method of the Application object and so will compile, but it doesn’t return an object in an ADP. You'll receive a runtime error in any code which refers to it. Though you can add a DAO reference library to an ADP if you choose it would only be to work with external Jet data, which ADO can do too of course.]

ADP’s support extra functionality such as binding recordsets to Reports (a feature not available in MDBs) which is useful given the lack of local tables.
For data that would normally be stored in a local table for lookups or local operations then a disconnected ADO recordset (perhaps persisted on your local hard disk) can operate in a similar capacity or you could fall back on Jet again to maintain a (separate) MDB storage of local data for retrieval without going back to the server.

The functionality offered in an ADP may seem favourable but there are definite caveats to take into consideration.
The ability to open a Stored Procedure through the Access UI as you would a query in a UI and be prompted for the parameters has no real place in a professional application.
However ADP’s permit the direct binding of a form to a Stored Procedure where you can specify the required parameters in a property of the form called InputParameters (each parameter comma delimited from the next) which can refer to an Access expression as the source value.
However this functionality is accomplished by inserting the data on the form as normal in an ADP and update time – it creates an update statement based on the underlying tables involved. So if the user has permissions to execute the Stored Procedure but not to the tables directly then this will fail.

The development of ADP’s isn't set to advance in future versions of Access but they can still be a useful, expedient and powerful tool for those that choose to use them.
However MDBs (now ACCDBs since Access 2007) still offer almost everything you could need from a data perspective – with the extra flexibility of the local database engine for ad-hoc processing needs.


Core SQL Dialect Differences

A selection of fundamental differences of SQL Server's implementation of Structured Query Language from Jet:

T-SQL string delimiter uses only single quote.
Jet: SELECT * FROM Orders WHERE ShipCountry = "France"
SS : SELECT * FROM Orders WHERE ShipCountry = 'France'

Standard Wildcard Characters are % and _ instead of * and ?
Jet: … WHERE ShipCountry Like "F*"
SS : … WHERE ShipCountry Like 'F%'

Dates are also delimited through single quote ' and not octothorpe #. US format remains the default.
Jet: … WHERE OrderDate Between #12/01/2007# AND #12/31/2007#
SS : … WHERE OrderDate Between '12/01/2007' AND '12/31/2007'

String concatenation uses + symbol and not & (as can be used in Jet) - Nulls therefore do propagate.
Jet: SELECT FirstName & " " & LastName As FullName FROM Employees
SS : SELECT FirstName + ' ' + LastName As FullName FROM Employees

For such circumstances the Access Nz function maps to T-SQL IsNull (or the more powerful Coalesce)
Jet: SELECT Nz(Region, 'N/A') FROM Employees
SS : SELECT IsNull(Region, 'N/A') FROM Employees

VBA's IsNull is not exactly represented (however in either SQL dialect "Is Null" is a better choice)
Jet: SELECT * FROM Employees WHERE Region Is Null
SS : SELECT * FROM Employees WHERE Region Is Null

VBA's Immediate If function is implemented with ANSI SQL Case statements
Jet: SELECT IIF(Region Is Null, 'N/A', Region) FROM Employees
SS : SELECT CASE WHEN Region Is Null THEN ' N/A' ELSE Region END FROM Employees

VBA Type conversion functions can be handled with CAST or CONVERT calls
Jet: SELECT CStr([Birth Date]) FROM Employees
SS : SELECT CONVERT(varChar, Region) FROM Employees

Data Types in T-SQL are *not* implicitly converted for you!
e.g., Jet will silently convert the numeric value to concatenate Text and Integer fields in a SQL statement
Jet: SELECT "Order Dated - " & [Order Date] As FullNameRef FROM Orders
SS : SELECT 'Order Dated - ' + CONVERT(varChar, [Order Date]) As FullNameRef

Boolean (Yes/No) Data Types in Jet (stored as 0 or -1) are represented by Bit fields in SQL Server (stored as 0 or 1). An ODBC linked table will perform the conversion for you – but if you're writing the SQL yourself you need to know. T-SQL also has no "True" and "False" literal constants.
Jet: SELECT * FROM Products WHERE Discontinued = True
SS : SELECT * FROM Products WHERE Discontinued = 1

The aliasing keyword "AS" is optional in T-SQL for both tables and columns – in Jet tables only.
Jet: SELECT [Unit Price] * Quantity As TotalAmt FROM [Order Details] tblDetails
SS : SELECT [Unit Price] * Quantity TotalAmt FROM [Order Details] tblDetails

You cannot reference aliased columns in T-SQL
Jet: SELECT [Unit Price] * Quantity As TotalAmt, TotalAmt/10 As Installment FROM [Order Details]
SS : SELECT [Unit Price] * Quantity As TotalAmt, ([Unit Price] * Quantity)/10 As Installment FROM [Order Details]



Further Reading

We've barely started to scratch the surface in all honesty. Hopefully this overview gives an inkling into the processes of Client Server development with Access and SQL Server.
A few sources for more information are found below. Read and enjoy.

What are the main differences between Access and SQL Server?

Moving Your Access 2002 Database to SQL Server

Optimizing Microsoft Office Access Applications Linked to SQL Server



Grateful thanks for suggestions from:
George Hepworth
Brent Spaulding
Walter Niesz
Larry Larsen
Danny Seager
Glen Kruger
and Truitt Bradly for getting the whole thing started.

Please note:
If you have questions about this or any other FAQ/code archive submission, please start a new thread in a general forum which relates to the subject of the item about which you have a question. You should include a link to the code archive item in your post. You can also pm the original poster of the code archive item to draw their attention to your new thread.

If the contributor indicated it in the submission, you may contact them directly via PM without posting a question in a new thread.

LPurvis
UA moderator