To improve collaboration within teams: adding notifications to your access-2013 Web applications

Er is een fout opgetreden bij het deserialiseren van de tekst in het antwoordbericht voor bewerking Translate. Het quotum voor de maximumlengte van tekenreeksinhoud (8192) is overschreden bij het lezen van XML-gegevens. Dit quotum kan verhoogd worden door de MaxStringContentLength-eigenschap op het XmlDictionaryReaderQuotas-object te wijzigen dat gebruikt wordt bij het maken van de XML-lezer. Regel 1, positie 9476.
Er is een fout opgetreden bij het deserialiseren van de tekst in het antwoordbericht voor bewerking Translate. Het quotum voor de maximumlengte van tekenreeksinhoud (8192) is overschreden bij het lezen van XML-gegevens. Dit quotum kan verhoogd worden door de MaxStringContentLength-eigenschap op het XmlDictionaryReaderQuotas-object te wijzigen dat gebruikt wordt bij het maken van de XML-lezer. Regel 1, positie 9425.

Access 2013 web apps are great for collaborating around a common set of data. When people work together in this way, the changes that one person makes to the data often require the attention of someone else.

For example, if a small team is working together to track and process maintenance requests, an Access app would be an ideal place to centrally track those requests and their status. Further, imagine that I process a maintenance request and assign it to another team member who needs to take action on it. But how does he or she know? Wouldn't it be great to be automatically informed? That's what notifications can do.

In this article, we'll describe a technique to build e-mail notifications into your Access 2013 web app using the power of SQL Server and a third-party service called Zapier.

In the above example of our maintenance request tracker, people need to get an e-mail when a maintenance request is assigned to them, letting them know of a required action. In the Maintenance Requests table, there's an Assigned To field that is a lookup to the Employees table. Whenever that field changes, an e-mail should be sent to the person to whom the Request is now assigned.

Building this notification functionality involves three steps, detailed further below:

Create a Messages table in the Access web app to store notification emails that you want to send.Create a data macro on the "Maintenance Requests" table that adds records to the Messages table whenever the "Assigned To" field changes.Configure Zapier to automatically monitor the "Messages" table and send an email whenever it detects a new record.

By the end, you'll have something that looks like this:

First, you'll need to create a Messages table to store information about the email notifications that you want to send. This table will need to have fields that specify all the details about the email message, including:

Click on Create > Table in the ribbon, then select the add a new blank table link. Add fields in the table designer so that it looks like this:

You want messages to be sent whenever the Assigned To field changes in a record on the Maintenance Requests table. Data Macros are the perfect tool for the job. Data macros are commands that run whenever records on a table change. For a given table, you can write macros that run when records are added, deleted, or updated. In our case, you'll want to write an On Update macro on the Maintenance Requests table.

To do this, first, open the Maintenance Requests table in the table designer, go to the Design tab, and click Events > On Update.

You'll only want to send the notification if the Assigned To field has changed. To do this, use an If statement that compares the old value of this field with its new value:

If that condition is true, you'll want the macro to create a record in the Messages table. Since you'll want this new Messages record to have the users' e-mail address in the To field, you need to first look that up and store it in a variable:

Finally, use this email address to create the message:

Then, this macro will add a new record to the Messages table whenever the Assigned To field of a record in the Maintenance Requests table changes.

Since, in this example, you also want people to get notifications when a new Maintenance Request is created and assigned to them, you'll create an After Insert macro that looks like this:

The only difference here is that we've changed the If expression at the beginning to run the macro if the Assigned To field is not blank.

Zapier is a third-party tool that you can use to build simple connections between one service, like an Access 2013 web app, and another, like email. Zapier calls these connections "Zaps." To get started building our zap, go to zapier.com and sign up for a free trial account, and click Create New Zap

You'll then find the services that you'd like to connect. In our case, we'll choose SQL Server > New Row as the "trigger" > Gmail > Send Email as the "action."

Click Create Zap and you'll be prompted to enter some more information about these connections. Start with the SQL account. You'll need to tell Zapier how to get permissions to look at the SQL database that powers your Access 2013 web app. Zapier will want to know things like server name, database name, username and password. To find out these things for your database, open it in Access, click File > Info > Manage Connections.

From this menu, you'll want to click From Any Location to disable firewall rules that would otherwise prevent a third-party service like Zapier from connecting. Then, click Enable Read-Only Connection which will generate a username and password that Zapier can use. To view these details, click View Read-Only Connection Information, which will show you something that looks like this:

Back in Zapier, you can copy this information into the Zap. Be sure to use port 1433 to connect:

Next, you'll want to tell Zapier which table it should monitor for changes and how it can tell which rows are new. We'll pick the Messages table, and tell it to examine the ID field. Since the ID field is a number that is automatically increased by one as new rows are added, this will be a reliable way for Zapier to tell the new rows from the old ones.

Next, you'll want to enter the credentials to your Gmail account so that Zapier can send the emails. Gmail is just used as a means of sending-the notifications can be sent to any inbox, such as Outlook, Yahoo, etc.

After your Gmail account is configured, you have to tell Zapier how to map the fields in the Messages table in SQL to the fields that Gmail expects. This part is pretty straightforward: the To field in Gmail maps to the To field in SQL, and so on:

That's it! You can easily test out your Zap by choosing "Click to load samples!" This will load the first row that Zapier finds in the Message table, and you can click to send a sample message.

The final step is to make your Zap live. This means that Zapier will run it every 15 minutes to check for new records in the Messages table, sending emails if it finds any.

You can try Zapier out for free for 15 days. Since SQL connections are a premium service for Zapier, you can sign up for a paid plan when the trial expires. See www.zapier for pricing details.

This article has outlined how to use notifications for a specific example, but there are many other situations where this capability will be useful. However, regardless of the scenario, you can follow the three steps above. We can't wait to hear about the cool things you're able to do with this technique!

View the original article here

How to improve Contribute Startup time?

How to improve Contribute Startup time? « All things contribute… The Adobe Contribute Blog function clearSearch() {document.search_form.s.value = "";} adobe.com      All things contribute… The Adobe Contribute Blog / How to improve Contribute Startup time?by Michael Dominic  

View the original article here

Improve your OneNote skills with Ribbon Hero 2

If formal training materials are not your thing, put your notes for a moment and check out Ribbon Hero 2, the sequel to our popular training game for Microsoft Office 2010.

Ribbon Hero 2 can the Office Web apps learning fun. It is also the first version includes OneNote, so even if you play before played with some of the other apps, have make sure that you are giving content to the new OneNote a whirl!

While playing Ribbon Hero 2, move the to see the mouse pointer across the screen what challenges await
and score to see how many points will upon successful completion of available tasks.

Need help with the search for a specific command on the Ribbon? Remember the steps for a particular process can't? Test what you think, that you want to know about OneNote? Ribbon Hero 2 can with all that help. Plus, you can travel through time, to measure points, and your success on the way. You will also meet with an old friend named Clippy, you surely remember that in previous versions of Office (although he was never part of OneNote).

Test your knowledge or fighting against employees and friends fun Download Ribbon heroand then check out our quick start guide to learn how to play.

RIBBON 2 is fun, it's free and it help you learn more about OneNote 2010 as may otherwise mastering.

--Michael C. Oldenburg

View the original article here

Improve coverage of the repeated data imports

Having summarize often large amounts of data that have been extracted from your corporate database? Access can be very helpful if you need to work with a huge stream of data. We have published a new article that shows how to the data easier to import, analyze, and summarize reports, even if you have not you experts consider access.

The article walks through the creation of an Access database that supports importing monthly sales data from a text file. So that you follow along as we explore this scenario, we have provided the sample data that was used to the article as well as with all imported data create a database file.

The scenario involves the following tasks:

Set up an import specification for access to the same data in future months import.Use the integrated Table Analyzer a normalized structure for which to create data.Create a number of append the incoming data in the normalized structure is analysed.Write a macro that import automates the data.Finally, create and distribute reports that summarize the data.

The work this month to import to normalize report, and automate the process will in future months pay when you create the reports take only a few seconds time!

The article is only one possible solution for this scenario. Have you used access to solve a similar problem? Leave a comment and let us know!

--Mike Stowe

View the original article here

Power tip: Improve the security of the database connections

Translate Request has too much data
Parameter name: request
Translate Request has too much data
Parameter name: request

This post about making ODBC connections more secure is provided by Access MVP Ben Clothier.

Now and then, we come to a point where we decide to upsize an Access database to a server-based RDBMS. The reasons for upsizing can vary, but a common theme is security. With a server-based RDBMS, we have more options for security than when we are working with a file-based data source. Even if security wasn’t the reason for upsizing, we do have an obligation to ensure that we develop a “good citizen” application and protect the company’s assets contained in the RDBMS. When our RDBMS supports Windows authentication (e.g., SQL Server, Oracle, FireBird), we have it easy – no username or password needs to be stored. But unfortunately, we don’t always have Windows authentication available, and even when it is supported, it may not be practical. This article focuses on the case where we have to pass in a username and password as a part of the connection string and we want to do so in a secure manner. The article also assumes we are using ODBC and DAO.

By default, Access offers to set up a Data Source Name (DSN) when we want to create a new linked table. As a matter of development, the DSN is quite convenient, giving us a graphic method to quickly build and specify an ODBC connection string without having to remember all of the syntax and parameters. Unfortunately, when the time comes for deployment, using DSNs has a host of problems, especially security. Let’s quickly review a few of the security holes associated with using DSNs.

 
Figure 1

When we link a new ODBC table, Access defaults to not saving the password when we select a DSN. We have to select Save Password to do so, as seen in Figure 1. Recent versions of Access warn that the password will be saved as plaintext. If we ignore the warning and save the password anyway, the password is saved in the Access system table, MSysObjects, as plaintext. You can see an example of this in Figure 2.

 
Figure 2

So, let’s elect not to save the password. Does that resolve the problem? Unfortunately, not necessarily. When we create a DSN, the data used to build connection strings is stored in a registry entry. Let’s see how the DSN for a MySQL database is structured. You can find it in the registry node shown in Figure 3.

 
Figure 3

Here, the password is stored in the registry as plaintext, so even though we didn’t check the Save Password option, and our Connect wouldn’t list Password anywhere in the database itself, it’s still there for the taking. Yikes. Even worse, storing passwords is a vendor-specific implementation. The PostgreSQL ODBC driver also stores passwords as plaintext, but the Firebird ODBC driver always encrypts the password. SQL Server ODBC drivers will never try to store a SQL Server authentication password, no matter what. Considering the large number of different ODBC drivers, including third-party drivers for the same RDBMS, I’d rather not have to track the specific oddities of each ODBC driver and accommodate each one of them as I move from RDBMS to RDBMS.

We can say, “Let’s not define the password in the DSN, requiring the user to complete the connection at runtime.” Indeed, we can do that, but we now run into a new problem that also plagues the SQL Server ODBC driver. Every time a user opens an Access object that either is, or depends on, an ODBC object, the user will be prompted to enter their password for that ODBC connection. This does not make for a great user experience, not to mention several additional issues raised by showing users the dialog box for configuring the ODBC driver, which exposes options you may not want users to tinker with.

So in short, using DSNs may be convenient, but it can be problematic to implement in a secure manner. Moreover, because the specifics of implementation vary by ODBC driver, there is no good general solution for designing a secure DSN. Therefore, we should consider DSN-less connection strings. Though DSN-less connections per se are not necessarily more secure, the additional security comes from the fact that they can be disposable, which becomes very important, as we’ll see shortly.

Access MVP Douglas J. Steele has posted a sample of building a DSN-less connection in VBA for a SQL Server backend. Doug recently updated the material, incorporating modifications suggested by George Hepworth, another Access MVP, to support both trusted connections and SQL Server authentication. This document provides us an excellent starting point to learn how we can build DSN-less connections. We’ll also learn how we can ensure that all linked tables will be updatable by adding a unique index local to Access.

There is an interesting behavior in Access we want to take advantage of. When Access opens an ODBC connection, it caches that connection. Any subsequent ODBC objects that happen to match on three parameters—ODBC driver, server, and database—will reuse that cached connection. This means we don’t have to specify the full connection string for all ODBC objects each time. We only need to supply the complete connection string once at startup and store only the incomplete connection string. We can then leave it up to Access to match subsequent ODBC objects to that cached connection string. This helps immensely in simplifying the security setup.

During application startup, we want to call a routine that will create a temporary query that contains the complete connection. Then we can discard that query at end of the routine. Procedure InitConnect demonstrates this crucial step.

Public Function InitConnect(UserName As String, Password As String) As Boolean
' Description:  Should be called in the application's startup
'               to ensure that Access has a cached connection
'               for all other ODBC objects' use.
On Error GoTo ErrHandler

    Dim dbCurrent As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
'

    strConnection = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};" & _
"Server=" & ServerAddress & ";" & _
"Port=" & PortNum & ";" & _
"Option=" & Opt & ";" & _  'MySql-specific configuration
"Stmt=;" & _
"Database=" & DbName & ";"
Set dbCurrent = DBEngine(0)(0)
Set qdf = dbCurrent.CreateQueryDef("")
With qdf
.Connect = strConnection & _
"Uid=" & UserName & ";" & _
"Pwd=" & Password
.SQL = "SELECT CURRENT_USER();"
Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
InitConnect = True

ExitProcedure:
On Error Resume Next
Set rst = Nothing
Set qdf = Nothing
Set dbCurrent = Nothing
Exit Function
ErrHandler:
InitConnect = False
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "InitConnect"
Resume ExitProcedure
Resume
End Function

Even though we discard the query at the end of the procedure, Access holds onto the connection it created behind the scenes. As far as I know, there is no programmatic access to this cached connection.  However, once it exists, you can open any other ODBC objects that match on driver, server, and database parameters and interact with it as if it did have the complete connection string. Figure 4 shows a table opened without any additional prompt after we run the procedure at startup. Even though it does not have the complete connection string, Access used the cached connection created by InitConnect so there’s no need to prompt the user for missing information.

 
Figure 4

With this technique, you can now implement a custom login form to have users enter their username and credentials at runtime and call the InitConnect procedure. That information is passed to the code snippet illustrated above to create the connection. You now have an application that does not store the password anywhere, inside or outside the file. Although that won’t stop your users from penciling their password on a note taped to their monitor, you can be confident that, if the file was copied and taken outside the building, it would be useless; more so if the connection requires being within a certain network. (Certain IP addresses such as 192.168.*.* are not world-accessible and require a physical connection to a particular DHCP server or at least VPN or similar tunneling software.)

Figure 5

As Figure 5 shows, we should remember that linked tables aren’t the only objects to have a Connect property –passthrough queries can have a Connect property that also needs to be maintained. Fortunately, those queries can also share the cached connection so there should be no reason for us to store a complete connection string for them. We only need to ensure that all passthrough queries have the minimum of three required parameters and we’re done.

There is a hole we need to close – the cached connection does not close when we close the database but do not quit Access. In other words, if a user closed your application and opened their own databases, they wouldn’t be prevented from accessing the same ODBC objects using the cached connection. Fortunately, that is fairly easy to remedy – when the last form closes, forcing Access to quit will usually ensure that the cached connection is properly disposed of at the end of your application’s session. This guarantees that users will be always required to explicitly log in when they open Access and attempt to access the ODBC sources.

When you’ve implemented an incomplete connection string, the file no longer can be simply copied around. If someone attempts to bypass the login routine, the tables and queries will not be able to connect. Double-clicking on those objects would simply give them the ODBC driver’s dialog box asking them to complete the connection. Because the password isn’t stored anywhere, they can’t just look under the doormat. Though encrypting the password is an option, it’s much more preferable not to store any password and require the user to supply it just in time. Encrypting the password and storing it in the file is analogous to putting your confidential information in a small safe and leaving it out in your front yard. Anybody can just take your small safe somewhere else and take as much time as they want to crack the password. True security comes in not having anything available for taking and blocking access rather than making access harder.

For additional security options, you may want to look into creating an MSysConf table on your RDBMS server to ensure that Access won’t offer to save passwords locally. For those who do not want to have users log in and enter a password, Access MVP Tom van Stiphout’s example of using Active Directory to provide user-level access control for Access forms can be used as a model for implementing authentication via third-party server (e.g., Active Directory). If you're just getting started with connect strings, you can go to Carl Prothman’s site and ConnectionStrings to find excellent repositories of complete connection strings, though you may want to go to your ODBC driver’s vendor for the most up-to-date and complete documentation. Finally, check out George Hepworth's site for lots of tips, demos, and downloads.

Note: If we create a File DSN instead of a System or User DSN, the data will be stored in a file rather than in a registry entry. However, File DSNs still store the data in plaintext, similar to what we saw in the registry. So the concerns for security apply equally to File DSNs and System/User DSNs.

--Ben Clothier

Ben Clothier has been an Access MVP since 2009. He is an independent contractor working with J Street Technology and Advisicon and was Technical Editor for the Access 2010 Programmer's Reference.

View the original article here