Tuesday, January 14, 2014

** Index to Access Web Apps 2013 **

I am doing something here that I absolutely hate – learn something new. 

Despite my previous blog series: Access 2010 Web Databases: How Can I Put My Access Database On The Web?, I have steadfastly ignored Access Web Applications. Mostly, it’s been a matter of them being irrelevant to what I do in Access.

But I’ve decided it’s time to address the matter. Since I don’t know anything about them (other than in theory), I decided to share my journey.  If you care to follow along, I’d be glad to have you.

A note on the series: This topic is by nature, sequential. But since blogs sequence backwards, it can make it confusing. Therefore, I’m going to use this initial post as an index for the series. I’ll update it as the series progresses.  I strongly suggest that where ever you enter this series, you go back to the beginning.

How can I put my Access 2013 database on the web?

  1. Access Web Databases: 2010 vs. 2013

 

 

*

Friday, December 13, 2013

Access Web Databases: 2010 vs. 2013

Access web databases are dead.  But there’s no cause for alarm.  Now we have Access web “apps”.

With the introduction of Access 2013, Microsoft has made substantial changes to its vision of how to put Access database applications on the web. The differences between Access 2010 web databases and Access 2013 web apps are major, and you should consider them carefully before embarking on an Access web project.

Before I go further, I should point out a relatively minor terminology change.  Microsoft refers to 2010 web projects as “web databases”, while it refers to 2013 web projects as “web apps”.  I don’t think this will help much to differentiate the two products, but it’s at least worth noting. I’m going to continue prefacing the terms with the Access version, just to be clear.

So what are the differences, and why are they important? 

Differences

The most important difference is where and how the data is stored.  Both 2010 web databases and 2013 web apps require SharePoint (although different versions)  However, 2010 stores the tables and application in SharePoint lists, while 2013 stores them in SQL Server tables.  The minimum SQL Server version for working with Access 2013 web apps is SQL Server 2012. No earlier versions of SQL Server will work in this setup.

Secondly, both require SharePoint Access Services, but 2010 uses Access Services 2010 while 2013 requires Access Services 2013.  These services are very different and mutually incompatible. However, you can have both services running on the same SharePoint site, so you can host both access 2010 web databases and 2013 web apps as long as you have both services running.

Importance

This incompatibility means that 2010 web databases and 2013 web apps are entirely incompatible.  It that if you create a 2010 web database there is no upgrade path to 2013.  You cannot convert a 2010 web database to a 2013 web app.  It will have to be re-created from scratch.  You will still be able to maintain it in 2013, but you cannot create a new project.  There is no guarantee that future versions will even be able to do that.

On the other hand, you can easily migrate your data (structure and data) from a 2010 web database to a 2013 web app, but the application (forms, etc) will have to be rebuilt from scratch.

Recommendations

There are, of course, many other differences between Access 2010 and Access 2013, just as there are between any two versions of a product.  But in terms of deciding which is right for you, these are the most important.

If you already have an Access 2010 web database, there’s no need to immediately redevelop it in 2013.  But if you’re planning on using it in the long term, you should begin making plans to move to 2013.  All of Microsoft’s future development will be in the 2013 web app arena and future versions of Access may not even support 2010 web databases.

If you are starting a new project, there is no question you should be using Access 2013 web apps.

Thursday, June 20, 2013

You CAN use quotes as a text value delimiter in T-SQL

SET QUOTED_IDENTIFIER OFF

Even though I said in a recent post (Access SQL Delimiters vs. T-SQL Delimiters), that T-SQL uses only the apostrophe (') as a text delimiter, that's not entirely true. It IS possible to use the quote (") as a text delimiter, just like Access. To do this, you must set a T-SQL option called QUOTED_IDENTIFIER to OFF. This must be done at the beginning of the pass-through query:

SET QUOTED_IDENTIFIER OFF

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField="Roger Carlson";

SET QUOTED_IDENTIFIER ON

Because T-SQL (and therefore pass-though queries) allow multiple statements to execute, you can turn the option off, and then back on again in the same query.

Please note that I DID set the option back on at the conclusion of the query. That's because it will set it OFF for every pass-though query in this Access session, and you may not want that. It is always best to set an option back the way it was to avoid confusion.

This can be useful when you are converting an Access SQL statement (that has a lot of delimited text values) to T-SQL.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField IN ("Roger Carlson", "Sue Carlson", "Bob O'Brien");

Instead of replacing the quotes with apostrophes (remembering that Access Query Builder does not have a find and replace feature), you can simply bracket it with the QUOTED_IDENTIFIER option.

SET QUOTED_IDENTIFIER OFF

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField IN ("Roger Carlson", "Sue Carlson", "Bob O'Brien");

SET QUOTED_IDENTIFIER ON

Monday, June 17, 2013

Access SQL Delimiters vs. T-SQL Delimiters

In my earlier post: What are the differences between Access SQL and T-SQL?, I discussed in the differences between Access SQL and T-SQL in general terms. This time, I want to expand upon the differences in how delimiters are used between the two.

Delimiters are generally used around explicit values in SQL statements. By explicit value, I mean a value in one of the fields upon which you are searching. For instance, if I wanted to find the value "Roger Carlson" in a field in my table, I would surround the value in quotes (in Access), as I'll demonstrate a little later on. Different types of data require different delimiters.

As seen in my previous post, it breaks down like so:

Delimiters

Type

  Access

  T-SQL

  Numeric

  no delimiter

  no delimiter

  Text

  " or ' (quote or apostrophe)

  ' (apostrophe)

  Date

  # (pound sign)

  ' (apostrophe)

  Wildcard

  *

  %

  Delimited Identifiers

  [..]

  [..] or “..”

Note: For all of the samples I'll be using a simple SQL Server table. Access queries will be against a linked table. T-SQL statement will be as pass-through queries executed in Access. As such, table names in the Access queries will start with 'dbo_', whereas T-SQL table name with have 'dbo.'.

 

dbo_MyTable

MyTextField

MyDateField

MyIntField

Roger Carlson

1/1/2013

44

Susan Carlson

2/13/2013

88

Bob O'Brien

4/16/2013

0

Numeric

Numeric values do not require a delimiter at all, in either Access SQL or T-SQL

Access SQL

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyIntField>=44;

T-SQL

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyIntField>=44;

The SQL statements are identical and they produce identical output:

MyTable

MyTextField

MyDateField

MyIntField

Roger Carlson

1/1/2013

44

Susan Carlson

2/13/2013

88

 

Text

Text values do require a delimiter.

Access SQL

The delimiter is either a quote (") or an apostrophe (')

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField="Roger Carlson";

Or

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField='Roger Carlson';

This can be useful when the field value itself has a delimiter in it. For instance, if the name is Bob O'Brien, using the quote delimiter will not produce an error, whereas an apostrophe would.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField="Bob O'Brien";

(There are of course, other solutions for this, but that's beyond the scope of this post.)

T-SQL

Generally, the only delimiter used is the apostrophe (') -- although there is an exception, which I'll discuss below.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField='Roger Carlson';

To query a name like O'Brien, you'd double the explicit apostrophe (one of those "other" solutions I mentioned).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField= 'Bob O''Brien';

Note that this is TWO apostrophes, not a quote.

 

Dates

The date delimiters between Access SQL and T-SQL are also different.

Access SQL

Access uses the pound or hash mark (#).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyDateField>=#2/1/2013#;

T-SQL

T-SQL uses the apostrophe ('), just like text values.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyDateField>='2/1/2013';

In either case, the query engine will interpret the explicit date in US date format, so the above it February 1st, not January 2nd. If the date format is an issue, it's best to use the YYYY/MM/DD format.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyDateField>='2013/02/01';

 

Wildcards

Both Access SQL and T-SQL can use wildcards with the LIKE operator to allow the user to query for a character or group of characters anywhere within a text field. For instance, say if I wanted to know just the Carlsons in the table.

Access SQL

In Access, the wildcard most used is the asterisk (*).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField Like "*Carlson";

The question mark can be used to act as a wild card for a single character only.

T-SQL

T-SQL uses the percent character (%).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField LIKE '%Carlson';

 

Delimited Identifiers

So far, I've only discussed delimiters in regards to explicit values. However, identifiers (that is, table names and field names) also must be delimited when they contain illegal characters like a space or question mark or others. In order for the query engine to recognize a tablename or fieldname with spaces (or other illegal characters) in it, is to delimit it.

Access SQL

In Access, the delimiters for identifiers are the right and left brackets ([…])

SELECT [My Text Field], [My Date Field], [My Int Field]
FROM [dbo_My Table]
WHERE [My Int Field] = 44;

T-SQL

T-SQL has two delimiters available. Left and right brackets ([…]), just like Access, but it can also use the quote marks ("…").

SELECT [My Text Field], [My Date Field], [My Int Field]
FROM dbo.[My Table]
WHERE [My Int Field] = 44;

Or

SELECT "My Text Field", "My Date Field", "My Int Field"]
FROM dbo."My Table"
WHERE "My Int Field" = 44;

Invalid Column Name Error

Now, at best, using the quotes as identifier delimiters is a curiosity, except it explains the odd error message you get when you try to execute an Access query with text identifiers in T-SQL.

If I try to run this query as a pass-through query,

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField="Roger Carlson";

I'll get the following message:

clip_image002

Notice that the error is an "Invalid column name". That's because it thinks "Roger Carlson" is a column, not an explicit value.

 

Wednesday, May 15, 2013

What are the differences between Access SQL and T-SQL (SQL Server)?

Access SQL and SQL Server's T-SQL have more in common than differences. They share much the same structure, syntax, and many functions. And yet for all that, the differences can be frustrating. There are many times when an experienced Access user will know exactly how to do what they want in Access, but cannot figure out how to do the same thing in T-SQL.

This is especially true when trying to convert an Access query to a Pass-Through query. A Pass-Through query passes the SQL Statement on directly to the Server database (ie: SQL Server, Oracle, etc.). The problem is it must be in the syntax used by the Server database.

 

Basic Differences

I'll start by just listing the basics, but I'll spend some time over the next few months expanding on them. The following is certainly not exhaustive, but they are differences that cause most of the confusion when first trying to use T-SQL.

 

Delimiters

  Type

  Access

  T-SQL

  Numeric   no delimiter   no delimiter

  Text

  " or ' (quote or apostrophe)

  ' (apostrophe)

  Date

  # (pound sign)

  ' (apostrophe)

  Wildcard

  *

  %

  Delimited Identifiers   [..]   [..] or “..”

 

Distinct and DistinctRow

Both Access and SQL Server have the DISTINCT predicate which follows the SELECT, but T-SQL does not have DISTINCTROW.

 

Joins

Both Access and T-SQL use INNER JOIN for inner joins. However, for outer joins, Access uses LEFT JOIN and RIGHT JOIN, while T-SQL uses LEFT OUTER JOIN and RIGHT OUTER JOIN. T-SQL also has a FULL JOIN, which Access lacks. (Note: In the comments, Michal points out that T-SQL can also use LEFT JOIN and RIGHT JOIN.  He’s quite correct.)

 

Built-in Functions

Type

Access

T-SQL

Conditional

IIF()

CASE, IF-THEN-ELSE

Conversion

Cdate(), Clng(), Cint(), etc

CAST()

Formatting

Format()

CONVERT()

Nulls

Nz()

ISNULL()

Aggregates

Min

Max

First

Last

Sum

Avg

Count

No equivalent

MIN

MAX

No equivalent

No equivalent

SUM

AVG

COUNT

COUNT DISTINCT

Current Date

Date(), Now()

GETDATE()

Domain Aggregate Functions

DMax(), DMin, DCount, DLookup, etc

No counterpart. Must use subqueries.

String

InStr()

Trim()

Mid()

CHARINDEX

RTRIM and LTRIM

SUBSTRING

String Concatenation

&, +

+

 

Parameter Queries

Parameter queries as known in Access SQL cannot be converted directly to a Pass-through query. The most common way to simulate parameters is to build the Pass-Through query in VBA code and then execute it.

 

Great Features of T-SQL you should know about

 

Comments

Access does not allow comments in queries, but T-SQL DOES! This is a great feature of SQL Server. There are two methods of commenting:

  • two dashes ( -- ) comments out a single line
  • blocks of text bracketed by /*…*/ comments out multiple lines

 

Variables

Variables are another thing which Access does not have that T-SQL does. Variables provide another way to parameterize a T-SQL query.

 

Count Distinct

As mentioned in the Functions list, T-SQL has a COUNT DISTINCT aggregate function, which Access lacks. You can read more about it here: COUNT DISTINCT in Access

 

Multiple SQL Statement and Temporary Tables

These two things go hand in hand. In Access, a "query" can only have a single SQL Statement. But in T-SQL, you can execute multiple SQL Statements. You can even use a Make-Table query (SELECT..INTO) to create a temporary table, which can be used in subsequent SQL Statements. You can use this feature in an Access Pass-Through query.

 

Conclusion

There are, of course, many more differences between Access SQL and T-SQL. What I've concentrated on here, are those "gotchas" that crop up when trying to convert an Access query to a Pass-through query in T-SQL syntax. I'll discuss the details of these differences in future posts.

Wednesday, May 1, 2013

New Sample: DataDICTIONARY_DisplayControl_Crystal

By Crystal Long

Zip file contains 2 objects: 1 form and 1 module:

  • f_DataDICTIONARY_DisplayControl
  • mod_crystal_DataDICTIONARY_DisplayControl

You can find this sample here:
http://www.rogersaccesslibrary.com/forum/data-dictionary-display-control_topic610.html
Other Samples By Crystal:
http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html

How to Use this tool:

 
Import the DataDICTIONARY_DisplayControl form and module into a working database, then compile and save, then Open the form: f_DataDICTIONARY_DisplayControl

 

Overview

  1. View Data Dictionary for selected table 
  2. Go to Table Design view of selected table
  3. Open table Datasheet View of selected table
  4. Rename selected table
  5. See if there are text or memo fields where Unicode Compression is not set
  6. See an estimate of record width (sum of the data type sizes, taking compression into account)
  7. Change Display Control of selected fields:
    1. Combo and Listbox to Textbox
    2. Integer to Checkbox

Screen Shots

When you first open the f_DataDICTIONARY_DisplayControl form, you will not see much until you choose a table to look at.

Choose Table

 Menu: Data Dictionary, Display Control

 3 Lookup fields, Need Unicode Compression

Rename Tables 


 Rename Table with bad characters

List of Tables with New Name chosen

 

Delete Lookups

3 Lookup fields to change

Integer to Checkbox


Select Integer fields to set DisplayControl to Checkbox

Tuesday, April 23, 2013

How Do I Hide A Form But Leave It Running?

There are times when it is preferable to hide a form, in other words to have it open but invisible.
  1. Global Variables:
    There are circumstances under which global variables lose their values in Access. These circumstances are not common, but they happen often enough to be of concern. One way around this problem is to create a hidden form with unbound controls, each of which would hold the value of one of your global variables. So instead of using a global variable, you set and reference the value of a control on the form.
  2. Timer events:
    If you want run a process at a particular time or multiple times throughout the day, you can create a timer event in a form. This form must be open in order for the timer event to fire. So to keep this form active, but out of the way, you can hide it.
  3. Performance:
    Some forms take a long time to load. This may be because it has a large, bound dataset, or it may be a complex form with several subforms. Whatever the reason, once the form is loaded, it makes sense to not close it again.

There are several ways to hide a form. The one you use depends on what you're trying to accomplish.

If you're using the form for global variables or a timer event (or both), the simplest thing is to open it in Hidden mode. To do that, create a macro and name it AutoExec. (see How do I run a macro or code when the database first starts? ). The AutoExec macro is a special macro that will automatically execute when the database opens. In this macro, you'll want to choose the OpenForm action, name the form, and choose Hidden in the Window Mode property.

Like this:
Access 2010-2013
image
 
Access 2000-2007
If some of your forms take a long time to load, however, this is not a good method. It will appear as if Access has frozen while the form or forms load at Start Up. In such cases, it is preferable to create a Splash Screen. A Splash Screen is simply a form which tells the user that the database is opening and to wait. This gives the user feedback and reassures them that everything is functioning normally.

I'll discuss splash screens in a later post.

But once the form is open, it makes sense to hide it rather than close it. To do that, you can use a button. I usually use the button wizard because it creates the button's Onclick event code with error trapping automatically. I use the Form Close wizard, which has a single line of code:

DoCmd.Close

I will replace that with the following:

Me.Visible = False

The next time you "open" the form, it will simply make the already open form visible.   So instead of opening a Form, you can instantly by make the form visible and set the focus on it like this:

Forms("MyFormName").Visible = True

Forms("MyFormName").SetFocus