tag:blogger.com,1999:blog-47601781734050888462024-03-12T23:21:21.415-07:00Roger Jennings' Access BlogA blog about my "Microsoft Access 2010 In Depth" book (QUE Publishing, 2010) and advanced Access data management and programming techniques.Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.comBlogger95125tag:blogger.com,1999:blog-4760178173405088846.post-5586436748062970862013-05-29T10:57:00.001-07:002013-05-29T10:57:55.090-07:00Replacing Access 2010’s TAPI AutoDialer with a No-Frills Dialer using the Hayes ATDT Command<h4>Background</h4> <p>Adding an AutoDialer button with the Command Button Wizard creates an embedded macro that runs Windows’ built-in Dialer.exe, a Telephone API (TAPI) application that reads configuration data and dialing rules from Control Panel’s Phone and Modem tool. By default, the macro transfers the phone number from the preceding control in the tab order to a text box. Clicking the button opens a dialog that lets the user confirm (and, optionally, edit the number), and then click OK to dial or Cancel to abort it:</p> <p><a href="http://lh5.ggpht.com/-btAPUWTwd_4/UaZBWqaAyXI/AAAAAAABDOg/unaHN0a6Pkc/s1600-h/image2.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; display: block; padding-right: 0px; border-top-width: 0px; margin-right: auto" border="0" alt="image" src="http://lh3.ggpht.com/-XLKPIC9Z-Nk/UaZBXQYH-dI/AAAAAAABDOo/w3orEENwod4/image_thumb.png?imgmax=800" width="274" height="32"></a></p> <p><a href="http://lh5.ggpht.com/-Bh_YkIrW6ZE/UaZBX3xvMVI/AAAAAAABDOw/DaNtU5t_E2I/s1600-h/image5.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; display: block; padding-right: 0px; border-top-width: 0px; margin-right: auto" border="0" alt="image" src="http://lh4.ggpht.com/-0KarotUOq0c/UaZBYr-rkHI/AAAAAAABDO4/49W2qioZeZI/image_thumb1.png?imgmax=800" width="346" height="140"></a></p> <p><a href="http://lh4.ggpht.com/-OdObZL94x6k/UaZBZeVMziI/AAAAAAABDPA/_a_IRUv5BAw/s1600-h/image11.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; float: right; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" align="right" src="http://lh4.ggpht.com/-lctIc7u22dE/UaZBZ4L2CiI/AAAAAAABDPI/atKw3la-T30/image_thumb3.png?imgmax=800" width="44" height="27"></a>Clicking OK also activates an AutoDialer icon in the taskbar: </p> <p>Clicking the taskbar icon opens the following two dialogs anchored ot the top left of the screen:</p> <p><a href="http://lh4.ggpht.com/-IoMyMROOqpU/UaZBat6zZFI/AAAAAAABDPQ/Qm-B91Mb9kY/s1600-h/image14.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; display: block; padding-right: 0px; border-top-width: 0px; margin-right: auto" border="0" alt="image" src="http://lh5.ggpht.com/-KFiy2dZC-Y4/UaZBbVY9kRI/AAAAAAABDPY/MVSGKE0AmEY/image_thumb4.png?imgmax=800" width="456" height="477"></a></p> <p>Most developers and their clients will consider these two dialogs superfluous and confusing to end users. What’s worse, there appears to be no readily apparent method for preventing the Phone Dialer dialog from appearing when the user just wants to hang up. Users expect to “hang up” simply by placing the handset on hook.</p> <p>Extensive searching with Bing and Google disclosed no free, turnkey code for creating a simple autodialer by executing the Hayes <font face="Courier New">,,ATDT+1###-###-####;H</font> command to dial the number and hang up.</p> <h4>Creating the No-Frills Dialer with David M. Hitchner's modCOMM Library</h4> <p>Access 2010’s VBA 7 doesn’t have a feature that emulates VB 6’s MSComm control, but David M. Hitchner has thoughtfully posted a VB module containing a collection of routines to perform serial port I/O without using the <i>Microsoft Comm Control</i> component <a href="http://www.thescarms.com/vbasic/CommIO.aspx">here</a>. His CommIO.zip file contains a CommIO.bas and SampleCode.txt files. Rename CommIO.bas to CommIO.txt, open it in Notepad and paste it into a new module.</p> <p><strong>Important:</strong> Be sure to add <font face="Courier New">PtrSafe</font> to CommIO’s <font face="Courier New">Declare</font> statement if you’re running 64-bit Access or either 32-bit or 64-bit Access under Windows 8 or Server 2012, as in:</p> <p><font face="Courier New">Declare <strong>PtrSafe</strong> Function GetCommModemStatus Lib "kernel32" _<br> (ByVal hFile As Long, lpModemStat As Long) As Long</font></p> <p>Here’s my code for a procedure that accepts arguments for the form name, control name of the text box with the phone number, and name of the dialed party, opens this message box:</p> <p><a href="http://lh6.ggpht.com/-VAVXs2UmOwk/UaZBcFJvy6I/AAAAAAABDPg/1EehhujYCTg/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; display: block; padding-right: 0px; border-top-width: 0px; margin-right: auto" border="0" alt="image" src="http://lh3.ggpht.com/-vis5S8gDwMI/UaZBdAigC5I/AAAAAAABDPo/_qWDixn0A6M/image_thumb%25255B1%25255D.png?imgmax=800" width="497" height="199"></a>and sends the COM port number (2) and ATDT command text to a subprocedure based on David’s SampleCode.txt example:</p><pre>Public Sub AutoDial(strForm As String, strControl As String, strParty As String)<br> ' Dial with Hayes-compatible command set<br> On Error GoTo ErrorHandler<br> Dim Phone As Control<br> Dim Party As Control<br> Dim strNumber As String<br> Dim strDialCmd As String<br> Dim strMsg As String<br> Dim varRetVal As Variant<br> Set Phone = Forms(strForm).Controls(strControl)<br> Set Party = Forms(strForm).Controls(strParty)<br> varRetVal = Application.SysCmd(acSysCmdClearStatus)<br> If Len(Phone.Value) = 12 Then<br> If Left(Phone.Value, 3) = "510" Then<br> ' Local call<br> strNumber = Mid(Phone.Value, 5)<br> Else<br> ' 1-AAA-PPP-NNN<br> strNumber = "1-" & Phone.Value<br> End If<br> strMsg = "Click OK to dial " & strNumber<br> If Len(Party.Value) > 0 Then<br> strMsg = strMsg & " for " & Party.Value<br> End If<br> strMsg = strMsg & " and pick up handset within 10 seconds."<br> strMsg = strMsg & vbCrLf & vbCrLf & "Or click Cancel to abort the call."<br> <br> strDialCmd = ",,ATDT" & strNumber & ";H"<br> varRetVal = Application.SysCmd(acSysCmdSetStatus, "Dialing " & strDialCmd)<br> If MsgBox(strMsg, vbOKCancel + vbQuestion, "Pacific Park Plaza Autodialer") = vbOK Then<br> ModemDialer 2, strDialCmd & vbCrLf<br> End If<br> Else<br> MsgBox "Phone number not valid.", vbOKOnly + vbExclamation, "Autodialer Error"<br> End If<br> Set Phone = Nothing<br /><p>ExitPoint:<br> Exit Sub<br> <br>ErrorHandler:<br> MsgBox Err.Description, vbExclamation, "Unexpected Autodialer Error"<br> Resume ExitPoint<br>End Sub</p></pre><br /><p>You must specify the modem’s COM port to correspond with Control Panel’s Phone and Modem tool in the first argument of the above <font face="Courier New">ModemDialer 2, strDialCmd & vbCrLf</font> instruction:<br></p><br /><p><a href="http://lh3.ggpht.com/-B5LH9AiU6VU/UaZBd2DVKqI/AAAAAAABDPw/JtsSUd_JKfQ/s1600-h/image%25255B6%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; border-left: 0px; display: block; padding-right: 0px; margin-right: auto" border="0" alt="image" src="http://lh5.ggpht.com/-nkSq3O-9akg/UaZBekRMnpI/AAAAAAABDP4/t4UqzvHpRAY/image_thumb%25255B2%25255D.png?imgmax=800" width="415" height="461"></a></p><br /><p>Here’s the modified procedure for invoking CommIO functions:</p><pre>Sub ModemDialer(intPortID As Integer, strData As String)<br> ' Truncated version of David M. Hitchner's SampleCode.txt from modCOMM.zip<br> ' Requires installation of David M. Hitchner's modCOMM library downloaded from<br> ' <a href="http://www.thescarms.com/vbasic/CommIO.aspx">http://www.thescarms.com/vbasic/CommIO.aspx</a><br> On Error GoTo ErrorHandler<br> ' intPortID = 1, 2, 3, 4 for COM1 - COM4<br> ' strData = Phone number with area code removed for local numbers and<br> ' +1 prepended for out-of-area numbers<br> Dim lngStatus As Long<br> Dim lngSize As Long<br> Dim strError As String<br> Dim varRetVal As Variant<br /><p> ' Initialize Communications<br> lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _<br> "baud=9600 parity=N data=8 stop=1")<br> DoEvents<br> <br> ' Write data to serial port.<br> lngSize = Len(strData)<br> lngStatus = CommWrite(intPortID, strData)<br> If lngStatus = lngSize Then<br> varRetVal = Application.SysCmd(acSysCmdSetStatus, strData)<br> Else<br> varRetVal = Application.SysCmd(acSysCmdSetStatus, "Error: " & strData)<br> End If<br> DoEvents<br> <br> ' 10 seconds to pick up phone; see procedure below<br> Pause 10<br> varRetVal = Application.SysCmd(acSysCmdClearStatus)<br> Call CommClose(intPortID)<br> <br>ExitPoint:<br> Exit Sub</p><br /><p>ErrorHandler:<br> MsgBox Err.Description, vbInformation + vbOKOnly, "Error Opening COM Port"<br> Resume ExitPoint<br>End Sub</p></pre><br /><br /><div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:580c6c0b-f962-4f6f-ad52-7fd599625130" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access" rel="tag">Microsoft Access</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Access+Autodialer" rel="tag">Access Autodialer</a>,<a href="http://technorati.com/tags/Autodialer" rel="tag">Autodialer</a>,<a href="http://technorati.com/tags/Modem+Dialers" rel="tag">Modem Dialers</a>,<a href="http://technorati.com/tags/Modem" rel="tag">Modem</a>,<a href="http://technorati.com/tags/Hayes" rel="tag">Hayes</a>,<a href="http://technorati.com/tags/ATDT" rel="tag">ATDT</a>,<a href="http://technorati.com/tags/CommIO" rel="tag">CommIO</a>,<a href="http://technorati.com/tags/MSComm" rel="tag">MSComm</a>,<a href="http://technorati.com/tags/TAPI" rel="tag">TAPI</a>,<a href="http://technorati.com/tags/Dialer.exe" rel="tag">Dialer.exe</a>,<a href="http://technorati.com/tags/VB6" rel="tag">VB6</a>,<a href="http://technorati.com/tags/VBA" rel="tag">VBA</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-47615031122350676182013-04-13T11:33:00.001-07:002013-04-13T11:33:07.576-07:00Received Author Copies of Chinese Translation of Access 2010 In Depth<p>On April 12, 2013, I received two copies of the <a href="http://www.sdcbs.slww.net/shop/result.asp?id=21881&jsjh=">Chinese translation of Access 2010 In Depth</a> from QUE Publishing:</p> <p><a href="http://lh4.ggpht.com/-3I4KBsdt8Sg/UWmkq-onhmI/AAAAAAABB4U/bCJBvCcwv04/s1600-h/image%25255B15%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; border-left: 0px; display: block; padding-right: 0px; margin-right: auto" border="0" alt="image" src="http://lh4.ggpht.com/-G6f3mMo1RAs/UWmkscQJZ4I/AAAAAAABB4c/_yJnnARQyqI/image_thumb%25255B9%25255D.png?imgmax=800" width="352" height="456"></a></p> <p>Here are the details from the Chinese publisher’s Web site:</p> <p><a href="http://lh3.ggpht.com/-LJRzz_qwEHM/UWmkvqB7etI/AAAAAAABB4k/NWYbYAzr4Zo/s1600-h/image%25255B9%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: none; padding-top: 0px; padding-left: 0px; margin-left: auto; border-left: 0px; display: block; padding-right: 0px; margin-right: auto" border="0" alt="image" src="http://lh5.ggpht.com/-UdB5tTbc6DQ/UWmkzHsfAxI/AAAAAAABB4s/MPVHYjVgGdE/image_thumb%25255B5%25255D.png?imgmax=800" width="562" height="742"></a></p> <p>And Microsoft Translator’s version (with related book titles removed):</p> <p><a href="http://lh3.ggpht.com/-EJ8hmZPyGBo/UWmk2CBN76I/AAAAAAABB40/ZB6GZWCZVsE/s1600-h/image%25255B10%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: right; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" align="right" src="http://lh5.ggpht.com/--zz3FSpUDNg/UWmk4RuaOlI/AAAAAAABB48/jsNwjVaYZ1s/image_thumb%25255B6%25255D.png?imgmax=800" width="562" height="606"></a></p> <blockquote> <p> </p></blockquote> <p>I especially like the <font face="Courier New">[Beauty]</font> prefix, but it’s undeserved. Sea freight from China is a bit slow, but I’m surprised it took the copies more than six months to arrive.</p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:8f2a4d85-334d-46b7-94c0-5493d09bc784" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010+In+Depth" rel="tag">Microsoft Access 2010 In Depth</a>,<a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/QUE+Publishing" rel="tag">QUE Publishing</a>,<a href="http://technorati.com/tags/Pearson+Education" rel="tag">Pearson Education</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-4046994972726418802013-03-17T11:25:00.001-07:002013-03-17T11:30:44.398-07:00Creating a Working DSNless ODBC Connection String with SQL Server Native Client v11.0 for SQL Server 2012<p><a href="http://lh5.ggpht.com/-aciLsJPgFCU/UUYKkDUKidI/AAAAAAABATE/GRcQK7qT6VY/s1600-h/OakLeafLogoMVP100px%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="OakLeafLogoMVP100px" border="0" alt="OakLeafLogoMVP100px" align="right" src="http://lh3.ggpht.com/-OtYyaeeqQF4/UUYKk4kdkKI/AAAAAAABATM/o9D4_zAJhsA/OakLeafLogoMVP100px_thumb.png?imgmax=800" width="100" height="100"></a>When completing a Microsoft Access 2010 Resident Information Management System (RIMS) project for the Home Owners Association of an East Bay high-rise condominium, the last step in preparing the runtime installation package was generating a DSNLess connection to SQL Server 2012 Express running under SQL Server 2012 Standard Edition. I used a System DSN with the SQL Native Client v11 driver during testing on my client’s new Active Directory network.</p> <p><a href="http://lh6.ggpht.com/-2sqo28UcKws/UUYKlpaJbXI/AAAAAAABATU/FP6KUBJcFTs/s1600-h/image%25255B11%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-BU2-Fp9I-Ek/UUYKmGdfmkI/AAAAAAABATc/hD0G5MeYgUw/image_thumb%25255B5%25255D.png?imgmax=800" width="100" height="97"></a>The SQL Server chapters of my <em>Special Edition Using Microsoft Access</em> (since the Access 2005 edition) and <em>Microsoft Access In Depth</em> books for QUE Publishing have included VBA source code for a <font face="Courier New">ChangeServer</font> class module, which generates DSNLess connection strings from System Data Source Names (DSNs) for the classic SQL Server Driver (SQLSVR32.dll, v6.01.7601.17154 for SQL Server 2012). SQL Server Native Client (SQLNCLI11.dll, v2011.110.3000.0 for SQL Server 2012) is now the preferred ODBC driver for all client applications, including Microsoft Access.</p> <p><a href="http://lh5.ggpht.com/-E87qFVkUoJU/UUYKm9dzEUI/AAAAAAABATk/l6jaj0AZMr8/s1600-h/image%25255B16%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/-oUsok9qHK30/UUYKnbktAGI/AAAAAAABATs/ZcFJc9Gw8II/image_thumb%25255B8%25255D.png?imgmax=800" width="160" height="105"></a>I had customized the <font face="Courier New">ChangeServer</font> module for the SQL Native Client (SQLNCli) for applications using Access 2007 and 2010 front ends with SQL Server 2005, 2008 and 2008 R2. When I wrote code to change the <font face="Courier New">SERVER</font> clause of the connection string to <font face="Courier New">DRIVER={SQL Native Client 11.0};</font>, and ran the <em>Client</em>.accdr file with the new <font face="Courier New">ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=RIMS;TABLE=dbo.Owners; Trusted_Connection=YES</font> connection string, I received the following malformed error message:</p> <p><a href="http://lh6.ggpht.com/-lw6I4pawG9s/UUYKn5PY1sI/AAAAAAABAT0/IXcTJO0rIQ4/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-qoC0f_CvWBk/UUYKoVn-r6I/AAAAAAABAT8/FwKix8W4PyY/image_thumb%25255B1%25255D.png?imgmax=800" width="400" height="178"></a></p> <p>Previous SQL Server versions hadn’t objected to similar connection strings, so after unsuccessfully searching for the issue with Bing and Google, <a href="http://social.technet.microsoft.com/Forums/en-US/sqldataaccess/thread/2ba40b98-ad93-44b3-8985-4a5fe7775218">I started a How to Create a Working DSNless SQL Server 2012 Connection String?</a> thread in the <a href="http://social.technet.microsoft.com/Forums/en-US/sqldataaccess/threads">SQL Server Data Access</a> forum.</p> <p>Dan Guzman, SQL Server MVP, <a href="http://www.dbdelta.com">http://www.dbdelta.com</a> offered the following suggestion:</p> <blockquote> <p><a href="http://lh6.ggpht.com/-X3bQA4arWOU/UUYKo-a4kDI/AAAAAAABAUE/vSpRGYiGG88/s1600-h/image%25255B21%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/--qq59CUKOPU/UUYKpsjcRxI/AAAAAAABAUM/6bO3lJbbS_A/image_thumb%25255B11%25255D.png?imgmax=800" width="500" height="194"></a></p></blockquote> <p>Dan was right, but changing the the connection string to <font face="Courier New">ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=RIMS; TABLE=dbo.Owners;Trusted_Connection=YES</font> <font size="2">threw a “missing field” error, which indicated the table was missing a primary key field. (It wasn’t.)</font></p> <p><font size="2">It turns out that specifying the table name with a <font face="Courier New">TABLE=</font> clause no longer works. You must execute a <font face="Courier New"><em>tdfToAppend</em>.SourceTableName = "dbo.<em>TableName</em>"</font> instruction.</font></p> <p><a href="http://lh5.ggpht.com/-gsPBCrdsCh8/UUYKqUc9jgI/AAAAAAABAUU/VwA-IZEr5Rg/s1600-h/image%25255B20%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-HtzRZgKRFSo/UUYKqzLc7yI/AAAAAAABAUc/1VIrSYdtxpU/image_thumb%25255B10%25255D.png?imgmax=800" width="100" height="61"></a><font size="2">You can download the updated <font face="Courier New">ChangeServer</font> class module from my SkyDrive account by clicking <a href="https://skydrive.live.com/redir?resid=7E9D5282B93B6501!1738">here</a>.</font></p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:e7e41cef-53c3-4fb1-9c0d-acd400e7c2b8" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access" rel="tag">Microsoft Access</a>,<a href="http://technorati.com/tags/SQL+Server" rel="tag">SQL Server</a>,<a href="http://technorati.com/tags/SQL+Native+Client" rel="tag">SQL Native Client</a>,<a href="http://technorati.com/tags/SQL+Server+Native+Client" rel="tag">SQL Server Native Client</a>,<a href="http://technorati.com/tags/SQL+Server+Native+Client+v11" rel="tag">SQL Server Native Client v11</a>,<a href="http://technorati.com/tags/ODBC" rel="tag">ODBC</a>,<a href="http://technorati.com/tags/ODBC+drivers" rel="tag">ODBC drivers</a>,<a href="http://technorati.com/tags/ODBC+Connection+Strings" rel="tag">ODBC Connection Strings</a>,<a href="http://technorati.com/tags/ODBC+DSNLess+Connection+Strings" rel="tag">ODBC DSNLess Connection Strings</a>,<a href="http://technorati.com/tags/Runtime+Access" rel="tag">Runtime Access</a>,<a href="http://technorati.com/tags/Access+Runtime" rel="tag">Access Runtime</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-35736782052667473432012-11-07T11:56:00.001-08:002012-11-07T11:56:06.018-08:00Microsoft Access 2013: A Cool New Rapid Development Tool for the Cloud<p><strong>Andrew Steigmeier</strong> and <strong>Gregory Leake</strong> posted <a href="http://blogs.msdn.com/b/windowsazure/archive/2012/11/06/microsoft-access-2013-a-cool-new-rapid-development-tool-for-the-cloud.aspx">Microsoft Access 2013: A Cool New Rapid Development Tool for the Cloud</a> to the Windows Azure blog on 11/6/2012:</p> <blockquote> <p><a href="http://lh6.ggpht.com/-bgyBJ3XdN9c/UJq8UfTiNOI/AAAAAAAAzQc/n4Ubmf_VdiU/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-Pl3EyZvf2Bs/UJq8ms0SwEI/AAAAAAAAzQk/1uS8BNuGNd0/image_thumb%25255B1%25255D.png?imgmax=800" width="70" height="69"></a>There are many frameworks for developers that are a good fit for Windows Azure SQL Database, and for that matter on-premises SQL Server 2012. In addition to the .NET Framework with support for languages such as C# and VB.NET, new JDBC, PHP and Node.JS drivers for SQL Database and SQL Server 2012 offer Java, PHP and Node developers other great language options for building data-driven applications based on Windows Azure SQL Database. In this post I want to make DBAs and others aware of a cool new way to rapidly build applications on SQL Database (or SQL Server 2012) <strong>without writing code</strong>. And this tool is in fact part of the new Microsoft Office 2013 suite: <strong>Microsoft Access 2013</strong>! <p><a href="http://lh5.ggpht.com/-FwSF0uxh4QQ/UJq8tjfsPTI/AAAAAAAAzQs/5pWBP0rVOuw/s1600-h/image%25255B8%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-iz-LavLK3tk/UJq8zmKFVdI/AAAAAAAAzQ0/Z3bwB4w6Q7Q/image_thumb%25255B4%25255D.png?imgmax=800" width="120" height="108"></a>With SharePoint Online (hosted in the cloud with an Office 365 subscription) or on-premise SharePoint Server 2013 you get the best collaboration for your organization or department. A big part of this is providing a central place (such as an internal Team Site) that’s scalable enough to organize and manage all your information assets, including your business applications such as those created in Microsoft Access. Remember that in the previous version of Microsoft Access (MS Access 2010), when you created a web application on SharePoint, the tables in your database were stored as <strong>SharePoint lists</strong> on the site that housed the application. When you use Access 2013 to create a cloud application on SharePoint (via automatic Office 365 hosting), Access Services create either a <strong>SQL Server </strong>or <strong>Windows Azure SQL Database</strong> that houses all of your Access objects. This new architecture increases performance and scalability; it also opens up new opportunities for professional SQL developers to extend and work with the data in Access applications using other tools and frameworks. <h6>How it Works</h6> <p>When you create an application in Access 2013, you'll choose a SharePoint site where you want it to live. Your application can be accessed, managed, or uninstalled from this site just like any other SharePoint application. In the process of creating your application in SharePoint, we provision a SQL Server database that will house all of the objects and data that your application requires. The tables, queries, macros, and forms are all stored in this database. Whenever anyone visits the app, enters data, or modifies the design, he'll be interacting with this database behind the scenes. If you create an app in Office 365, the database is created in Windows Azure SQL Database. If you create an app on a SharePoint server that your company hosts, Access will create the database in the SQL Server 2012 installation that was selected by your SharePoint administrator. In either case, the database created is specific to your app and is not shared with other apps. <p>As you build your app, you can add tables, queries, views, and macros to deliver the functionality you and your users need. Here's what happens in the database when you create each of these objects: <h6>Tables</h6> <p>When you add table to your Access app, a SQL Server table is created in the database. This table has the same name you gave it in Access, as do the fields you create in the client. The data types that are used in the SQL Server database match the types you would expect: text fields use nvarchar; number fields use decimal, int or float; and image fields are stored as varbinary(MAX). <p>Consider the following table in Access: <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/8400.Table-1.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/350x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/8400.Table-1.png"></a> <p>The resulting table in SQL Server looks like this: <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/0743.Table-2.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/350x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/0743.Table-2.png"></a> <h6>Queries</h6> <p>When you add a query to your app, Access creates a SQL Server view (or a table-valued function (TVF), if your query takes parameters). The name of the view or TVF matches the name you used in Access. We even use formatting rules when generating the T-SQL, so if you view the definition directly in SQL Server, it will be easy to understand. <p>This is a query designed in Access: <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/8765.Table-3.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/350x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/8765.Table-3.png"></a> <p>It is stored as a formatted statement in SQL Server: <p><font face="Courier New">CREATEVIEW[Access].[MyQuery]<br>AS<br>SELECT<br>[MyTable].[ID],<br>[MyTable].[String Field],<br>[MyTable].[Date Field]<br>FROM<br>[Access].[MyTable]<br>WHERE<br>[MyTable].[Date Field]>DATEFROMPARTS(2012, 7, 16</font>) <h6>Data Macros</h6> <p>Data macros come in two flavors: event data macros and standalone macros. <p>You can create event data macros by opening a table in design view and clicking on any of the Events buttons in the Table ribbon. <p>Event data macros are implemented on SQL Server as AFTER triggers on the table to which they belong. <p>You can create a standalone macro from the Home ribbon by clicking the Advanced button in the Create section and choosing Data Macro from the list of items. This type of macro can take parameters and is persisted as a stored procedure in SQL Server. <h6>Views</h6> <p>Views in Access 2013 are the parts of your app that display your data in the browser—database experts might call them forms. They are also stored in the database. Since they are HTML and JavaScript rather than SQL objects, they are stored as text in the Access system tables <h6>SQL Server Schemas</h6> <p>Within the database, Access makes use of three separate SQL Server schemas: Access; AccessSystem; and AccessRuntime. <p>The <strong>AccessSystem</strong> schema contains system tables that store the definitions of each object in a format that Access Services understands, as well as bits and pieces of information that are necessary in order for the item to work well in the runtime or design time surface. <p>The <strong>Access</strong> schema contains all of the tables, queries, and macros created by you, the app designer. Everything in this schema is the implementation of the objects you designed in SQL Server. <p>The <strong>AccessRuntime</strong> schema contains a number of items that we use in Access Services to optimize the runtime behavior of your application. <h6>So What?</h6> <p>You might be wondering why these details are important. For some users, the only visible effect of the new SQL Server back-end will be increased speed and reliability. They don't need to worry about the technical details. More advanced users, though, can directly connect to the SQL Server or Windows Azure SQL Database from outside of their Access app, which enables a whole new frontier of possibilities for advanced integration and extensions. <strong>This is big!</strong> <p>To enable external connections, simply click on the File menu to go to the Backstage. Under the Connections section, you'll find the SQL Server login credentials that you can use to connect to your database in SQL Server Management Studio, ASP.NET, or any other application that supports SQL Server. <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/8015.Info-Blog.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/350x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/8015.Info-Blog.png"></a> <p>The Manage connections button contains a number of commands that allow you to manage connections to the SQL Server database. You'll find that you can generate a read-only login and a read-write login. Use the read-only login when you want to connect to the SQL Server database from a program or app that doesn't need to modify the data, such as a reporting tool. Use the read-write login when you want to connect to the database and modify or enter new data. For example, you could create a public website in ASP.NET that allowed internet users to submit applications that get stored in your Access database. <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/4314.list.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-13-25/4314.list.png" width="500" height="240"></a> <h6>SQL Rocks with Access</h6> <p>We are really excited about these changes to Access 2013 and we hope you are as well. Windows Azure SQL Database and SQL Server 2012 give Access 2013 a powerful data engine to house your data. In return, Access 2013 is now a valuable end-user tool to rapidly build native SQL Server and Windows Azure SQL Database applications. Together, they also enable many new scenarios for advanced integration and extension. We can't wait to hear about the great new apps that you'll build with Access on Windows Azure SQL Database. <p>- by <a href="http://blogs.office.com/members/Andrew-Stegmaier/default.aspx">Andrew Stegmaier</a> and Gregory Leake</p></blockquote> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-48666803640706195402012-10-28T12:04:00.001-07:002012-10-28T12:07:22.686-07:00Microsoft Visual Studio LightSwitch for Microsoft Access, SQL Server, and Visual Studio .NET Database Developers<p><strong>Luke Chung</strong> (@<a href="https://twitter.com/LukeChung">LukeChung</a>) published a <a href="http://www.fmsinc.com/microsoftaccess/lightswitch/">Microsoft Visual Studio LightSwitch for Microsoft Access, SQL Server, and Visual Studio .NET Database Developers</a> to the FMS, Inc. blog: <blockquote> <p><a href="http://lh5.ggpht.com/-0pXl6XnH3sQ/UI19wRUC6BI/AAAAAAAAyv8/pqsSHnG1VtQ/s1600-h/image%25255B29%25255D.png"><img title="image" border="0" alt="image" align="right" src="http://lh4.ggpht.com/-EpftmLxQvd0/UI19w05vYYI/AAAAAAAAywE/Dm5SGK3IG2k/image_thumb%25255B13%25255D.png?imgmax=800" width="65" height="65"></a>The Visual Studio team has introduced a development platform called LightSwitch which simplifies the creation of database applications in Visual Studio. This rapid application development environment lets you create solutions that can be easily deployed on Windows or Mac platforms from a public web site or Intranet. <p>This article provides an overview of the benefits and limitations of the LightSwitch platform for the Microsoft Access community. <h5>Sample Screens</h5> <p>Here are some examples of what can be created in LightSwitch. <h5>Sample LightSwitch User Interface</h5> <p><img alt="LightSwitch Sample User Interface" src="http://www.fmsinc.com/microsoftaccess/lightswitch/screenshot.gif" width="520" height="354"> <h5>Dashboards Built in LightSwitch</h5> <p><img alt="Dashboards Built in LightSwitch" src="http://www.fmsinc.com/microsoftaccess/lightswitch/sample2.jpg" width="520" height="283"> <h5>LightSwitch Integration with Other Programs</h5> <p><img alt="LightSwitch Integration with Other Programs" src="http://www.fmsinc.com/microsoftaccess/lightswitch/sample3.jpg" width="520" height="442"> <h5>Customization Using Visual Studio .NET</h5> <p><a href="http://www.fmsinc.com/consulting/microsoft-dot-net-platform.aspx"><img alt="Microsoft Visual Studio .NET Programming" align="right" src="http://www.fmsinc.com/consulting/img/visual-studio-net.jpg" width="160" height="61"></a>While LightSwitch can be used to create database applications with limited coding, as part of the Microsoft Visual Studio .NET family, LightSwitch supports customization using C# and VB.NET programming languages. This offers all the benefits of managed code and the latest programming features. <p>LightSwitch does not support Office/Access VBA. <h5><a href="http://www.fmsinc.com/consulting/microsoft-sql-server.aspx"><img alt="Microsoft SQL Server Developers" align="right" src="http://www.fmsinc.com/MicrosoftSQLServer/LogoSQLServer-sm.gif" width="166" height="42"></a>Direct Support for Microsoft SQL Server</h5> <p>LightSwitch works directly against SQL Server databases. It understands table structures, referential integrity, one-to-many relationships, and other database architecture so that it can bind directly to your table, fields, and records. It requires your database to be properly designed with primary keys and other basic requirements, so having a good database design helps (and should be done anyway). <p><img alt="Microsoft Access and SQL Azure" align="right" src="http://www.fmsinc.com/microsoft-azure/sql-azure-logo-sm.jpg" width="166" height="46">It also supports databases hosted on SQL Azure, Microsoft's cloud provider. <h5>Dramatically Reduced Development Time</h5> <p>LightSwitch provides the ability to deliver incredibly rich, intuitive and easy to use applications, all within a Windows, Mac or Browser client. It offers affordable, reliable, and scalable custom solutions with user-friendly views of your data. It dramatically decreases the time it takes to build and deliver the custom application compared to traditional Visual Studio .NET approaches. <p>LightSwitch allows the use of extensible application shells to provide users with the familiar feel of popular Microsoft software, significantly reducing learning curve and application adoption time. <p>Built-in authentication models simplify the management of users with varying degrees of access and authorization, especially when integrated with existing Active Directory implementations. <h5>Requires Silverlight on the Client Machine</h5> <p><a href="http://www.microsoft.com/silverlight/"><img alt="Microsoft Silverlight" align="right" src="http://www.fmsinc.com/microsoftaccess/lightswitch/silverlight.jpg" width="160" height="58"></a>Visual Studio .NET and LightSwitch are used by the developer and are not installed on the users' machines. <p>However, LightSwitch applications require installing the free <a href="http://www.microsoft.com/silverlight/">Microsoft’s Silverlight</a> on each user's machine. This is a one time installation similar to installing Adobe Flash to watch videos or Adobe Acrobat Reader to open PDF files. Silverlight allows applications to be easily run on desktops and browsers through a one-click deployment, thereby dramatically reducing distribution and maintenance efforts. The Silverlight requirement makes a LightSwitch application inappropriate for general Internet solutions. <p>Unlike Microsoft Access database applications, you don't need to worry about what the user has installed on their Windows desktop, the version of Office/Access, and version conflicts on their machine. Unlike installing Office which usually requires physically updating each machine, Silverlight can be installed by the user from their browser. <h5>Platforms Supported by LightSwitch</h5> <p>LightSwitch runs as a Windows or Mac client application, and supports multiple web browsers, including: <ul> <li>Internet Explorer versions 7, 8, and 9 on Windows Vista and Windows 7 <li>Google Chrome version 12 or greater <li>Mozilla Firefox version 3.6 or higher <li>Apple Safari on Macintosh OS 10.5.7 (Intel-based) or higher </li></ul> <p>These browsers can run on 32 or 64-bit operating systems. <h5>Silverlight Limitations</h5> <h5>Does not Support iPad, iPhone, Android and Windows Phones</h5> <p>Silverlight is not supported on mobile platforms such as the iPad/iPhone, Android or Windows phone. <h5>Silverlight Requirement Limits its Use for Public Websites</h5> <p>By requiring the installation of Silverlight, LightSwitch applications are not suited for public web sites where visitors may not have it installed on their machines (sites face the similar issue when using Flash). However, for internal users and close external contacts, this requirement may be perfectly acceptable. <h5>Does not Support 64-bit Browsers</h5> <p>Silverlight is currently a 32-bit program that does not run in 64-bit browsers. This should not be an issue for most users. By default, on 64-bit PCs, the 32-bit version of Internet Explorer is installed and extra steps are required to intentionally install the 64-bit version, which will have problems supporting other common 32-bit components as well. <h5>Additional Limitations</h5> <h5>Limited User Interface Options</h5> <p>The LightSwitch architecture limits the user interface to its structure. We find the structure suitable for most database solutions but many Visual Studio .NET developers find the constraints (or potential constraints) too restrictive and uncomfortable in the long-term. There is definitely a tradeoff here, so it's important everyone understands the style of solution LightSwitch offers and are comfortable with it. <h5>No Reports</h5> <p>LightSwitch doesn't offer reporting. You can display data in a list, but you can't get the nice reports with groupings, summaries, sub-reports, etc. that exist in Microsoft Access. With SQL Server, you can use its Reporting Services feature, but integrating it into a .NET application is not the same as Access where you can share the same variable space as the application. There are third party controls that can be added for reporting. <h5>Future Directions</h5> <p>Microsoft has a preview version of an HTML5-based client that replaces Silverlight and offers true browser-based operation of LightSwitch applications (<a href="http://blogs.msdn.com/b/lightswitch/archive/2012/06/11/announcing-the-lightswitch-html-client.aspx">Microsoft announcement</a>). We expect the HTML5 client to be available from Microsoft in 2013 to support mobile clients. However, the features are not the identical to the Silverlight platform which provides a richer end user environment and is simpler to develop. <h5>Summary</h5> <p>With our experience building Microsoft Access and SQL Server solutions, we are very excited by the functionality and productivity LightSwitch offers for database application developers. LightSwitch fills a niche that allows the creation of web deployable SQL Server database solutions with .NET extensibility. It's ideal for solutions where the users are known either inside your organization or over the web. Microsoft Access remains a viable solution for end users, information workers, and applications that work on Windows. <h5>Database Evolution</h5> <p>Visual Studio LightSwitch offers the Microsoft Access community the opportunity to extend their platform beyond the Windows desktop. It is the natural evolution of solutions which start in Excel, evolve to Microsoft Access, grow into SQL Server, and now to the Intranet and web. Compared to traditional Visual Studio .NET applications, the learning curve for LightSwitch is considerably shorter which means solutions that were either too expensive or took too long to build, can now be created profitably. <p>If you're interested in learning how our Professional Solutions Group can help you with Microsoft Access, LightSwitch, SQL Server, and/or Visual Studio .NET, please visit our <a href="http://www.fmsinc.com/consulting/microsoft-lightswitch.aspx">LightSwitch Consulting page</a>. </p></blockquote> <p>Luke’s whitepaper also appears in <a href="http://oakleafblog.blogspot.com/2012/10/windows-azure-and-cloud-computing-posts_23.html#Light">Windows Azure and Cloud Computing Posts for 10/22/2012+</a>.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:e80849be-9ef1-4d87-8fbd-48a49812fd5f" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Visual+Studio+LightSwitch+2011" rel="tag">Visual Studio LightSwitch 2011</a>,<a href="http://technorati.com/tags/Visual+Studio+LightSwitch+2012" rel="tag">Visual Studio LightSwitch 2012</a>,<a href="http://technorati.com/tags/LightSwitch+2011" rel="tag">LightSwitch 2011</a>,<a href="http://technorati.com/tags/LightSwitch+2012" rel="tag">LightSwitch 2012</a>,<a href="http://technorati.com/tags/SilverLight" rel="tag">SilverLight</a>,<a href="http://technorati.com/tags/Visual+Studio+2012" rel="tag">Visual Studio 2012</a>,<a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Windows+Azure" rel="tag">Windows Azure</a>,<a href="http://technorati.com/tags/SQL+Server+Express" rel="tag">SQL Server Express</a>,<a href="http://technorati.com/tags/Windows+Azure+SQL+Database" rel="tag">Windows Azure SQL Database</a>,<a href="http://technorati.com/tags/SQL+Azure" rel="tag">SQL Azure</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-28350639283608562902012-08-30T07:01:00.001-07:002012-08-30T07:17:48.718-07:00Link Access 2013 Web Apps to SharePoint Lists<p><strong>Andrew Stegmeier</strong> posted <a href="http://blogs.office.com/b/microsoft-access/archive/2012/08/30/link-to-sharepoint-lists-in-access.aspx">Connect your Access 2013 Web Apps to SharePoint Lists</a> by <strong>Lois Wang</strong> on 8/30/2012:</p> <blockquote> <p><i>This post was written by Lois Wang, a Program Manager on the Access team.</i> <p><a href="http://lh4.ggpht.com/-l3ZcAPiIX8s/UD9yQR-DVGI/AAAAAAAArKw/Vx3wCNfJOGg/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-dW3caaMOq8M/UD9ySNRO6zI/AAAAAAAArK4/9ZmVkMmaLXg/image_thumb.png?imgmax=800" width="68" height="66"></a>Access 2013 web apps are great places to centralize your data. Whether you're tracking people, events, products or something else, storing data in an Access app allows you to easily collaborate with others while keeping things organized. <p>Sometimes, though, the stuff you care about is already stored somewhere else. Although you could import the data into Access, those external sources may be maintained by other people or processes. In these cases, you want to make sure that as these sources are updated, you're always seeing the latest version in Access. Wouldn't it be great if you could simply link to these data? <p>Access 2013 makes this easy. The web apps you create with Access 2013 can connect to and display real-time data from SharePoint lists. That way, you can easily supplement or combine external data sources with the things that your app uniquely tracks. <p><a href="http://lh6.ggpht.com/-02QtfrqteKA/UD9ySiNsdlI/AAAAAAAArLA/oTFOOGG9cKY/s1600-h/image%25255B5%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/--K7orjM_EOk/UD9yTdYWMwI/AAAAAAAArLI/Vjq_evui5Co/image_thumb%25255B1%25255D.png?imgmax=800" width="166" height="148"></a>Imagine a scenario where a small business owner named Ryan is trying to manage a party planning company. He and his five employees use Office 365 for sharing information. His accountant manages all the suppliers of his business in a SharePoint list. Ryan has built an Access 2013 web app to manage all the parties that he is in charge of planning. He wants to pull supplier information into his app, but he doesn't want to have to worry about manually keeping his app in sync with the accountants list. How can he do that? <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="The PartySuppliers SharePoint list." alt="The PartySuppliers SharePoint list." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/0755.SharePointList.png" width="480" height="319"> <p>He opens up his Access 2013 web app in the Access designer and clicks the Create Table button in the ribbon. Then, under the heading "Create a table from an existing data source," he chooses "SharePoint List." <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Add a new table from an existing data source." alt="Add a new table from an existing data source." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8688.ExistingDataSource.png"> <p>The next step is to provide the URL of the SharePoint site where the PartySuppliers list lives. Since he wants to link to rather than import his data, Ryan selects "Link to the data source by creating a linked table." <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="The External Data Wizard." alt="The External Data wizard." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/7635.ExternalDataDestination.png" width="480" height="355"> <p>Access will go fetch the names of the lists on that site, and Ryan selects the one he wants—the PartySuppliers list. In order for Ryan set up this link, his account's <a href="http://office.microsoft.com/en-us/windows-sharepoint-services-help/permission-levels-and-permissions-HA010100149.aspx">permission level</a> needs to be "Full Control" for the PartySuppliers list in SharePoint. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Assign permissions to allow your Access app to read the items in the SharePoint list." alt="Assign permissions to allow your Access app to read the items in the SharePoint list." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/4062.ExternalDataGrantPermissions.png" width="480" height="316"> <p>Now, the supplier data show up in Ryan's project management app. Access automatically creates a List view and a Datasheet view for displaying the suppliers. It looks and feels just like the rest of his Access web app. <p><img title="Access automatically creates List and Datasheet views to display the data in linked SharePoint lists." alt="Access automatically creates List and Datasheet views to display the data in linked SharePoint lists." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/4503.ExternalDataInAccess.png" width="480" height="316"> <p><a href="http://lh3.ggpht.com/-KVrx8aRY5e0/UD9yUMjnpfI/AAAAAAAArLQ/j6U3Jr2KhZI/s1600-h/image%25255B9%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-onrxvY63jss/UD9yUsjcVCI/AAAAAAAArLY/q8QKs6Jagic/image_thumb%25255B3%25255D.png?imgmax=800" width="160" height="91"></a>Ryan can further integrate this SharePoint list into his app by adding a lookup field in his Events table to show which PartySupplier is working on which Event. Even though the PartySuppliers "table" is actually stored externally, setting up this relationship works exactly as it would if the data were stored in a local table. <p>Two things are worth noting about this external data feature in Access 2013 web apps. First, Access currently only supports read-only connections to SharePoint lists. In our example, that would mean that in order to change information about a supplier, Ryan would have to edit the SharePoint list directly. <p>Second, in order to set up a connection to an external List, your user account has to be allowed to change permissions to the List. This is because when you set up the connection, you need to give the Access web app itself the right to read the data. The right to grant other accounts or apps access to a List is usually included with the "Full Control" or "Owner" SharePoint permission groups. If you have trouble, check with the person who is in charge of your SharePoint site. <p>Access 2013 web apps can easily integrate with external SharePoint lists. You can try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: <a href="http://www.microsoft.com/office/preview/en/office-365-small-business-premium">Small Business Premium</a> or <a href="http://www.microsoft.com/office/preview/en/office-365-enterprise">Enterprise</a>.</p></blockquote> <p>Access 2010’s Web Databases linked only to SharePoint lists, which was a drag. Inability to update SharePoint lists with Access is equally disappointing. <p>Will the real SharePoint logo please raise its hand?</p> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:24705aab-6aa7-40fb-94f8-ae863935a697" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Access+Web+Apps" rel="tag">Access Web Apps</a>,<a href="http://technorati.com/tags/SharePoint+2013" rel="tag">SharePoint 2013</a>,<a href="http://technorati.com/tags/SharePoint" rel="tag">SharePoint</a>,<a href="http://technorati.com/tags/SharePoint+Lists" rel="tag">SharePoint Lists</a>,<a href="http://technorati.com/tags/Access+Linked+Databases" rel="tag">Access Linked Databases</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-59787337996928221942012-08-21T07:16:00.001-07:002012-08-21T07:17:59.723-07:00Four Ways to Create Business Apps with Access 2013<p><strong>Andrew Stegmeier</strong> posted <a href="http://blogs.office.com/b/microsoft-access/archive/2012/08/20/4-ways-to-create-access-apps.aspx">4 Ways to Create Business Apps with Access 2013</a> on 8/20/2012:</p> <blockquote> <p>There are many ways to build useful apps to run your business with Access 2013. You can download an app via the Office Store, build an app using a Web App Template, or create a Custom Web App. You also have the ability to make a desktop database, just like you always have in Access. Each method offers a fast and easy way to get started. Several members of the Access team explain more in the video below: <p align="center"><iframe height="315" src="http://www.youtube.com/embed/nN1y1j3kE9I" frameborder="0" width="500" allowfullscreen></iframe></p> <h5>1 - Use the Office Store</h5> <p>Getting an app from the Office Store is a quick way to start using a database that's been professionally designed for a specific purpose. To use the store, you'll need to sign up for the Office 365 preview. (Be sure to choose one of the plans for business: <a href="http://www.microsoft.com/office/preview/en/office-365-small-business-premium">Small Business Premium</a> or <a href="http://www.microsoft.com/office/preview/en/office-365-enterprise">Enterprise</a>). After you've logged into your account, click the elipsis (...) in the upper-right-hand corner of the screen, then click "Office Store." You can easily search for apps related to your specific business. You'll find a wide array of SharePoint apps available—the ones that include the Access 2013 logo are Access 2013 web apps. After you find the app you want, you can click the Add button to install it directly to your SharePoint site and start using it right away. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Pick an app from the Office Store." alt="Pick an app from the Office Store." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/2870.AppStore.png" width="500" height="361"> <p>If you want, you can customize the app with Access 2013. For example, you could easily add a field to a table to track some information that's specific to your business. You could even add new tables and new relationships to track additional types of things. When you install an Access app from the Office store, you can get up and running fast, but there's still plenty of room to grow. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Customize your app in Access." alt="Customize your app in Access." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8103.CustomizeInAccess.png"> <h5>2 - Use Web App Templates</h5> <p>You can also make web apps directly from Access 2013. The easiest way to do this is by clicking on one of the web app template tiles that you see right after opening Access. You can search for additional templates if you don't see what you're looking for. These templates are fully-functional web databases, but instead of immediately seeing them in action, you're taken straight to the customization step. In our case, you can select "Task management" (outlined in blue). <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Create a new web app from a template." alt="Create a new web app from a template." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8030.NewWebApp.png" width="500" height="427"> <p>Once you've selected your template, give it a name and pick a location. If you're logged into your Office 365 preview account (see above), you will see two available locations by default. The first is for personal apps that you don't want to share with others. The second is for your company's default SharePoint team site. This is a great place to put an app that you want other people in your organization to be able to use. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Select a location for your app." alt="Select a location for your app." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/1682.NewTemplate.png" width="500" height="308"> <p>When you click Create, Access makes an app in the location you selected. The template you selected will include tables, relationships, and views that make it useful from the start. You can start adding data immediately by clicking Launch App in the ribbon. Or, you can customize the app in Access to your heart's content. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Launch your app in the browser." alt="Launch your app in the browser." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/2543.LaunchApp.png"> <h5>3 - Build a Custom Web App</h5> <p>You can also build an app by starting from scratch. You can create custom tables and fields for your precise needs or search and add tables from our library of Table Templates. To build a custom web app, open Access 2013 and click on "Custom web app" (highlighted in blue). Just like with Web App Templates, you'll need to give your app a name and a location. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Create a custom web app." alt="Create a custom web app." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/2021.NewCustomWebApp.png" width="500" height="390"> <p>You'll be initially given a blank database with no tables or schema. The easiest way to start building your app from here is to use table templates. To search for a table template related to what you'd like to do, simply type something in the search box and hit enter. You'll see a list of search results from the library. For example, if you type "Orders," then click on the Orders table template from the results, you'll find that a bunch of useful stuff related to tracking Orders has been added to your blank database. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Add tables using table templates." alt="Add tables using table templates." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/3286.NewTables.png" width="500" height="320"> <p>Each template includes all the tables, fields and views you'll need to create a working app. Some templates come along with other, related tables. For example, the Orders template also gives you a place to track related Customers, Employees, Products, Suppliers, and Categories. <p>Access includes a large searchable library of these templates. Whether you want to manage products, employees, customers, projects, tasks, or ideas, it's likely you'll find a template that's tailored to your needs. <h5>4 - Build a Desktop Database</h5> <p>If you don't have Office 365 or SharePoint, you won't be able to create web apps (options 1-3 above). You can, however, create desktop databases that run on your machine. If you've used previous versions of Access, these are the databases you're already familiar with, and you'll find it easy to work with files you've created in the past. Desktop databases have all the powerful features, such as VBA, that has made Access such a popular way to run a business. <p>To create a new desktop database, open up Access and click on any tile that uses the word "Desktop" such as the "Blank desktop database" or "Desktop asset tracking" (highlighted in blue). Just like with web databases, you can start from scratch or choose from a large library of templates. Once you've selected the template you want, give it a name and click create to start editing. <p>When Access is finished downloading your template, you'll be presented with the familiar, rich database building experience you've come to expect from Access, complete with reporting, advanced forms, and VBA. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="A traditional desktop database." alt="A traditional desktop database." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/7242.DesktopDatabase.png" width="500" height="315"> <h3></h3> <h5>Conclusion</h5> <p>No matter which method you choose—the Office store, Web App Templates, custom Web Apps, or Desktop Databases—Access 2013 will help you get started quickly. Try it out today by signing up for the Office 365 Preview. If you'd like to take advantage of Access 2013 Web Apps, be sure to choose one of the plans for business: <a href="http://www.microsoft.com/office/preview/en/office-365-small-business-premium">Small Business Premium</a> or <a href="http://www.microsoft.com/office/preview/en/office-365-enterprise">Enterprise</a>.</p></blockquote> <p> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:7239df17-69b5-4879-bf1c-be6b3ce4fc9c" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Access+2013+Web+Apps" rel="tag">Access 2013 Web Apps</a>,<a href="http://technorati.com/tags/SharePoint+2013+Access+Services" rel="tag">SharePoint 2013 Access Services</a>,<a href="http://technorati.com/tags/SharePoint+2013" rel="tag">SharePoint 2013</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-68589998504099390682012-08-15T09:04:00.001-07:002012-08-15T09:04:54.437-07:00Access 2013 Data Entry Made Easy<p><strong>Andrew Stegmeier</strong> posted <a href="http://blogs.office.com/b/microsoft-access/archive/2012/08/13/data-entry-made-easy.aspx">Data Entry Made Easy</a> on 8/13/2012:</p> <blockquote> <p><a href="http://lh6.ggpht.com/-n-1B_4YHCVg/UCvIopzYwAI/AAAAAAAApJI/a-MGTb3AK_c/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-PPw9WCm_Fww/UCvIpLHzOmI/AAAAAAAApJQ/GmKKWGHOiYg/image_thumb.png?imgmax=800" width="68" height="68"></a><em>This post was written by Erik Kennedy, a Program Manager on the Access team.</em> <p>In Access 2013, there are lots of improvements to help you quickly make a great user interface for your web databases. With this interface, the people who use your app will have easy time entering data. <p align="center"><iframe height="285" src="http://www.youtube.com/embed/QlqFzoCHWMs" frameborder="0" width="500" allowfullscreen></iframe></p> <p>There's two ways we've gone about doing this: <ol> <li>Automatically generating two views based on your data. <li>Generating special controls for entering related data—the related items control and the autocomplete control.</li></ol> <h3>Automatically Created Views</h3> <p>The basic unit of an Access app is a table. When you describe an app by what it tracks—"I want to track appointments" or "I want to track payments and invoices"—you're talking about tables. In this case, a table for Appointments, Payments, or Invoices. <p>Access 2013 does something really handy with all of your tables. Whenever you create a new table (or make certain updates to an existing one), it will automatically create (or update) two <i>views</i> for you. A view is the screen in the browser that you see when you navigate to the app—it's through views that you interact with your data. Access 2013 gives you the power to create entirely customized views, but to get you started quickly, it will automatically generate a List view and a Datasheet view. <p>This is a List view. It functions a lot like a fill-out form you may see on any other website. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="The default List view." alt="The default List view." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/3823.ListView.png" width="500" height="210"> <p>This is the Datasheet view. It looks and works like an Excel spreadsheet. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="The default datasheet view." alt="The default datasheet view." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/7737.Datasheet.png" width="500" height="159"> <p>Both the List view and the Datasheet view allow you to edit the data of your app. Whether you want to add new data, or edit or delete existing data, there's a way to do it in the view. <p>You can create different types of views besides these two, and you can even create duplicates of the same view, but with different data sources. For instance, you could have a List called "All Employees" <i>and</i> a list of "Current Employees". <p>But I'm getting ahead of myself. The point is that these views are automatically generated for you whenever you create a new table. And if you update the table in the Table Designer, as long as you haven't edited the view in the View Designer, your view will be updated automatically. <p>For instance, if you want to add a Birthday field to your Customers table to track the birthday of each customer, you can make the change in the Table Designer... <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Add a birthday field to the Customers table." alt="Add a birthday field to the Customers table." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/7140.AddBirthdayField.png" width="500" height="464"> <p>...and your View will be updated with the field you added when you click "Refresh" in the browser! <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="A birthday field is automatically added to the List view." alt="A birthday field is automatically added to the List view." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/3581.BirthdayFieldAdded.png" width="500" height="306"> <h3></h3> <h3>Hotkeys and the Action Bar</h3> <p>You'll notice at the top of the List view is a row of buttons. This is called the Action Bar, and it includes a number of commonly used commands that are useful in interacting with the data in your app. <p>For the List View, these actions are provided automatically: <ul> <li>Add item <li>Delete item <li>Edit item <li>Save item <li>Cancel item</li></ul> <p>These few functions help get an app off the ground quickly, so you can start using it right away. If your scenario requires advanced customization or business logic, you can focus your time designing for what's unique about your situation. <p>Because these functions are so common, we've also assigned some hotkeys. This way you can quickly move around the app without switching back and forth between the keyboard and the mouse. Here are the hotkeys for each of the actions: <ul> <li><strong>Add:</strong> N <li><strong>Delete:</strong> <Delete> key <li><strong>Edit:</strong> E <li><strong>Save:</strong> Ctrl + S <li><strong>Cancel:</strong> Esc</li></ul> <p>You'll also notice that you can tab between links and controls in your app. Keyboard shortcuts will help make it easy and fast to interact with the data in your Access 2013 app! <br clear="all"> <h4>Automatically Generated Controls for Related Data</h4> <p>Access is particularly powerful in tracking <i>relational data</i>—i.e. where two tables are connected. For instance, if you track Employees and Tasks, you'll likely want to connect those two tables so that each Task has an Employee assigned to it, and each Employee can have multiple tasks. <p>To relate two tables like that, you create a <i>lookup</i> from one table to another. A lookup is a kind of field in one table that can display data from another, related, table. So for the Tasks/Employees example, you would create a lookup field called "Owner" field in Tasks that displays the appropriate employee's name or alias. <p>Below, we're using the Table Designer to look at our Tasks table. You can see there's a lookup here to Employees, and that lookup is what makes the two tables related. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Create a lookup from Tasks to Customers." alt="Create a lookup from Tasks to Customers." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/2350.CreateLookup.png" width="500" height="312"> <p>Whenever you create a lookup between two tables, Access 2013 will automatically create some special controls for you so that it's easy to enter and view data for those tables. <p>The two special controls for related tables are: <ol> <li>The related items control <li>The autocomplete control</li></ol> <h5>The Related Items Control</h5> <p>In the Tasks/Employees example, we said that each Employee could have multiple tasks assigned to him or her. Wouldn't it be nice if you could see all of an employee's tasks just by looking at a view for that employee? That's what the related items control does. <p>If you navigate to a record in one table—in this case, Employees—you'll see data from a related table—in this case, all his related Tasks—thanks to the related items control. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="The related items control displays the Tasks assigned to an Employee." alt="The related items control displays the Tasks assigned to an Employee." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/4744.MultipleRelatedItem.png" width="500" height="298"> <p>Any time you create a relationship between two tables, Access 2013 will try to generate a related items control so you can see the related data easily. <p>This isn't all, though—if you click any of the items in the related items control, you will see a popup which presents all the details for that item. Want details on a specific task? Simply click on it. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Click on a related Task to see and edit its details." alt="Click on a related Task to see and edit its details." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8055.Popups.png" width="500" height="326"> <h5>The Autocomplete Control</h5> <p>The autocomplete control is generated in similar cases as the related items control, but it appears on the table in the relationship that <i>doesn't</i> have a related items control. <p>For the Tasks/Employees example, that means we'll see an autocomplete control on the Tasks table. If you navigate to a Task and look under owner, you'll notice it looks like a link. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Click on the hyperlink to drill-down into the details of the Employee who owns the Task." alt="Click on the hyperlink to drill-down into the details of the Employee who owns the Task." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/5873.AutocompleteLink.png" width="500" height="173"> <p>When you click the link, a popup launches with all the details for that item. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="A popup displays the Task owner's details." alt="A popup displays the Task owner's details." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8154.AutocompleteDrilldown.png" width="500" height="441"> <p>If you want to change the ownership of a task, the autocomplete control helps you find the right record in the related table. I simply start typing the name of another person into the autocomplete control, and I'm presented with a dropdown of the search results. When I've found the person I was looking for, I just hit enter. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="The autocomplete control will help you search the Employees table to find the right owner for the task." alt="The autocomplete control will help you search the Employees table to find the right owner for the task." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/6558.AutocompleteControl.png" width="500" height="202"> <p>The autocomplete control and the related items control make it easy to deal with related data. Combined with the List view and Datasheet view, Access 2013 makes it easy to view and edit data. Perhaps most importantly, all of this functionality is available automatically as soon as you create the tables that represent the things you want to track. When you're creating an app, you can focus on designing what's specific and unique about your scenario instead of rebuilding basic functionality every time. <p>Access 2013 is the most powerful tool around for quickly making a web app for your department, organization, or business—in part because it's easy and quick to enter and edit data. </p></blockquote> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:55c30da8-c339-43aa-a82f-f992a34aae5a" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Related+Items+Control" rel="tag">Related Items Control</a>,<a href="http://technorati.com/tags/Autocomplete+Control" rel="tag">Autocomplete Control</a>,<a href="http://technorati.com/tags/Automatically+Created+Views" rel="tag">Automatically Created Views</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-82094210606669533192012-08-10T09:07:00.001-07:002012-08-10T09:21:55.582-07:00Access 2013 Web Apps and SQL Server Back Ends<p><strong>Andrew Stegmeier</strong> of the Microsoft Access team posted a described of the relationship between <a href="http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx">Access 2013 and SQL Server</a>/SQL Azure on 8/8/2012:</p> <blockquote> <p><em>This post was written by Russell Sinclair, a Program Manager on the Access Team.</em> <p><img style="display: inline; float: right" alt="Access and SQL" align="right" src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/3404.Summary_5F00_AccessSQL_5F00_300x166.jpg" width="155" height="88">Access 2013 web apps feature a new, deep integration with SQL Server and SQL Azure. In Access 2010, when you created a web application on SharePoint, the tables in your database were stored as SharePoint lists on the site that housed the application. When you use Access 2013 to create a web app on SharePoint, Access Services will create a SQL Server or SQL Azure database that houses all of your Access objects. This new architecture increases performance and scalability; it also opens up new opportunities for SQL developers to extend and work with the data in Access apps. <p><font size="4"><strong>How it Works</strong></font> <p><a href="http://lh5.ggpht.com/-sr7jgwJDutw/UCU1ILybFzI/AAAAAAAAo8M/bD7wju3zMUU/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/--1Ijndj6y5k/UCU1ItLFsQI/AAAAAAAAo8U/0vHKm-4yLiw/image_thumb.png?imgmax=800" width="69" height="68"></a>When you create a web app in Access 2013, you'll choose a SharePoint site where you want it to live. Your app can be accessed, managed, or uninstalled from this site just like any other SharePoint app. In the process of creating your app in SharePoint, we provision a SQL Server database that will house all of the objects and data that your application requires. The tables, queries, macros, and forms are all stored in this database. Whenever anyone visits the app, enters data, or modifies the design, he'll be interacting with this database behind the scenes. If you create an app in Office 365, the database is created in SQL Azure. If you create an app on a SharePoint server that your company hosts, Access will create the database in the SQL Server 2012 installation that was selected by your SharePoint administrator. In either case, the database created is specific to your app and is not shared with other apps. <p>As you build your app, you can add tables, queries, views, and macros to deliver the functionality you and your users need. Here's what happens in the database when you create each of these objects: <h5>Tables</h5> <p>When you add table to your Access app, a SQL Server table is created in the database. This table has the same name you gave it in Access, as do the fields you create in the client. The data types that are used in the SQL Server database match the types you would expect: text fields use nvarchar; number fields use decimal, int or float; and image fields are stored as varbinary(MAX). <p>Consider the following table in Access: <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="A table in Access 2013." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/3201.AccessTable.png"> <p>The resulting table in SQL Server looks like this: <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="The same table in SQL Server." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/5417.SQLServerTable.png"> <h5>Queries</h5> <p>When you add a query to your app, Access creates a SQL Server view (or a table-valued function (TVF), if your query takes parameters). The name of the view or TVF matches the name you used in Access. We even use formatting rules when generating the T-SQL, so if you view the definition directly in SQL Server, it will be easy to understand. <p>This is a query designed in Access: <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="A query in Access 2013." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/7752.QueryAccess.png"> <p>It is stored as a formatted statement in SQL Server: <p><font face="Courier New">CREATE VIEW [Access].[MyQuery]<br>AS<br>SELECT<br>[MyTable].[ID],<br>[MyTable].[String Field],<br>[MyTable].[Date Field]<br>FROM<br>[Access].[MyTable]<br>WHERE<br>[MyTable].[Date Field] > DATEFROMPARTS(2012, 7, 16)</font> <h5><strong>Data Macros</strong></h5> <p>Data macros come in two flavors: event data macros and standalone macros. <p>You can create event data macros by opening a table in design view and clicking on any of the Events buttons in the Table ribbon. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Events in Access 2013 get translated into AFTER triggers in SQL." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8154.DataMacros.png"> <p>Event data macros are implemented on SQL Server as AFTER triggers on the table to which they belong. <p>You can create a standalone macro from the Home ribbon by clicking the Advanced button in the Create section and choosing Data Macro from the list of items. This type of macro can take parameters and is persisted as a stored procedure in SQL Server. <h5>Views</h5> <p>Views in Access 2013 are the parts of your app that display your data in the browser—database experts might call them forms. They are also stored in the database. Since they are HTML and JavaScript rather than SQL objects, they are stored as text in the Access system tables. <p><font size="4"><strong>SQL Server Schemas</strong></font> <p>Within the database, Access makes use of three separate SQL Server schemas: Access; AccessSystem; and AccessRuntime. <p>The <b>AccessSystem</b> schema contains system tables that store the definitions of each object in a format that Access Services understands, as well as bits and pieces of information that are necessary in order for the item to work well in the runtime or design time surface. <p>The <b>Access</b> schema contains all of the tables, queries, and macros created by you, the app designer. Everything in this schema is the implementation of the objects you designed in SQL Server. <p>The <b>AccessRuntime</b> schema contains a number of items that we use in Access Services to optimize the runtime behavior of your application. <p><strong><font size="4">So What?</font></strong></p> <p>You might be wondering why these details are important. For some users, the only visible effect of the new SQL Server back-end will be increased speed and reliability. They don't need to worry about the technical details. More advanced users, though, can directly connect to the SQL Server or SQL Azure database from outside of their Access app, which enables a whole new frontier of possibilities for advanced integration and extensions. <b>This is big!</b> <p>To enable external connections, simply click on the File menu to go to the Backstage. Under the Connections section, you'll find the SQL Server login credentials that you can use to connect to your database in SQL Server Management Studio, ASP.NET, or any other application that supports SQL Server. <p><img alt="The backstage view of a database in Access will give you the information you need to connect to the back-end SQL database." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/5415.Backstage.png" width="500" height="368"> <p>The Manage connections button contains a number of commands that allow you to manage connections to the SQL Server database. You'll find that you can generate a read-only login and a read-write login. Use the read-only login when you want to connect to the SQL Server database from a program or app that doesn't need to modify the data, such as a reporting tool. Use the read-write login when you want to connect to the database and modify or enter new data. For example, you could create a public website in ASP.NET that allowed internet users to submit applications that get stored in your Access database. <p><img alt="Open (or close) connections to the back-end SQL database." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/0207.Connections.png" width="500" height="240"> <p>Please note, however, that this functionality is not currently available in the Office 365 Preview. If you'd like to try it out, though, you can download the <a href="http://technet.microsoft.com/en-us/evalcenter/hh973397.aspx?wt.mc_id=TEC_121_1_33">Microsoft SharePoint Server 2013 Preview</a> and set it up on your own servers. <p><font size="4"><strong>SQL Server Rocks</strong></font></p> <p>We are really excited about these changes to Access 2013 and we hope you are as well. SQL Azure and SQL Server give Access 2013 a powerful data engine to house your data. They also enable many new scenarios for advanced integration and extension. We can't wait to hear about the great new apps that you'll build with Access.</p></blockquote> <p>What’s not clear from Russ’ article is how do SQL Server and Windows Azure SQL Database (formerly SQL Azure) handle multi-valued list boxes, which require backing by SharePoint lists in Access 2010. <p> <hr> </p> <p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:bf004a23-c3cd-4faf-b325-2e845ac9eaea" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Access+2013+Web+Apps" rel="tag">Access 2013 Web Apps</a>,<a href="http://technorati.com/tags/Access+Web+Apps" rel="tag">Access Web Apps</a>,<a href="http://technorati.com/tags/Windows+Azure+SQL+Database" rel="tag">Windows Azure SQL Database</a>,<a href="http://technorati.com/tags/WADB" rel="tag">WADB</a>,<a href="http://technorati.com/tags/SQL+Azure" rel="tag">SQL Azure</a>,<a href="http://technorati.com/tags/SharePoint+2013+Access+Services" rel="tag">SharePoint 2013 Access Services</a>,<a href="http://technorati.com/tags/SharePoint+2013" rel="tag">SharePoint 2013</a>,<a href="http://technorati.com/tags/SharePoint" rel="tag">SharePoint</a></div></p> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-52563143996784451292012-07-31T07:37:00.001-07:002012-07-31T16:55:04.714-07:00Get Started with Access 2013 Web Apps<p><strong>Andrew Stegmeier</strong> posted <a href="http://blogs.office.com/b/microsoft-access/archive/2012/07/30/get-started-with-access-2013.aspx">Get Started with Access 2013 Web Apps</a> to the Microsoft Access blog on 7/30/2012:</p> <blockquote> <p>You can use Access 2013 and the Office 365 Preview to build a web app almost immediately and start using it to track the things you care about. There's no need to set up expensive or complicated servers, but you can still take advantage of the speed, security and simplicity that comes with server-based apps. <p>Follow these steps to get started in minutes:<strong></strong> <h5>1. Sign up for Office 365 Preview<strong></strong></h5> <p><strong></strong> <p>You can create Access 2013 web databases[<strong>*</strong>] on any SharePoint 2013 site. The easiest way to try this out is through SharePoint Online, which is included in the <a href="http://www.microsoft.com/office/preview/en/office-365-small-business-premium">Office 365 Small Business Premium</a> and <a href="http://www.microsoft.com/office/preview/en/office-365-enterprise">Office 365 Enterprise</a> plans. (Note that the Office 365 ProPlus and Office 365 Home Premium products do not include SharePoint Online). <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Choose an Office 365 plan for business: Small Business Premium or Enterprise" alt="Choose an Office 365 plan for business: Small Business Premium or Enterprise" src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8270.PlansForBusiness.png" width="480" height="273"> <p>Once you've downloaded a copy of Office 2013 through the preview site, open Access 2013 and click "Create Blank Web Database." For the "Web Location," enter the URL of the SharePoint site where you'd like to put your app. On Office 365, that URL will look something like this: <p><font face="Courier New">http://mycompany.onmicrosoft.com/mysharepointsite</font> <p>When you click Create, Access will talk with the SharePoint server, and make a new, blank Access App on the site. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Create a new web app." alt="Create a new web app." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/2148.NewApp.png" width="480" height="403"> <h5>2. Add tables to your app</h5> <p>When your blank app is finished being created, you'll be taken to the Add Tables screen. The fastest way to build your app is to search through our list of table templates to see if we have one that's similar to the thing that you'd like to track. Whether you want an app to store contacts, employees, companies, equipment, events—or anything else—you'll likely find something there to help you get started. <p>Simply type something into the box that says "What would you like to track?", press Enter, and select something from the search results. As soon as you click on an available template, that table will be added to your app. <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="Add some tables." alt="Add some tables." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/0143.AddTables.png" width="480" height="360"> <p>At the most basic level, a table template is a list of fields. For example, if you choose the "Contacts" template, you'll get a place to store information about people with fields such as "First Name," "Last Name," and "Email Address." You also may find that a table comes along with others. For example, adding the "Assets" template (illustrated above) also gives you a "Categories" table. You can enter whatever Categories you want into this table, which you can use to group and organize the Assets. <p>You can easily customize these templates to suit your needs. Stay tuned for future posts that will dive into these topics. <h5>3. Launch your app in the browser</h5> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Launch the app in the browser." src="http://blogs.office.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/0842.LaunchApp.png"> <p>After you've added some tables to your app, you're ready to start using it. Click the "Launch App" button to view the app in your internet browser. <p>In general, you'll use a browser to add, edit and view the data in your app. Access 2013 is there to help you create and customize the views and tables. After you're done designing, you can share the app with other people, even if they don't have Access. Access web apps work in all major browsers, so whether you prefer Internet Explorer, Firefox, Chrome, or Safari, you can still get to your data. <p>Let's take a quick look at what you've created: a fully-functioning app to track the things you care about. <p>On the left (1) is a list of tables. These are the broad types of things, like assets, that are tracked in your app. For each table, there are several views (2) across the top. These views display the information stored in the tables in useful ways. The default List view can be used to search and filter the different assets (3) and to edit, add, or delete information about each asset (4). <p>As you can see, Access 2013 makes it easy to build a fully functioning web app in no time. We hope you'll check it out by signing up for one of the Office 365 plans for business: <a href="http://www.microsoft.com/office/preview/en/office-365-small-business-premium">Small Business Premium</a> or <a href="http://www.microsoft.com/office/preview/en/office-365-enterprise">Enterprise</a>.</p></blockquote> <p> <p><strong>*</strong> I believe that the Access 2010 term “web database(s)” should be “custom web app(s)” for Access 2013 and the Office 365 Preview.</p> <p> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:855fa3aa-bb7b-4068-8503-7e9b079cd218" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Office+365+Preview" rel="tag">Office 365 Preview</a>,<a href="http://technorati.com/tags/Office+365" rel="tag">Office 365</a>,<a href="http://technorati.com/tags/Access+Web+Apps" rel="tag">Access Web Apps</a></div></p> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-68727300337217241592012-07-25T12:54:00.001-07:002012-07-25T12:56:51.013-07:00Tim Anderson Gives Access 2013 Web Apps the Thumbs-Up<p><strong>Tim Anderson</strong> (@<a href="http://twitter.com/timanderson">timanderson</a>) posted <a href="http://www.itwriting.com/blog/6147-access-web-app-at-last-a-simple-web-database-app-builder-from-microsoft.html">Access Web App: at last a simple web database app builder from Microsoft</a> on 7/25/2012:</p> <blockquote> <p><a href="http://lh5.ggpht.com/-ZrZMqOv9ze4/UBBO4ZUHmdI/AAAAAAAAnbk/av2FF0o27eA/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh4.ggpht.com/-vcCevP4w61o/UBBO4rGtLCI/AAAAAAAAnbs/VecNQ5EKQUg/image_thumb%25255B1%25255D.png?imgmax=800" width="65" height="66"></a>One thing hardly mentioned in the press materials for Office 2013, and therefore mostly ignored in the immediate publicity, is Microsoft Access 2013. It is included though, and its most interesting new feature is a thing called an Access Web app. <p><a href="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image44.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image_thumb44.png" width="244" height="176"></a> <p>To make one of these, you click the big “Custom web app” button on the opening screen. The first thing you are asked is where to put it. It is looking for a SkyDrive or Office 365 team site – essentially, online SharePoint 2013 I imagine. If you are not signed in, this screen appears blank. <p><em>Advertisement</em> <p>I selected SkyDrive at my Office 365 preview site. <p><a href="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image45.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image_thumb45.png" width="244" height="197"></a> <p>Hit Create and you can select an app from a template. I chose a Music Collection app. Access generated several tables and forms for me and opened the design environment. <p><a href="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image46.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image_thumb46.png" width="244" height="143"></a> <p>The template app is a bit daft – Artists and Labels are based on a People template, so you get Labels with a Job Title field – but that does not bother me. What interests me is that Access generates a relational database that you can edit as you like. The template UI offers either a list/detail view called a List, or a Datasheet which shows rows in a grid format. There is also a Blank view which you can design from scratch. <p>I had a quick poke around. Access Web Apps do too good a job of hiding their innards for my taste, but what you get is a SharePoint app with data stored in SQL Server Azure. You can also use on-premise SharePoint and SQL Server 2012. <p>Programmability in Access Web Apps is limited, but you do get macros which let you combine multiple actions. There are two kinds of macros, UI macros and Data macros. UI macros support a range of actions including <strong>SetVariable</strong>, <strong>if</strong> and <strong>else</strong> statements. The only loop functions I can see are in Data macros, which include a <strong>ForEachRecord</strong> action. You can call Data macros from other macros and a Data macro includes a <strong>SetReturnVar</strong> statement, so I guess with a bit of ingenuity you can do many kinds of automated operations. Macros are described <a href="http://msdn.microsoft.com/en-us/library/office/jj250134(v=office.15)">here</a>. <p><a href="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image47.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image_thumb47.png" width="180" height="244"></a> <p>In my quick test, I put a button on a view and had it show a message. Apologies. <p>The application files are all stored on SharePoint, rather than locally, so I presume you could easily edit the app on any machine with Access 2013 installed. <p>Click Launch App and the web app opens in the browser. Everything worked, including my MessageBox. <p><a href="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image48.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image_thumb48.png" width="404" height="207"></a> <p>I also tried it on the Google Nexus 7 Android device. Again it seems to work fine, though I did get some odd behaviour returning to the app. There are possibly some authentication issues. <p><a href="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image49.png"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://www.itwriting.com/blog/wp-content/uploads/2012/07/image_thumb49.png" width="404" height="254"></a> <p>An Access Web App is just another SharePoint app, as explained <a href="http://blogs.office.com/b/microsoft-access/archive/2012/07/20/introducing-access-2013-.aspx">here</a>, so you can publish it to selected groups via the built-in store. <p>There is no way that I can see to craft your own SQL, which to me is a disadvantage, but maybe we will discover how to bypass the UI and open a database in SQL Management Studio, or access it programmatically from other environments. <p>It seems to me that what Microsoft is offering here is what it tried, but failed, to offer in Visual Studio LightSwitch: database programming for the non-specialist. Access has always done this, though unfortunately it is easy to make rather a mess if you do not know what you are doing. An Access Web App gives the developer/user fewer ways to go wrong, and builds cross-browser web apps. It is not yet possible to judge whether Microsoft has got the feature set right, but fundamentally this looks useful for simple custom business database applications of the kind that many small organisations and departments find they need. It is a big advance on MDB files stuck on a file share, fits with the BYOD (Bring Your Own Device) concept by working on iPads and the like, and makes it easy to get started and experiment. Good work. <p>Related posts: <ol> <li><a href="http://www.itwriting.com/blog/articles/very-simple-vb-2008-database-app-with-databinding">Very simple VB 2008 database app with databinding</a> <li><a href="http://www.itwriting.com/blog/712-sample-code-for-a-very-very-simple-vb-database-application.html">Sample code for a very very simple VB database application</a> <li><a href="http://www.itwriting.com/blog/636-microsoft-access-needs-a-complete-rethink-or-retirement.html">Microsoft Access needs a complete rethink – or retirement</a> <li><a href="http://www.itwriting.com/blog/2156-the-end-of-code-access-security-in-microsoft-net.html">The end of Code Access Security in Microsoft .NET</a> <li><a href="http://www.itwriting.com/blog/3541-remote-access-to-files-in-microsoft-small-business-server-2011.html">Remote access to files in Microsoft Small Business Server 2011</a></li></ol></blockquote> <p>This might be Microsoft’s answer to FileMaker and QuickBase. I plan to try Tim’s approach and test it with my Nexus 7 tablet shortly. Stay tuned.</p> <p> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:b49228bf-450a-4250-86df-08387b3dd0af" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Office+2013" rel="tag">Office 2013</a>,<a href="http://technorati.com/tags/Office+365" rel="tag">Office 365</a>,<a href="http://technorati.com/tags/SharePoint+2013" rel="tag">SharePoint 2013</a>,<a href="http://technorati.com/tags/SharePoint" rel="tag">SharePoint</a>,<a href="http://technorati.com/tags/Access+Web+Apps" rel="tag">Access Web Apps</a>,<a href="http://technorati.com/tags/Visual+Studio+LightSwitch" rel="tag">Visual Studio LightSwitch</a></div></p> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com2tag:blogger.com,1999:blog-4760178173405088846.post-17389513842191844382012-07-23T12:59:00.000-07:002012-07-25T13:04:47.075-07:00The Microsoft Access Team Announces Access 2013<p><strong>Andrew Stegmaier</strong> posted <a href="http://blogs.office.com/b/microsoft-access/archive/2012/07/20/introducing-access-2013-.aspx">Introducing Access 2013</a> on 7/20/2012 (missed when published):</p> <blockquote> <p><a href="http://lh5.ggpht.com/-BKcJyNseG9I/UBBRXMNICII/AAAAAAAAncQ/wguDG-B82nU/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/-51Y_DTnDc_c/UBBRXpumvJI/AAAAAAAAncY/-0yrRniBRJU/image_thumb%25255B1%25255D.png?imgmax=800" width="65" height="60"></a>The Access team is proud to introduce the Access 2013 public preview, which will make it easier than ever for everyday people to organize the data in their lives and businesses using Access apps. On this blog, you'll be able to learn about the improvements included in this new release. <p>Access has always been a great tool to help you organize and run a small business or a team. This release focuses on bringing Access databases to the web, making them more useful than ever. Your database can be hosted by Microsoft through Office 365 and securely accessed from any tablet or computer—even if the device doesn't have Access installed. <p>Getting started is easier, too, taking just 60 seconds to get your first Access app up and running. With little or no additional effort, you'll end up with a finished app that is both functional and beautiful—automatically—thanks to an enhanced user interface. Finally, we've made some big improvements under the covers to make your apps faster and more extensible. Your data is now stored in a full-fledged SQL Server database. When Microsoft hosts your database in the cloud, we'll use SQL Azure; when you host it yourself on your network, you can use SQL Server 2012. Advanced users will love the fact that they can directly connect to SQL Server with familiar tools for powerful analysis and integration. <p>Here's a peek of some of the things we'll be talking about: <h5>Host your databases in the cloud with Office 365</h5> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Create a new Access 2013 web app with Office 365." src="http://blogs.office.com/resized-image.ashx/__size/350x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/4188.NewApp.png">If your Office 365 plan includes SharePoint, you can host Access 2013 databases with no extra setup required. Microsoft will make sure your data is secure, backed-up, and available, so that you can focus on getting things done. You can try it out by signing up for a preview of <a href="http://www.microsoft.com/office/preview/en/office-365-small-business-premium">Office 365 Small Business Premium</a> or <a href="http://www.microsoft.com/office/preview/en/office-365-enterprise">Office 365 Enterprise</a>. Whether you’re a small business or a large corporation, you’ll be able to harness the power of Access 2013 in the cloud simply and easily. Of course, companies also have the option of hosting databases themselves by installing SharePoint 2013 and SQL Server 2012 on their own network. <h5>Get started faster</h5> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Add tables using templates." src="http://blogs.office.com/resized-image.ashx/__size/550x200/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/5102.AddTables.png">Search from a library of table templates to help you track the people, things, events, or tasks that you care about. Each table template comes with fields, views, relationships, and data-integrity rules, so you can take advantage of all the great features of Access 2013 with a single click. It’s easy to combine different table templates into a single app or tweak an existing table by adding or removing fields. You’ll get to spend your time customizing your database to meet your unique needs instead of worrying about repetitive details. <h5>A polished, professional user interface for your apps</h5> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="A clean, professional user interface." src="http://blogs.office.com/resized-image.ashx/__size/550x250/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/3021.UserInterface.png">Whether you use table templates, import existing data, or define your own schema from scratch, Access 2013 will provide your database with a great user interface automatically. Without any effort on your part, Access will generate views for your data, including a searchable list view and an Excel-like datasheet. Buttons to navigate between your views and tables come for free, too. If you have related data—like Invoices and Line Items—Access will automatically create views that show these items together, allowing you to drill-through to get more details. Of course, everything is still customizable, but now you can focus on what's unique about your app. <h5>Access apps = SharePoint apps</h5> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Add Access apps to SharePoint using the App Catalogue" src="http://blogs.office.com/resized-image.ashx/__size/550x280/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8182.SharePointApps.png">Access 2013 web databases work great with SharePoint 2013, which has been enhanced in this release with <a href="http://blogs.msdn.com/b/officeapps/archive/2012/07/17/introducing-the-new-office-cloud-app-model.aspx">apps for SharePoint</a>. Because an Access app is just like any other SharePoint app, it’s easy to deploy, manage, and share securely. There are no additional passwords or logins to juggle because security is controlled through the same infrastructure. Users can discover and share Access apps through the public SharePoint App Store or a private App Catalogue. Installing an app takes just a few clicks. Corporate IT can control everything centrally using familiar SharePoint tools. Best of all, anyone with a web browser and an internet connection can use your app, even if he doesn't have Access installed on his device. <h5>SQL Server back-end</h5> <p>One of the biggest improvements in Access 2013 is one you may not even notice—except that you're whole app will be faster, more reliable, and work great with large amounts of data. When Access databases are published to SharePoint—whether on-premise or through Office 365—a full-fledged SQL Server database is automatically created to store the data. Advanced users who are already familiar with SQL Server will be able to directly connect to this database for advanced reporting and analysis with familiar tools such as Excel, Power View, and Crystal Reports. Everyday users can rest assured that their apps are ready for the future if they ever need to enhance them with advanced integrations or migrations. Check out the <a href="http://msdn.microsoft.com/en-US/office/aa905400">Access 2013 developer center</a> for more details.<b></b> <p><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Access on Office 365 harnesses the power of SQL Azure." src="http://blogs.office.com/resized-image.ashx/__size/600x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-00-53/8032.SQLBackEnd.png" width="480" height="143"> <p>We're looking forward to introducing you to what's amazing and new about Access 2013. Stay tuned! <p> <hr> </p></blockquote> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:561b0971-72c6-4bc1-9bc0-051ed82513bd" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Office+2013" rel="tag">Office 2013</a>,<a href="http://technorati.com/tags/Office+365" rel="tag">Office 365</a>,<a href="http://technorati.com/tags/SharePoint+2013" rel="tag">SharePoint 2013</a>,<a href="http://technorati.com/tags/SharePoint" rel="tag">SharePoint</a>,<a href="http://technorati.com/tags/Access+Web+Apps" rel="tag">Access Web Apps</a>,<a href="http://technorati.com/tags/Visual+Studio+LightSwitch" rel="tag">Visual Studio LightSwitch</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-8093374422110919212012-07-18T11:13:00.000-07:002012-07-18T11:13:12.202-07:00First Look at Office 365 Pro Plus Preview with Access 2013<p>Previews of multiple preview versions of Office 365 became available for download on July 16, 2012. I installed Office 365 Pro Plus’s online version only on Windows 8 Release Preview (Build 8400) as a starter. Here’s my desktop after the about 30-minute installation:</p> <p><a href="http://lh4.ggpht.com/-69bdux2f8rs/UAbjwslbpQI/AAAAAAAAmyc/9dvtAbWZEX4/s1600-h/1%252520-%252520Office365PreviewDesktop%25255B5%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="1 - Office365PreviewDesktop" border="0" alt="1 - Office365PreviewDesktop" src="http://lh4.ggpht.com/-dKvkQb6zYJE/UAbjx72qxyI/AAAAAAAAmyk/SPVYD4HBQdM/1%252520-%252520Office365PreviewDesktop_thumb%25255B3%25255D.png?imgmax=800" width="560" height="420"></a></p> <p>Clicking the Access 2013 tile opens the start window:</p> <p><a href="http://lh5.ggpht.com/-_5j4AalZPkc/UAbjzCxag7I/AAAAAAAAmys/6yegTnyEZMw/s1600-h/2%252520-%252520Access2013PreviewStart%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="2 - Access2013PreviewStart" border="0" alt="2 - Access2013PreviewStart" src="http://lh3.ggpht.com/-NDmn4xC2tEA/UAbj1cXbGVI/AAAAAAAAmy0/89qUMaMpMkA/2%252520-%252520Access2013PreviewStart_thumb%25255B2%25255D.png?imgmax=800" width="562" height="422"></a></p> <p>Notice that Access Custom Web Apps appear to have replaced Access Web Databases.</p> <p>Clicking the Blank Desktop Database button proposes to create a new Table1:</p> <p><a href="http://lh4.ggpht.com/-h9vBD4v6wO8/UAbj3XpIAyI/AAAAAAAAmy8/uPdM32NM3K4/s1600-h/3%252520-%252520Access2013PreviewCreateTable%25255B5%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="3 - Access2013PreviewCreateTable" border="0" alt="3 - Access2013PreviewCreateTable" src="http://lh3.ggpht.com/-QV_502w-C_E/UAbj4e27BKI/AAAAAAAAmzE/4biAxD3Y-YA/3%252520-%252520Access2013PreviewCreateTable_thumb%25255B3%25255D.png?imgmax=800" width="562" height="422"></a></p> <p>Following is a completed table in Table Design view:</p> <p><a href="http://lh5.ggpht.com/-IQLM8LAgX6g/UAbj5rbc-ZI/AAAAAAAAmzM/Hc-uJW_cQkg/s1600-h/4%252520-%252520Access2013PreviewTableDesign%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="4 - Access2013PreviewTableDesign" border="0" alt="4 - Access2013PreviewTableDesign" src="http://lh3.ggpht.com/-mo7UJJjJmYo/UAbj6nvS6QI/AAAAAAAAmzU/g6IE5fS9AWQ/4%252520-%252520Access2013PreviewTableDesign_thumb%25255B2%25255D.png?imgmax=800" width="562" height="422"></a></p> <p>More follows after I find out more about Access 2013 Custom Web Apps. The link to the topic is broken (see below).</p> <h4>New Access 2013 Application Architecture</h4> <p>From the <a href="http://msdn.microsoft.com/en-us/library/df778f51-d65e-4c30-b618-65003ceb39b3#ac15_WhatsNew_PublishApp">What's new for Access 2013 developers</a> document:</p> <p><a href="http://lh5.ggpht.com/-VuOrhM33b88/UAb1rLIWz1I/AAAAAAAAmzo/2-K7S1Xug58/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-otLMwKGUpnU/UAb1seVkqpI/AAAAAAAAmzw/a-9-nhZY_Ic/image_thumb%25255B1%25255D.png?imgmax=800" width="560" height="307"></a></p> <p>I’m glad to see a more direct connection to SQL Server than SharePoint lists, but the use of SOAP Web services flies in the face of the trend to RESTful approaches. There’s no mention of VBA programmability that I can find in the resources below. Macros are back in full force.</p> <h4>Resources</h4> <p>Following is Microsoft documentation related to Access 2013 as of 7/16/2012:</p> <ul> <li><a href="http://msdn.microsoft.com/en-us/library/sharepoint/fp179914(v=office.15)">What's new in Access 2013</a></li> <li><a href="http://msdn.microsoft.com/en-us/library/df778f51-d65e-4c30-b618-65003ceb39b3#ac15_WhatsNew_PublishApp">What's new for Access 2013 developers</a></li> <li><a href="http://msdn.microsoft.com/en-us/library/sharepoint/jj249372(v=office.15)">Create and customize a web app in Access 2013 Preview</a></li> <li><a href="http://msdn.microsoft.com/en-us/library/8d696fa4-a6f2-4fb1-8662-a313bf0b5989">Access 2013 Custom Web App Reference</a> (This link is broken)</li></ul> <p> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:df77fef5-bc0f-4c9f-92d7-74fc72bae189" class="wlWriterSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2013" rel="tag">Microsoft Access 2013</a>,<a href="http://technorati.com/tags/Access+2013" rel="tag">Access 2013</a>,<a href="http://technorati.com/tags/Office+365+Preview" rel="tag">Office 365 Preview</a>,<a href="http://technorati.com/tags/Office+365+Pro+Plus+Preview" rel="tag">Office 365 Pro Plus Preview</a>,<a href="http://technorati.com/tags/Access+2013+Online" rel="tag">Access 2013 Online</a>,<a href="http://technorati.com/tags/Access+Custom+Web+Apps" rel="tag">Access Custom Web Apps</a></div></p> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-41136563817126332332012-02-08T11:26:00.001-08:002012-02-08T11:26:53.099-08:00Securing MS Linked Tables Connection Strings During Migration<p><strong>Han</strong> posted <a href="http://blogs.msdn.com/b/ssma/archive/2012/02/08/securing-ms-linked-tables-connection-strings-during-migration.aspx">Securing MS Linked Tables Connection Strings During Migration</a> to the SQL Server Migration Assistant Team Blog on 2/8/2012:</p> <blockquote> <p>Microsoft Access stores all the connection strings for the respective linked tables in a system table called MSysObjects. As seen below, the connection strings contain clear-text used id and password. With the release for SSMA for Access 5.2, when creating link tables during migration, users will now have the option to not store the user id and password for the linked tables. <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-39-83/8055.connection_5F00_strings.jpg"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-39-83/8055.connection_5F00_strings.jpg" width="480" height="127"></a> <p>A new setting for linked tables can be found under the <b><i>Project Settings</i></b> menu. By default, the <b><i>Store user credentials</i></b> setting is set to false, thus user id and password will not be persisted in the connection string of a linked table. Switching the setting to <b>true</b> would provide the option to store the user id and password in the connection strings during the creation of linked tables. <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-39-83/4705.setting.jpg"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-39-83/4705.setting.jpg" width="480" height="345"></a> <p>It is important to note that after securing the connection string, MS Access users will have to enter the required user id and password whenever the linked tables are referenced in the MS Access Database application. Below shows the prompt presented by MS Access. <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-39-83/2806.prompt.jpg"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="" src="http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/communityserver-blogs-components-weblogfiles/00-00-01-39-83/2806.prompt.jpg" width="283" height="190"></a></p></blockquote> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:641477a1-89ac-4688-a4a5-8541437d6e51" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Microsof+Access" rel="tag">Microsof Access</a>,<a href="http://technorati.com/tags/SQL+Server+Migration+Assistant" rel="tag">SQL Server Migration Assistant</a>,<a href="http://technorati.com/tags/SQL+Server" rel="tag">SQL Server</a>,<a href="http://technorati.com/tags/SQL+Azure" rel="tag">SQL Azure</a>,<a href="http://technorati.com/tags/SQL+Azure+Migration" rel="tag">SQL Azure Migration</a>,<a href="http://technorati.com/tags/Cloud+Computing" rel="tag">Cloud Computing</a>,<a href="http://technorati.com/tags/Windows+Azure" rel="tag">Windows Azure</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-7340753187748742372012-01-28T12:47:00.001-08:002012-01-28T12:47:30.776-08:00How to save your Access SharePoint Password<p><strong>Emily Warn</strong> posted <a href="http://blogs.office.com/b/microsoft-access/archive/2012/01/26/how-to-save-your-sharepoint-password-in-windows.aspx">How to save your Access SharePoint password</a> to the Access Blog on 1/26/2012:</p> <blockquote> <p><a href="http://lh4.ggpht.com/-FVCST88hc5w/TyRe3zfQ8CI/AAAAAAAAaHc/nM-mXaAJ9fQ/s1600-h/image%25255B5%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-d-cmwiXBY-c/TyRe31536hI/AAAAAAAAaHk/YxP05L6Byuk/image_thumb%25255B1%25255D.png?imgmax=800" width="72" height="70"></a>Recently, someone posted a comment on IT Impact Inc.'s blog asking how to get SharePoint to remember his password when he logged in via Access. He wanted to avoid having to log in every time. <p>Ben Clothier, a Senior Access Developer at IT Impact, knew the answer. He wrote a <a href="http://accessexperts.net/blog/2012/01/18/how-to-save-your-sharepoint-password-in-windows/">detailed blog post</a> (with plenty of screenshots) that we'd like to share with you. <p><a href="http://accessexperts.net/blog/about/"><a href="http://lh3.ggpht.com/-_tJt4qGpo_A/TyRe4NAeJGI/AAAAAAAAaHs/eLWuKWzvQE8/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-TGndI2U2jAQ/TyRe4aqg0TI/AAAAAAAAaH0/wKiGfyECCz4/image_thumb.png?imgmax=800" width="161" height="37"></a>ITImpact</a> has been building custom databases with Microsoft Access since 1994, serving customers around the world. Ben Clothier has been Microsoft Access MVP since 2009 and is also a SharePoint Technology Specialist. He has worked on business applications from survey tracking to world-wide supply control databases.</p></blockquote> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:5f1da90f-489c-45a6-a236-6e9194aeaaef" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Microsoft+Access" rel="tag">Microsoft Access</a>,<a href="http://technorati.com/tags/SharePoint+2010" rel="tag">SharePoint 2010</a>,<a href="http://technorati.com/tags/SharePoint+Online" rel="tag">SharePoint Online</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-80097823235146119472011-12-18T09:16:00.000-08:002011-12-18T09:25:29.604-08:00New Competition for Access Web Databases: InfoPath 2010 Forms and SharePoint 2010 Online<p><font face="Calibri"><font size="3"><strong>Si Dunn</strong> (@</font></font><a href="http://twitter.com/grumblecore"><font size="3" face="Calibri">grumblecore</font></a><font size="3" face="Calibri">) posted a comprehensive </font><a href="http://sagecreek.wordpress.com/2011/12/14/using-microsoft-infopath-2010-with-microsoft-sharepoint-2010-step-by-step-bookreview/"><font size="3" face="Calibri">review of <em>Using Microsoft InfoPath 2010 with Microsoft SharePoint 2010: Step by Step</em></font></a><font size="3" face="Calibri"> on 12/14/2011:</font></p> <blockquote> <p><strong><a href="http://www.amazon.com/Using-Microsoft-InfoPath-2010-SharePoint/dp/0735662061/ref=as_li_wdgt_js_ex?&linkCode=wsw&tag=sagecreekassocia"><font size="3" face="Calibri"><em>Using Microsoft InfoPath 2010 with Microsoft SharePoint 2010: Step by Step<br></em></font></a></strong><font face="Calibri"><font size="3"><em>By Darvish Shadravan and Laura Rogers</em><br><em>(Microsoft Press, <a href="http://www.amazon.com/Using-Microsoft-InfoPath-2010-SharePoint/dp/0735662061/ref=as_li_wdgt_js_ex?&linkCode=wsw&tag=sagecreekassocia">paperback</a>, list price $34.99; <a href="http://www.amazon.com/Microsoft%C2%AE-InfoPath%C2%AE-SharePoint%C2%AE-Microsoft-ebook/dp/B005Z29QVW/ref=as_li_wdgt_js_ex?&linkCode=wsw&tag=sagecreekassocia">Kindle edition</a>, list price $31.99)</em></font></font> <p><a href="http://lh4.ggpht.com/-W-OcmmKFZ5A/Tu4f2NDJRWI/AAAAAAAAWyE/fTPPnbnHhik/s1600-h/image%25255B6%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-0Ic8YDNTbqU/Tu4f2hX9oPI/AAAAAAAAWyM/4C1NW2I8Sks/image_thumb%25255B2%25255D.png?imgmax=800" width="64" height="64"></a><font face="Calibri"><font size="3"><strong>A 21st century</strong> Shakespeare might write: “All the world’s a form, and we are just filling it in.”</font></font> <p><font size="3" face="Calibri">One of this book’s authors contends (not completely in jest) that “forms run the world. Imagine modern life without forms, both paper and digital–it’s not possible! Everything that is known and recorded about you, from your birth city to your magazine subscriptions, to your preference of aisle or window seats–yes, all of this information was entered in a form at some point in time.”</font> <p><font size="3" face="Calibri"><a href="http://lh5.ggpht.com/-OnxMJgtFk3A/Tu4f26-X6rI/AAAAAAAAWyU/8Faqy7ddYVw/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-gUx8MRyxFGQ/Tu4f3Q3TvsI/AAAAAAAAWyc/94XZwNQOigI/image_thumb.png?imgmax=800" width="82" height="82"></a>Microsoft InfoPath 2010 is used to design and build electronic forms, as well as gather data, without writing code. Meanwhile, SharePoint Server 2010 “offers a robust architecture for managing access to data connections and external systems.” SharePoint is Microsoft’s suite of software tools aimed at making it “easier for people work together,” whether in the same office or scattered around the planet.</font> <p><a href="http://lh5.ggpht.com/-dm3PdmMgOFM/Tu4f3mg41MI/AAAAAAAAWyk/YQElB8juDSk/s1600-h/image%25255B10%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-GSTtXD_nEOk/Tu4f3ms97WI/AAAAAAAAWys/tOTNp3wa26A/image_thumb%25255B4%25255D.png?imgmax=800" width="160" height="34"></a><font face="Calibri"><font size="3"><strong>This well-written</strong> and nicely illustrated book shows how to bring the two products together in powerful ways that (1) enable InfoPath forms to be created and formatted and (2) integrate data from SharePoint and other company systems. InfoPath forms also can be hosted on SharePoint.</font></font> <p><font size="3" face="Calibri">The book is aimed at “any information worker that needs to build and use electronic forms that will be stored in SharePoint.” Its goal is to “teach you the basics of building and using InfoPath 2010 forms in a SharePoint 2010 environment.”</font> <p><font size="3" face="Calibri">The writers assume you are at least a “savvy Office and Windows user.” It is helpful, but not mandatory, to also have at least some basic familiarity with SharePoint Server 2010. “However, even if you’re not a SharePoint guru, most topics in this book should be within your grasp,” they point out.</font> <p><font face="Calibri"><font size="3"><strong>If you do not have</strong> a SharePoint environment in your company, “<strong>InfoPath 2010 supports the creation of forms in Microsoft Office 365</strong>,” the two authors note. </font></font><a href="http://blogs.msdn.com/b/microsoft_press/archive/2011/09/23/free-ebook-microsoft-office-365-connect-and-collaborate-virtually-anywhere-anytime-now-in-more-formats.aspx"><font size="3" face="Calibri">Office 365</font></a><font size="3" face="Calibri"> is Microsoft’s cloud product that provides online access to a variety of programs for communicating and collaborating. [Emphasis added.]</font> <p><font size="3" face="Calibri">InfoPath has been around for a few years and recently was given a significant update. But many businesses and computer users do not have it.</font> <p><font size="3" face="Calibri">That’s not show-stopper when InfoPath and SharePoint work together, the authors point out. ”If you create your forms as browser-enabled form templates, users who don’t have InfoPath installed on their computer can still work with the form in a browser. This lets you share business forms with a variety of users, including employees, customers, and vendors.”</font> <p><font face="Calibri"><font size="3"><strong>The 446-page book</strong> has 14 chapters. The first four chapters show how to create and format forms using InfoPath. The remaining chapters focus on using InfoPath <em>with</em> SharePoint.</font></font> <p><font size="3" face="Calibri">According to the two authors, “the mission of this book is to help you understand how to create business forms that provide a pleasant, reliable, and intuitive experience for your users and customers,” they write.</font> <p><font size="3" face="Calibri">The process of creating, formatting and publishing forms is shown and described in clear, succinct how-to steps. Practice files can be downloaded from a Microsoft site, and the exercise topics range from the basics of form design to building an approval process and working with SharePoint views and dashboards, to (1) “control what fields are displayed at any given time” and (2) “generate reports from any information in SharePoint lists and libraries.”</font> <p><font face="Calibri"><font size="3"><strong>The authors add</strong>: “SharePoint <em>libraries</em>, specifically <em>form libraries</em>, are well suited for storing and managing InfoPath forms.”</font></font> <p><font size="3" face="Calibri">InfoPath’s native language is XML, “perhaps the single most powerful method of storing and sharing structured data to come along since the advent of digital computing.” Creating electronic forms has long been a code-intensive process.</font> <p><font size="3" face="Calibri">InfoPath hides most of the XML behind an easy-to-use interface. And XSLT (Extensible Style Sheet Language) style sheets also “‘sit in front of’ the underlying XML and transform it into the rich and easy-to-use forms that InfoPath can create.”</font> <p><font face="Calibri"><font size="3"><strong>The book’s illustrations</strong>, short paragraphs, step-by-step lists and example files can all help readers get up to speed quickly, whether Microsoft InfoPath 2010 is used with Microsoft SharePoint on a company network or via the cloud, by way of Office 365.</font></font> <p><em><font size="3" face="Calibri">– <strong><a href="http://www.sagecreekproductions.com">Si Dunn</a></strong>‘s latest book is a novel, </font><a href="http://www.amazon.com/Erwins-Erwin-Tennyson-Mystery-ebook/dp/B0055ULNM4/ref=as_li_wdgt_js_ex?&linkCode=wsw&tag=sagecreekassocia"><font size="3" face="Calibri">Erwin’s Law</font></a><font size="3" face="Calibri">. His other published works include </font><a href="http://www.amazon.com/Erwins-Erwin-Tennyson-Mystery-ebook/dp/B0055ULNM4/ref=as_li_wdgt_js_ex?&linkCode=wsw&tag=sagecreekassocia"><font size="3" face="Calibri">Jump</font></a><font size="3" face="Calibri">, a novella, and a book of poetry and several short stories, all available on Kindle. He previously worked in the telecommunications industry as a software and hardware tester and technical writer.</font></em></p></blockquote> <p><font size="3" face="Calibri">My <em><a href="http://www.amazon.com/Introducing-Microsoft%C2%AE-Office-InfoPath-Bpg-Other/dp/0735619522/ref=sr_1_1?s=books&ie=UTF8&qid=1324228847&sr=1-1">Introducing Microsoft Office InfoPath 2003</a></em> book for Microsoft Press is still generating a few royalty checks.</font> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:8b82b71a-fd7a-482a-a4c2-24a455125595" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Access+Web+Databases" rel="tag">Access Web Databases</a>,<a href="http://technorati.com/tags/SharePoint+2010" rel="tag">SharePoint 2010</a>,<a href="http://technorati.com/tags/SharePoint+2010+Server" rel="tag">SharePoint 2010 Server</a>,<a href="http://technorati.com/tags/SharePoint+Online" rel="tag">SharePoint Online</a>,<a href="http://technorati.com/tags/Office+365" rel="tag">Office 365</a>,<a href="http://technorati.com/tags/InfoPath+2010" rel="tag">InfoPath 2010</a>,<a href="http://technorati.com/tags/InfoPath" rel="tag">InfoPath</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com2tag:blogger.com,1999:blog-4760178173405088846.post-39342079110948146022011-11-18T14:38:00.001-08:002011-11-18T14:42:06.558-08:00SQL Server Integration Services 2012 Finally Implements a Pivot UI to Emulate Access Crosstab Queries<p><font size="3" face="Calibri">Microsoft’s <strong>Matt Masson</strong> (@</font><a href="https://twitter.com/#!/mattmasson"><font size="3" face="Calibri">mattmasson</font></a><font size="3" face="Calibri">) described </font><a href="http://blogs.msdn.com/b/mattm/archive/2011/11/17/the-pivot-transform-now-with-ui.aspx"><font size="3" face="Calibri">The Pivot Transform – Now with UI!</font></a><font size="3" face="Calibri"> in an 11/17/2011 post:</font></p> <blockquote> <p><font size="3" face="Calibri">One of the post-CTP3 changes for SSIS in SQL Server 2012 is the addition of a user interface for the Pivot transform. This post walks through the new UI, which can be found in the new </font><a href="http://blogs.msdn.com/b/mattm/archive/2011/11/17/what-s-new-in-ssis-for-sql-server-2012-rc0.aspx"><font size="3" face="Calibri">RC0 preview release.</font></a> <h5><font size="3" face="Calibri">Sample Data</font></h5> <p><font size="3" face="Calibri">For this example, we’ll be pulling data from the AdventureWorks sample database. We’ll be grabbing the total sales of all products in the Accessories category (ProductCategoryID = 4), grouped by year. </font> <p><a href="http://lh3.ggpht.com/-vRWd9P3wIzQ/TsbeW91R5dI/AAAAAAAAU80/2Bqw4sAm7DQ/s1600-h/image%25255B5%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; margin-left: 0px; border-top: 0px; margin-right: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-JJR6k2CfEVY/TsbeXBiBaCI/AAAAAAAAU88/kWOBB0S8VrA/image_thumb%25255B1%25255D.png?imgmax=800" width="504" height="179"></font></a> <p><font size="3" face="Calibri">The data will look something like this:</font> <p><a href="http://lh3.ggpht.com/-zUbQhHHQ4eE/TsbeX1MdaxI/AAAAAAAAU9E/l1Y6q8BPm3M/s1600-h/image%25255B8%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-gxQ2e4bMxU4/TsbeYOvZUXI/AAAAAAAAU9M/RtRo_LU9rO4/image_thumb%25255B2%25255D.png?imgmax=800" width="301" height="132"></font></a></p></blockquote> <p><font size="3" face="Calibri"></font></p> <p><font size="3" face="Calibri"></font></p> <blockquote> <p><font size="3" face="Calibri">We want the end results to be pivoted to look like this (Total product sales by year):</font></p></blockquote> <blockquote><font size="3" face="Calibri"></font></blockquote> <blockquote> <p><a href="http://lh3.ggpht.com/-vXrXXMxo6Fo/TsbeYQwz2dI/AAAAAAAAU9U/fTZ1CaNgGpU/s1600-h/image%25255B11%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-00XV7YmP_AM/TsbeY_6CPeI/AAAAAAAAU9c/1lh-oDNC_Fw/image_thumb%25255B3%25255D.png?imgmax=800" width="403" height="133"></font></a></p> <h5><font size="3" face="Calibri">Setting up the Source</font></h5> <p><font size="3" face="Calibri">Add an OLE DB Source transform, and add a connection manager for the AdventureWorks sample database. Paste in the query to retrieve the total product sales by year:</font> <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/1106.image_5F00_3226BF3F.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/4403.image_5F00_thumb_5F00_3075F36B.png" width="560" height="484"></font></a> <p><font size="3" face="Calibri">Add a Pivot Transform from the SSIS Toolbox (found under the Other Transforms section by default).</font> <p><font size="3" face="Calibri"><img title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/2656.image_5F00_00D7D4DF.png" width="206" height="88"></font> <p><font size="3" face="Calibri">Connect the Source to the Pivot transform. Double click the Pivot transform to open its editor. </font> <p><font size="3" face="Calibri">The Pivot UI shows a sample pivot table at the top of the form, where you configure the <strong>Pivot Key</strong> (the column to use for values across the “top” of the table), the <strong>Set Key</strong> (the column to use for values down the “left” of the table), and the<strong> Pivot Value</strong> (the column to use for the values in the middle). The bottom of the UI is where you configure the pivot key values.</font> <p><font size="3" face="Calibri">After mapping my columns to the appropriate keys, the UI looks like this:</font> <ul> <li><font size="3" face="Calibri">Pivot Key –> Year </font> <li><font size="3" face="Calibri">Set Key –> Product Name </font> <li><font size="3" face="Calibri">Pivot Value –> Total </font></li></ul> <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/1185.image_5F00_6E22DB27.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/7450.image_5F00_thumb_5F00_3EF0EF90.png" width="614" height="484"></font></a> <p><font size="3" face="Calibri">The Pivot transform requires you to enter all of the possible Pivot Key values (so it can create output columns for each one). You can manually enter each key here (in our case, we’d have a value for each year that appears in our data set – 2002, 2003, and 2004). Alternatively, we can click the “Ignore un-matched Pivot Key values” checkbox and run the package as is. The pivot transform will output a log message containing all of the key values that we can then copy and paste into the UI. </font> <ol> <li><font size="3" face="Calibri">Check the “Ignore un-matched Pivot Key values and report them after DataFlow” execution box </font> <li><font size="3" face="Calibri">Click OK to save the changes to the UI </font> <li><font size="3" face="Calibri">Run the package in the designer </font> <li><font size="3" face="Calibri">When the package succeeds, click on the Progress tab </font> <li><font size="3" face="Calibri">Look for an information log message from the Pivot transform which contains the keys </font> <li><font size="3" face="Calibri">Right click the message and select Copy Message Text </font> <li><font size="3" face="Calibri">Click Stop to end the execution </font> <li><font size="3" face="Calibri">Double click the Pivot transform </font> <li><font size="3" face="Calibri">Uncheck the Ignore un-matched Pivot Key values checkbox </font> <li><font size="3" face="Calibri">Paste the Pivot Key values into the bottom text box </font> <li><font size="3" face="Calibri">Trim the text so that it only contains the key values – “[2002],[2003],[2004]” </font> <li><font size="3" face="Calibri">Click the Generate Columns Now button </font></li></ol> <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/1777.image_5F00_3D4023BC.png"><font size="3" face="Calibri"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/8611.image_5F00_thumb_5F00_236BED8D.png" width="413" height="192"></font></a> <p><font size="3" face="Calibri">The Pivot UI should now look like this:</font> <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/1754.image_5F00_691077AB.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/5305.image_5F00_thumb_5F00_0BF1395C.png" width="480" height="378"></font></a> <p><font size="3" face="Calibri">Click OK to save the changes. We’ll add a Row Count transform to the data flow, and connect it to the Pivot’s output. Add a data viewer on the path so we can see the end results. </font> <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/4403.image_5F00_1F31EFFB.png"><font size="3" face="Calibri"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/6064.image_5F00_thumb_5F00_1E598A11.png" width="356" height="319"></font></a> <p><font size="3" face="Calibri">Run the package, and we can see the pivoted results. </font> <p><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/5305.image_5F00_1661E7AF.png"><font size="3" face="Calibri"><img style="display: block; float: none; margin-left: auto; margin-right: auto" title="image" border="0" alt="image" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-74-44-metablogapi/1261.image_5F00_thumb_5F00_22172EE1.png" width="480" height="326"></font></a></p></blockquote> <p><font size="3" face="Calibri">Access’s Crosstab Wizard is still faster and easier to understand.<img src="http://blogs.msdn.com/aggbug.aspx?PostID=10238261" width="1" height="1"></font> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:4610935b-c08d-436f-b12a-eaeda59ff355" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/SQL+Server+Integration+Services+2012" rel="tag">SQL Server Integration Services 2012</a>,<a href="http://technorati.com/tags/SQL+Server+2012" rel="tag">SQL Server 2012</a>,<a href="http://technorati.com/tags/Microsoft+Access" rel="tag">Microsoft Access</a>,<a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+Crosstab+Wizard" rel="tag">Access Crosstab Wizard</a>,<a href="http://technorati.com/tags/Crosstab+Queries" rel="tag">Crosstab Queries</a>,<a href="http://technorati.com/tags/PIVOT+Transform" rel="tag">PIVOT Transform</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-73607019087691126012011-11-15T12:34:00.001-08:002011-11-15T12:34:36.247-08:00Steven Thomas Reported Full-featured Access Services Solution at http://www.accesshosting.com on 11/14/2011<blockquote> <p><a href="http://lh5.ggpht.com/-WSQm1QxyBFQ/TsLM2MuWaOI/AAAAAAAAUs4/Clp8aciFRlw/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-PB8roxUFV7c/TsLM23YE4NI/AAAAAAAAUtA/jO2BibkAnwo/image_thumb.png?imgmax=800" width="74" height="72"></a>Our friends at AccessHosting.com are offering free trials of two Access "cloud" solutions. Curious? Here's the deal, <a href="http://www.accesshosting.com/office365promo.asp">from the source</a>. <p>Offer text provided by AccessHosting.com: <p class="MsoNormal"><em><b>Reporting services for Access Web Databases in SharePoint 2010 now available</b> - Office 365 users looking web based reporting functionality can republish their databases to accesshosting.com and take advantage of a new introductory price for hosted Access 2010 web databases published to SharePoint 2010.</em></p> <p><em><b>Remote Desktop Services for Access Applications</b> - Looking to move your existing Access 2003, 2007 and 2010 applications into the cloud quickly and easily? Our Remote Desktop solution provides a simple, secure way to connect multiple external and internal users to your Access databases. You can also use this service to deliver legacy Access applications on the iPad and Android powered devices. </em> <p><b><em></em></b> <p><b><em>Both of these solutions are available as a no obligation 30-day trial for qualified customers. Visit </em><a href="http://www.accesshosting.com/"><em>www.accesshosting.com</em></a><em> for complete details.</em></b></p></blockquote> <p>Read Steve’s original post <a href="http://blogs.office.com/b/microsoft-access/archive/2011/11/14/access-services-free-trial-accesshosting.com-in-browser-reports.aspx">here</a>. <p>I’ve used AccessHosting’s services for several cloud-based projects. See my following posts: <ul> <li><a href="http://accessindepth.blogspot.com/2011/03/upsizing-northwind-web-database-to.html">Upsizing the Northwind Web Database to an Updated SharePoint 2010 Server Hosted by AccessHosting.com</a> (3/18/2011)</li> <li><a href="http://accessindepth.blogspot.com/2011/03/kay-eubank-reviews-access-2010-in-depth.html">Kay Eubank Reviews “Access 2010 In Depth” for the I Programmer Web Site</a> (3/15/2011)</li> <li><a href="http://accessindepth.blogspot.com/2011/07/migrate-access-2000-or-later-databases.html">Migrate Access 2000 or Later Databases to Public or Private Rollbase Clouds</a> (7/17/2011)</li> <li><a href="http://accessindepth.blogspot.com/2011/03/access-web-databases-on.html">Access Web Databases on AccessHosting.com: Adding User Logins and Assigning Permissions</a> (3/14/2011)</li> <li><a href="http://accessindepth.blogspot.com/2011/08/sharepoint-primer-for-access-developers.html">A SharePoint 2010 Primer for Access 2010 Developers by Ben Clothier</a> (8/1/2011)</li> <li><a href="http://accessindepth.blogspot.com/2011/03/what-is-odata-and-why-should-i-care.html">Access Web Databases on AccessHosting.com: What is OData and Why Should I Care?</a> (3/16/2011)</li></ul> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:3b870181-7cf4-463b-85f4-b2187193be61" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Microsoft+Access" rel="tag">Microsoft Access</a>,<a href="http://technorati.com/tags/Access" rel="tag">Access</a>,<a href="http://technorati.com/tags/Access+Hosting" rel="tag">Access Hosting</a>,<a href="http://technorati.com/tags/SharePoint+2010" rel="tag">SharePoint 2010</a>,<a href="http://technorati.com/tags/SharePoint" rel="tag">SharePoint</a>,<a href="http://technorati.com/tags/SharePoint+Lists" rel="tag">SharePoint Lists</a>,<a href="http://technorati.com/tags/OData" rel="tag">OData</a>,<a href="http://technorati.com/tags/Access+Services" rel="tag">Access Services</a>,<a href="http://technorati.com/tags/Access+Web+Databases" rel="tag">Access Web Databases</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-76246806549073972292011-08-25T08:57:00.001-07:002011-08-25T09:06:11.467-07:00Creating SQLAzure Logins and User Accounts with ODBC and Access VBA<p><font face="Calibri"><font size="3"><strong>Patrick Wood</strong> (</font></font><a href="https://twitter.com/#!/patrickawood"><font size="3" face="Calibri">@patrickawood</font></a><font size="3" face="Calibri">) explained </font><a href="http://gainingaccess.net/Articles/HowToCreateSQLAzureLogins.aspx"><font size="3" face="Calibri">How to Use Microsoft Access to Create Logins in a SQL Azure Database</font></a><font size="3" face="Calibri"> in an 8/2/2011 article for his Gaining Access site (missed when published):</font></p> <blockquote> <p><a href="http://lh4.ggpht.com/-NcCN-t26EpA/TlZw4HftkuI/AAAAAAAAOvE/NRrU3dq8KKQ/s1600-h/image%25255B2%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/-GQTmzxw0PCI/TlZw4voEW7I/AAAAAAAAOvI/9zIDLA215w4/image_thumb.png?imgmax=800" width="73" height="73"></font></a><font size="3" face="Calibri">In this article we will demonstrate how you can use a Pass-through query in Access with VBA code to create SQL Azure Logins. Microsoft Access can then use the Login and Password to gain access to SQL Azure Tables, Views, and Stored Procedures. We will create a Login using SQL in Access similar to the following Transact-SQL (T-SQL): </font> <p><font size="3" face="Calibri">CREATE LOGIN MyLoginName WITH password = 'zX/w3-q7jU' </font> <p><a href="http://lh6.ggpht.com/-qwLqbnlRpgM/TlZw5L1X4QI/AAAAAAAAOvM/vkop47eE-KM/s1600-h/image%25255B8%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/-qSke4sNweNs/TlZw5c3FJTI/AAAAAAAAOvQ/mmVewLwYslM/image_thumb%25255B2%25255D.png?imgmax=800" width="85" height="80"></font></a><font size="3" face="Calibri">Thankfully, a user would never have to memorize that password! Because this Login and password would only be used by my Access application the user never sees it and does not even know it exists. </font> <p><a href="http://lh6.ggpht.com/-AT8pmCoZQMk/TlZxUxmoRlI/AAAAAAAAOvk/FSlJh7kglPE/s1600-h/image%25255B14%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-pN3Tayqw-Jg/TlZxVDYnC6I/AAAAAAAAOvo/sz9a8SqRZmM/image_thumb%25255B4%25255D.png?imgmax=800" width="159" height="47"></a><font size="3" face="Calibri">There are several steps involved in creating a Login and Password for SQL Azure. And although most T-SQL that is used in SQL Azure is exactly the same as that used with SQL Server there are some critical differences which we will address in the following steps. </font> <h5><font size="3" face="Calibri">1) Create a Strong Password that Meets the Requirements of the </font><a href="http://msdn.microsoft.com/en-us/library/ms161959.aspx"><font size="3" face="Calibri">Password Policy</font></a><font size="3" face="Calibri">. </font></h5> <p><font size="3" face="Calibri">It is very important to use </font><a href="http://msdn.microsoft.com/en-us/library/ms161962.aspx"><font size="3" face="Calibri">Strong Passwords</font></a><font size="3" face="Calibri"> because the extra security is needed since we cannot use Windows Authentication with SQL Azure. Passwords must be at least 8 characters long and contain at least one number or special character such as -/~^&. </font> <h5><font size="3" face="Calibri">2) Use Characters That Do Not Conflict With ODBC Connection Strings. </font></h5> <p><font size="3" face="Calibri">To avoid errors we should not use these ODBC connection string characters []{}(),;?*!@ in our Login Name and Password. </font> <h5><font size="3" face="Calibri">3) Build a Transact-SQL Statement Which Will Create the Login. </font></h5> <p><font size="3" face="Calibri">We will use the T-SQL CREATE LOGIN statement in a Pass-through query to create the Login. Since Pass-through queries "pass" the SQL unaltered to SQL Azure most of the time the SQL is just like what we would in SQL Server Management Studio (SSMS) and as seen here: </font> <p><font size="3" face="Calibri"><font size="2" face="Courier New">CREATE LOGIN MyLoginName WITH password = 'zX/w3-q7jU'</font> </font> <p><font size="3" face="Calibri">Another requirement of the CREATE LOGIN statement is that </font><a href="http://msdn.microsoft.com/en-us/library/ee336268.aspx"><font size="3" face="Calibri">it must be the only statement in a SQL batch</font></a><font size="3" face="Calibri">. So we are only going to create one Login at a time. </font> <h5><font size="3" face="Calibri">4) Ensure the Login and Password Are Created In the master Database. </font></h5> <p><font size="3" face="Calibri">This is required because "USE master" does not work in SQL Azure as it does with SQL Server because <b>the USE statement is not supported in SQL Azure</b>. But with Access we can create the Login in the master database by specifying the master database in our Connection String: "DATABASE=master;". We use a Function like the one below to get the Connection String with an obfuscated name to keep it more secure. </font></p> <p><font size="2" face="Courier New">Public Function obfuscatedFunctionName() As String<br>obfuscatedFunctionName = "ODBC;" _<br>& "DRIVER={SQL Server Native Client 10.0};" _<br>& "SERVER=tcp:MyServerName.database.windows.net,1433;" _<br>& "UID=MyUserName@MyServerName;" _<br>& "PWD=MyPassword;" _<br>& "DATABASE=master;" _<br>& "Encrypt=Yes"<br>End Function</font></p> <p><font size="3" face="Calibri">See my article </font><a href="http://gainingaccess.net/SQLAzure/SQLAzureSecurity1.aspx"><font size="3" face="Calibri">Building Safer SQL Azure Cloud Applications with Microsoft Access</font></a><font size="3" face="Calibri"> for more information about securing your Access application. </font> <h5><font size="3" face="Calibri">5) Create a Function to Execute the SQL and Create the Login. </font></h5> <p><font size="3" face="Calibri">Place the ExecuteMasterDBSQL Function below in a Standard Module. This Function executes our CREATE LOGIN statement. It can be used any time you want to execute a T-SQL statement in the SQL Azure master database that does not return records. The Function returns True if the SQL was executed successfully or False if the SQL fails to be executed. </font></p> <p><font size="2" face="Courier New">'This procedure executes Action Query SQL in the SQL Azure master database.<br>'Example usage: Call ExecuteMasterDBSQL(strSQL) or If ExecuteMasterDBSQL(strSQL) = False Then<br>'<br>Function ExecuteMasterDBSQL(strSQL As String) As Boolean<br>On Error GoTo ErrHandle<br>Dim db As DAO.Database<br>Dim qdf As DAO.QueryDef<br>ExecuteMasterDBSQL = False 'Default Value<br>Set db = CurrentDb<br>'Create a temporary unnamed Pass-through QueryDef. This is a<br>'practice recommended in the Microsoft Developer Reference.<br>'The order of each line of code must not be changed or the code will fail.<br>Set qdf = db.CreateQueryDef("")<br>'Use a function to get the SQL Azure Connection string to the master database<br>qdf.Connect = obfuscatedFunctionName<br>'Set the QueryDef's SQL as the strSQL passed in to the procedure<br>qdf.SQL = strSQL<br>'ReturnsRecords must be set to False if the SQL does not return records<br>qdf.ReturnsRecords = False<br>'Execute the Pass-through query<br>qdf.Execute dbFailOnError<br>'If no errors were raised the query was successfully executed<br>ExecuteMasterDBSQL = True<br>ExitHere:<br>'Cleanup for security and to release memory<br>On Error Resume Next<br>Set qdf = Nothing<br>Set db = Nothing<br>Exit Function<br>ErrHandle:<br>MsgBox "Error " & Err.Number & vbCrLf & Err.Description _ <br>& vbCrLf & "In procedure ExecuteMasterDBSQL"<br>Resume ExitHere <br>End Function</font></p> <h5><font size="3" face="Calibri">6) Use a Form to Enter the Login Name and Password </font></h5> <p><font size="3" face="Calibri">We can make it easy for users to create a Login by using a form. To do this we need to add two text boxes and a command button to the form. Both text boxes need to be unbound. Name the text box for the Login Name txtLoginName. Name the text box for the Password txtPassword. Name the command button cmdCreateLogin. The form should look something like this, but without the extra touches for appearance sake. </font> <p><font size="3" face="Calibri"><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Create Logins Form" src="http://gainingaccess.net/articleimages/frmCreateLogin.png"></font> <p><font size="3" face="Calibri">Add the code below to the command button's Click event. After the code verifies that a Login Name and Password has been entered, it calls the ExecuteMasterDBSQL Function to create the Login in our SQL Azure master database. </font></p> <p><font size="2" face="Courier New">Private Sub cmdCreateLogin_Click()<br>'Prepare a Variable to hold the SQL statement<br>Dim strSQL As String<br>'Build the SQL statement<br>strSQL = "CREATE LOGIN " & Me.txtLoginName & " WITH password = '" & Me.txtPassword & "'"<br>'Verify both a Login Name and a Password has been entered.<br>If Len(Me.txtLoginName & vbNullString) = 0 Then<br>'A Login Name has not been entered.<br>MsgBox "Please enter a value in the Login Name text box.", vbCritical<br>Else<br>'We have a Login Name, verify a Password has been entered.<br>If Len(Me.txtPassword & vbNullString) = 0 Then<br>'A Password has not been entered.<br>MsgBox "Please enter a value in the Password text box.", vbCritical<br>Else<br>'We have a Login Name and a Password.<br>'Create the Login by calling the ExecuteMasterDBSQL Function.<br>If ExecuteMasterDBSQL(strSQL) = False Then<br>MsgBox "The Login failed to be created.", vbCritical<br>Else<br>MsgBox "The Login was successfully created.", vbInformation<br>End If<br>End If<br>End If<br>End Sub</font></p> <p><font size="3" face="Calibri">The code in the Form checks the return value of the ExecuteMasterDBSQL Function and informs us whether or not the Login was successfully created. Once we have created a Login we can create a Database User for the Login and grant the User access to the data in the SQL Azure Database. Creating a Database User for the Login appears to be a good subject for another article. [See article below.]</font></p></blockquote> <p><font size="3" face="Calibri"> <hr> </font></p> <p><font size="3" face="Calibri">Patrick continued with </font><a href="http://gainingaccess.net/Articles/CreateSQLAzureDBUsers.aspx"><font size="3" face="Calibri">How to Create SQL Azure Database Users With Microsoft Access VBA</font></a><font size="3" face="Calibri"> on 8/23/2011:</font></p> <blockquote> <p><a href="http://lh5.ggpht.com/-hI4xI_s1t5k/TlZw52PPwaI/AAAAAAAAOvU/YeCpm_bdtZg/s1600-h/image%25255B5%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/--VgX9z6kxng/TlZw6N1vPRI/AAAAAAAAOvY/R9zcqnFQaos/image_thumb%25255B1%25255D.png?imgmax=800" width="73" height="73"></font></a><font size="3" face="Calibri">Why do we need a SQL Azure Database User Account? An Access Database cannot access SQL Azure Objects such as Tables, Views, or Stored Procedures without one. That is, unless you use your SQL Azure Administrator account which would be living very dangerously if [you] were distributing your database to others. So before your database can do anything with SQL Azure, a Database User Account must be created that it can use. Also permissions must be granted to use the necessary SQL Azure Tables, Views, and Stored Procedures. We are going to show you how you can use a pass-through query in Access to create SQL Azure Database Users using Access VBA. </font> <p><a href="http://lh6.ggpht.com/-6Px70rbYvoo/TlZw6nPhQJI/AAAAAAAAOvc/V4d-C-qLlvQ/s1600-h/image%25255B11%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-9LxXnD_i9cI/TlZw6zJpnLI/AAAAAAAAOvg/87U28FU6Ppw/image_thumb%25255B3%25255D.png?imgmax=800" width="85" height="80"></font></a><font size="3" face="Calibri">Database users must be created in the database in which they will exist because the "USE" statement can only work for the current database in SQL Azure. So to create a Database User we must use a query that runs in the Database in which we want to create the User. And since it would be confusing, to me at least, to log in using "JoeDeveloper" and work as Database User "SamCodeSlinger" my normal practice is to create a Database User with the same name as a Login Name. </font> <p><a href="http://lh5.ggpht.com/-y9qjBBQSJVg/TlZxVnh0WCI/AAAAAAAAOvs/R4PETsiSOfc/s1600-h/image%25255B17%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh4.ggpht.com/-7Di5eWTljcw/TlZxWA81HzI/AAAAAAAAOvw/XARvSO1YO5E/image_thumb%25255B5%25255D.png?imgmax=800" width="159" height="47"></a><font size="3" face="Calibri">If we were using SQL Server Management Studio (SSMS) or the </font><a href="https://windows.azure.com/"><font size="3" face="Calibri">Windows Azure Management Portal</font></a><font size="3" face="Calibri"> we could create a Database User as shown with the following Transact-SQL (T-SQL): </font> <p><font size="3" face="Calibri"><font size="2" face="Courier New">CREATE USER MyLoginName FOR LOGIN MyLoginName</font> </font> <p><font size="3" face="Calibri">Or: </font> <p><font size="3" face="Calibri"><font size="2" face="Courier New">CREATE USER MyLoginName FROM LOGIN MyLoginName</font> </font> <p><font size="3" face="Calibri">But you can easily create Database Users with Microsoft Access using the following two procedures, passing the Login Name to the CreateSQLAzureDBUser Function: </font></p><br><font size="2" face="Courier New">'Example usage: Call CreateSQLAzureDBUser("MyLoginName")<br>Public Function CreateSQLAzureDBUser(strLoginName As String) As Boolean<br>On Error GoTo ErrHandle<br>Dim db As DAO.Database<br>Dim qdf As DAO.QueryDef<br>Dim strSQL As String<br>CreateSQLAzureDBUser = False 'Default Value<br>strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName<br>'Create the Database User<br>Set db = CurrentDb<br>Set qdf = db.CreateQueryDef("")<br>'Change obfuscatedFunctionName to the name of a Function<br>'that Returns your SQL Azure Database Connection String<br>qdf.Connect = obfuscatedFunctionName<br>qdf.SQL = strSQL<br>qdf.ReturnsRecords = False<br>qdf.Execute dbFailOnError<br>'If no errors the Database User was Created<br>CreateSQLAzureDBUser = True<br>ExitHere:<br>'Cleanup for security and to release memory<br>On Error Resume Next<br>Set qdf = Nothing<br>Set db = Nothing<br>Exit Function<br>ErrHandle:<br>MsgBox "Error " & Err.Number & vbCrLf & Err.Description _<br>& vbCrLf & "In procedure CreateSQLAzureDBUser"<br>Resume ExitHere<br>End Function</font> <p><font size="3" face="Calibri">The Database User must be created in the Database in which it is to be used and not in the master Database. You can do this by changing MySQLAzureDatabaseName in the obfuscatedFunctionName Function to the name of the database in which you want to create the users. </font> <p><font size="2" face="Courier New">'It is best to change the name of this procedure for better security for your use.<br>'The strIn Argument value, "Wb_gR%/PD\-k&yZq~j>l", is used like a Password to keep<br>'unauthorized users from getting your Connection String. You should also change it<br>'to suit you before you use it in a distributed application.<br>Public Function obfuscatedFunctionName(strIn As String) As String<br>If strIn = "Wb_gR%/PD\-k&yZq~j>l" Then<br>obfuscatedFunctionName = "ODBC;" _<br>& "DRIVER={SQL Server Native Client 10.0};" _<br>& "SERVER=tcp:MyServerName.database.windows.net,1433;" _<br>& "UID=MyUserName@MyServerName;" _<br>& "PWD=MyPassword;" _<br>& "DATABASE=MySQLAzureDatabaseName;" _<br>& "Encrypt=Yes"<br>Else<br>obfuscatedFunctionName = vbNullString<br>End If<br>End Function</font></p> <p><font size="3" face="Calibri">For better security you can keep the Login Name, Password, and User Name hidden in the code without exposing it to the Access user. </font> <p><font size="3" face="Calibri">We now have the ability to create Database Users but we still need to Grant Permissions before our Access Database can use this User Account to access any data in SQL Azure. We plan to show how you can do that in the articles to come. </font> <p><font size="3" face="Calibri">You can download the code used in this article from our </font><a href="http://gainingaccess.net/GainingAccess/CodeSamples.aspx"><font size="3" face="Calibri">Free Code Samples page</font></a><font size="3" face="Calibri">. </font></p></blockquote> <hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:e32c03d4-6fa1-4181-98b9-29c271a44076" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Microsoft+Access" rel="tag">Microsoft Access</a>,<a href="http://technorati.com/tags/Access+VBA" rel="tag">Access VBA</a>,<a href="http://technorati.com/tags/ODBC" rel="tag">ODBC</a>,<a href="http://technorati.com/tags/SQL+Azure" rel="tag">SQL Azure</a>,<a href="http://technorati.com/tags/Windows+Azure" rel="tag">Windows Azure</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-27982176630065324462011-08-07T11:34:00.001-07:002011-08-07T11:36:58.871-07:00Visual Studio LightSwitch as a Microsoft Access Upgrade Path<p><font face="Calibri"><font size="3"><strong>Jeffrey Palermo</strong> (</font></font><a href="https://twitter.com/#!/jeffreypalermo"><font size="3" face="Calibri">@jeffreypalermo</font></a><font size="3" face="Calibri">) described </font><a href="http://www.headspring.com/2011/07/visual-studio-lightswitch-and-upgrade-path-for-microsoft-access-to-net"><font size="3" face="Calibri">Visual Studio LightSwitch, an Upgrade Path for Microsoft Access</font></a><font size="3" face="Calibri"> in a 7/30/2011 post to the Headspring blog:</font> <blockquote> <p><a href="http://lh3.ggpht.com/-YZjDiuUjDoE/Tj7PHYPbFAI/AAAAAAAAOEQ/xIFkAn6C5VY/s1600-h/image%25255B78%25255D.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-zy5FpIGK_dM/Tj7PH37g2hI/AAAAAAAAOEU/h4JzXsuHN-k/image_thumb%25255B34%25255D.png?imgmax=800" width="57" height="66"></font></a><font size="3" face="Calibri">There are lots of business systems written in Microsoft Access. One of the most successful companies I know is </font><a href="http://www.fairsoftware.com/aboutgladstone.aspx"><font size="3" face="Calibri">Gladstone, Inc</font></a><font size="3" face="Calibri">, makers of </font><a href="http://www.fairsoftware.com/"><font size="3" face="Calibri">ShoWorks</font></a><font size="3" face="Calibri"> software. This software runs most of the county fairs in the U.S. From entries, to checks, to vendors, this piece of software does it all to help manage and run a fair and keep track of all the data. And it is written in Access. </font></p> <p><a href="http://lh4.ggpht.com/-tqM7_X415B4/Tj7aywCOW7I/AAAAAAAAOEY/FF06waXZDLM/s1600-h/image%25255B2%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-kF3kYntKejQ/Tj7azakSFJI/AAAAAAAAOEc/Oqj2WkP0ZB0/image_thumb.png?imgmax=800" width="87" height="86"></a><font size="3" face="Calibri">Started on Access 97, I have watched this software grow through the various Access upgrades, and it tests the limits of the platform. It’s author, </font><a href="http://www.hnatt.com/"><font size="3" face="Calibri">Mike Hnatt</font></a><font size="3" face="Calibri">, is one of the premiere Access gurus, and Microsoft has previous invited him up to the Redmond campus to be a part of internal Software Design Reviews, or SDR’s. Mike knows the limits of access, but even with the vast array of other development options out there, nothing comes close to parity with the capabilities he relies on – until today.</font></p> <p><a href="http://www.headspring.com/wp-content/uploads/2011/07/image9.png"><font size="3" face="Calibri"><img border="0" alt="image" src="http://www.headspring.com/wp-content/uploads/2011/07/image_thumb9.png" width="524" height="333"></font></a> <p><img title="image222422222222" border="0" alt="image222422222222" align="right" src="http://lh6.ggpht.com/-L6ub3Hq969o/TgtZjx-b8zI/AAAAAAAAMWo/lzRnjSNtm2k/image2224222222222.png?imgmax=800" width="169" height="53"><font size="3" face="Calibri">This is my first </font><a href="http://www.microsoft.com/visualstudio/en-us/lightswitch"><font size="3" face="Calibri">LightSwitch</font></a><font size="3" face="Calibri"> application. I just installed the software, ran it, defined a table structure and a few screens. It’s really simple, and I see that i runs a desktop version of Silverlight. It feels like Access (I have done some of that programming earlier in my career) because you just define the tables and queries, and then ask for screens that work off the data. You can customize the screens to some degree, and you can write code behind the screens, just like you can write VBA behind Access screens. This is my first time looking at Lightswitch in a serious way since it was just released. I will be looking at it more because it belongs in our toolbelt at </font><a href="http://www.headspring.com"><font size="3" face="Calibri">Headspring</font></a><font size="3" face="Calibri">. There are plenty of clients who have Access and FoxPro systems. These systems have tremendously useful built-in functionality that is prohibitively expensive to duplicate in a custom way with raw WPF and C#, but Lightswitch provides a possible upgrade path that won’t break the bank.</font> <p><font size="3" face="Calibri">In case you are wondering what it looks like to develop this, here it is.</font> <p><a href="http://www.headspring.com/wp-content/uploads/2011/07/image10.png"><font size="3" face="Calibri"><img border="0" alt="image" src="http://www.headspring.com/wp-content/uploads/2011/07/image_thumb10.png" width="510" height="304"></font></a> <p><font size="3" face="Calibri">Notice that there is a Solution Explorer, and you are in Visual Studio with a new project type. I was really pleased that I could write code easily.</font> <p><a href="http://www.headspring.com/wp-content/uploads/2011/07/image11.png"><font size="3" face="Calibri"><img border="0" alt="image" src="http://www.headspring.com/wp-content/uploads/2011/07/image_thumb11.png" width="513" height="315"></font></a> <p><font size="3" face="Calibri">I tried some </font><a href="http://www.jetbrains.com/resharper/"><font size="3" face="Calibri">ReSharper</font></a><font size="3" face="Calibri"> shortcuts, but they didn’t work. I guess we’ll have to wait for ReSharper to enable this project type. Here is my custom button that shows the message box.</font> <p><a href="http://www.headspring.com/wp-content/uploads/2011/07/image12.png"><font size="3" face="Calibri"><img style="display: block; float: none; margin-left: auto; margin-right: auto" border="0" alt="image" src="http://www.headspring.com/wp-content/uploads/2011/07/image_thumb12.png" width="513" height="302"></font></a> <p><font size="3" face="Calibri">I think LightSwitch as a lot of promise for legacy system rewrites, upgrades, and conversions. Because it’s 100% .Net, you can mix and match with web services, desktop, SQL Server, etc.</font></p></blockquote> <p><font size="3" face="Calibri">Jeffrey is COO of Headspring.</font> <p><font size="3" face="Calibri">It’s nice to see a well-known and respected .NET developer give credit to Microsoft Access where it’s due.</font> <p> <hr> </p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:f1aff095-0308-4395-aa4a-a650d1991fa4" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Visual+Studio+LightSwitch+2011" rel="tag">Visual Studio LightSwitch 2011</a>,<a href="http://technorati.com/tags/Visual+Studio+LightSwitch" rel="tag">Visual Studio LightSwitch</a>,<a href="http://technorati.com/tags/Upsizing+Access+Databases" rel="tag">Upsizing Access Databases</a>,<a href="http://technorati.com/tags/Access+Web+Databases" rel="tag">Access Web Databases</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com2tag:blogger.com,1999:blog-4760178173405088846.post-44096362612784083382011-08-05T08:29:00.001-07:002011-08-05T08:32:43.982-07:00Office 365 Video: Updating an Access database using SharePoint Online<p><font face="Calibri"><font size="3"><strong>Chris</strong> from the Microsoft SharePoint Content Team posted </font></font><a href="http://sharepoint.microsoft.com/Blogs/GetThePoint/Lists/Posts/Post.aspx?ID=495"><font size="3" face="Calibri">Office 365 Video: Updating an Access database using SharePoint Online</font></a><font size="3" face="Calibri"> to the SharePoint for End Users blog on 8/2/2011:</font></p> <blockquote> <p><font size="3" face="Calibri">Are you a Microsoft Access 2010 user? With Microsoft Office 365, you can use your Windows 7 smartphone to read and update a team database remotely. </font></p> <p><object style="height: 300px; width: 480px"><param name="movie" value="http://www.youtube.com/v/13-ckYp9ku0?version=3"><param name="allowFullScreen" value="true"><param name="allowScriptAccess" value="always"><embed src="http://www.youtube.com/v/13-ckYp9ku0?version=3" type="application/x-shockwave-flash" allowfullscreen="true" allowScriptAccess="always" width="480" height="300"></object><font size="3" face="Calibri"></font></p> <p><font face="Calibri"><font size="3"><strong>Important</strong>: The services and features that this video shows depend on several factors, such as the Microsoft Office 365 plan purchase agreement, licenses that might be assigned to each user, and specific user permissions. If your experience is different from the experience shown in the video, see your site administrator. </font></font> <p><font face="Calibri"><font size="3"><strong>Prerequisites</strong>: This scenario requires Microsoft Office 365 for enterprises with Enterprise Services enabled; Microsoft Access on a single client computer. </font></font> <p><font size="3" face="Calibri">To learn more about Access and SharePoint, check out the topics below: </font> <ul> <li><a href="http://office.microsoft.com/en-us/sharepoint-online-enterprise-help/redir/HA102435342.aspx?CTT=5&origin=VA102590226"><font size="3" face="Calibri">Build an Access database to share on the Web</font></a><font size="3" face="Calibri"> </font> <li><a href="http://office.microsoft.com/en-us/sharepoint-online-enterprise-help/redir/HA010341568.aspx?CTT=5&origin=VA102590226"><font size="3" face="Calibri">Create a form by using the Form Wizard</font></a><font size="3" face="Calibri"> </font></li></ul> <p><font size="3" face="Calibri">As always, let us know what you think!</font></p></blockquote> <p><font size="3" face="Calibri">For my detailed Webcasts about upsizing Access *.accdb databases to Web Databases that run with the version of SharePoint Online that comes with the US$6.00/month version of Microsoft Office 365 online, read </font><a href="http://accessindepth.blogspot.com/2011/05/learn-how-to-create-access-web.html"><font size="3" face="Calibri">Learn How To Create Access Web Databases with Office 365’s SharePoint Online from my Latest Webcast</font></a><font size="3" face="Calibri"> of 5/27/2011.</font></p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:b04f0409-46f4-47ea-bb81-8c1f36fbf242" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Access+Web+Databases" rel="tag">Access Web Databases</a>,<a href="http://technorati.com/tags/SharePoint+Online" rel="tag">SharePoint Online</a>,<a href="http://technorati.com/tags/SharePoint+2010" rel="tag">SharePoint 2010</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-87694950499242064532011-08-04T16:29:00.001-07:002011-08-04T16:30:16.671-07:00Create Logins in a SQL Azure Database with Access<p><font face="Calibri"><font size="3"><strong>Patrick Wood</strong> described </font></font><a href="http://accesstips.wordpress.com/2011/08/01/how-to-use-microsoft-access-to-create-logins-in-a-sql-azure-database/"><font size="3" face="Calibri">How to Use Microsoft Access to Create Logins in a SQL Azure Database</font></a><font size="3" face="Calibri"> in an 8/1/2011 post:</font> <blockquote> <p><a href="http://lh6.ggpht.com/-J79FQ92mcnQ/TjrsgGWz52I/AAAAAAAAN4g/mpDIo0e1OTI/s1600-h/image%25255B131%25255D.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/-HqBkQ1jfKe4/TjrsidIpj_I/AAAAAAAAN4k/Hcltt0vQYLY/image_thumb%25255B50%25255D.png?imgmax=800" width="65" height="65"></font></a><font size="3" face="Calibri">In this article we will demonstrate how you can use a Pass-through query in Access with VBA code to create SQL Azure Logins. Microsoft Access can then use the Login and Password to gain access to SQL Azure Tables, Views, and Stored Procedures. We will create a Login using SQL in Access similar to the following Transact-SQL (T-SQL): </font> <p><font size="3" face="Calibri">CREATE LOGIN MyLoginName WITH password = ‘zX/w3-q7jU’ </font> <p><a href="http://lh5.ggpht.com/-QN1FLftQQ7c/Tjrsi3Vn3MI/AAAAAAAAN4o/E2mzPh2tFoY/s1600-h/image%25255B134%25255D.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-_Or63yTIEAA/TjrskIo4RyI/AAAAAAAAN4s/2-hFJltmM7g/image_thumb%25255B51%25255D.png?imgmax=800" width="65" height="64"></font></a><font size="3" face="Calibri">Thankfully, a user would never have to memorize that password! Because this Login and password would only be used by my Access application the user never sees it and does not even know it exists. </font> <p><a href="http://lh5.ggpht.com/-oYTotQerPg0/Tjrskv0cRwI/AAAAAAAAN4w/8SQl9HyUe1E/s1600-h/image%25255B128%25255D.png"><font size="3" face="Calibri"><img title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-E5K4ojBE8zc/Tjrsk5hxenI/AAAAAAAAN40/_T-RDOfeMFo/image_thumb%25255B49%25255D.png?imgmax=800" width="159" height="47"></font></a><font size="3" face="Calibri">There are several steps involved in creating a Login and Password for SQL Azure. And although most T-SQL that is used in SQL Azure is exactly the same as that used with SQL Server there are some critical differences which we will address in the following steps. </font> <p><font size="3" face="Calibri">1) Create a Strong Password that Meets the Requirements of the </font><a href="http://msdn.microsoft.com/en-us/library/ms161959.aspx"><font size="3" face="Calibri">Password Policy</font></a><font size="3" face="Calibri">. </font> <p><font size="3" face="Calibri">It is very important to use </font><a href="http://msdn.microsoft.com/en-us/library/ms161962.aspx"><font size="3" face="Calibri">Strong Passwords</font></a><font size="3" face="Calibri"> because the extra security is needed since we cannot use Windows Authentication with SQL Azure. Passwords must be at least 8 characters long and contain at least one number or special character such as -/~^&. </font> <p><font size="3" face="Calibri">2) Use Characters That Do Not Conflict With ODBC Connection Strings. </font> <p><font size="3" face="Calibri">To avoid errors we should not use these ODBC connection string characters []{}(),;?*!@ in our Login Name and Password. </font> <p><font size="3" face="Calibri">3) Build a Transact-SQL Statement Which Will Create the Login. </font> <p><font size="3" face="Calibri">We will use the T-SQL CREATE LOGIN statement in a Pass-through query to create the Login. Since Pass-through queries “pass” the SQL unaltered to SQL Azure most of the time the SQL is just like what we would in SQL Server Management Studio (SSMS) and as seen here: </font> <p><font size="3" face="Calibri">CREATE LOGIN MyLoginName WITH password = ‘zX/w3-q7jU’ </font> <p><font size="3" face="Calibri">Another requirement of the CREATE LOGIN statement is that </font><a href="http://msdn.microsoft.com/en-us/library/ee336268.aspx"><font size="3" face="Calibri">it must be the only statement in a SQL batch</font></a><font size="3" face="Calibri">. So we are only going to create one Login at a time. </font> <p><font size="3" face="Calibri">4) Ensure the Login and Password Are Created In the master Database. </font> <p><font size="3" face="Calibri">This is required because “USE master” does not work in SQL Azure as it does with SQL Server because <b>the USE statement is not supported in SQL Azure</b>. But with Access we can create the Login in the master database by specifying the master database in our Connection String: “DATABASE=master;”. We use a Function like the one below to get the Connection String with an obfuscated name to keep it more secure. </font><pre>Public Function obfuscatedFunctionName() As String<br /> obfuscatedFunctionName = "ODBC;" _<br /> & "DRIVER={SQL Server Native Client 10.0};" _<br /> & "SERVER=tcp:MyServerName.database.windows.net,1433;" _<br /> & "UID=MyUserName@MyServerName;" _<br /> & "PWD=MyPassword;" _<br /> & "DATABASE=master;" _<br /> & "Encrypt=Yes"<br />End Function</pre><br /><p><font size="3" face="Calibri">See my article </font><a href="http://gainingaccess.net/SQLAzure/SQLAzureSecurity1.aspx"><font size="3" face="Calibri">Building Safer SQL Azure Cloud Applications with Microsoft Access</font></a><font size="3" face="Calibri"> for more information about securing your Access application. </font><br /><p><font size="3" face="Calibri">5) Create a Function to Execute the SQL and Create the Login. </font><br /><p><font size="3" face="Calibri">Place the ExecuteMasterDBSQL Function below in a Standard Module. This Function executes our CREATE LOGIN statement. It can be used any time you want to execute a T-SQL statement in the SQL Azure master database that does not return records. The Function returns True if the SQL was executed successfully or False if the SQL fails to be executed. </font><pre>'This procedure executes Action Query SQL in the SQL Azure master database.<br />'Example usage: Call ExecuteMasterDBSQL(strSQL) or If ExecuteMasterDBSQL(strSQL) = False Then<br />'<br />Function ExecuteMasterDBSQL(strSQL As String) As Boolean<br />On Error GoTo ErrHandle<br /><br /> Dim db As DAO.Database<br /> Dim qdf As DAO.QueryDef<br /><br /> ExecuteMasterDBSQL = False 'Default Value<br /><br /> Set db = CurrentDb<br /><br /> 'Create a temporary unnamed Pass-through QueryDef. This is a<br /> 'practice recommended in the Microsoft Developer Reference.<br /> 'The order of each line of code must not be changed or the code will fail.<br /> Set qdf = db.CreateQueryDef("")<br /> 'Use a function to get the SQL Azure Connection string to the master database<br /> qdf.Connect = obfuscatedFunctionName<br /> 'Set the QueryDef's SQL as the strSQL passed in to the procedure<br /> qdf.SQL = strSQL<br /> 'ReturnsRecords must be set to False if the SQL does not return records<br /> qdf.ReturnsRecords = False<br /> 'Execute the Pass-through query<br /> qdf.Execute dbFailOnError<br /><br /> 'If no errors were raised the query was successfully executed<br /> ExecuteMasterDBSQL = True<br /><br />ExitHere:<br /> 'Cleanup for security and to release memory<br /> On Error Resume Next<br /> Set qdf = Nothing<br /> Set db = Nothing<br /> Exit Function<br /><br />ErrHandle:<br /> MsgBox "Error " & Err.Number & vbCrLf & Err.Description _<br /> & vbCrLf & "In procedure ExecuteMasterDBSQL"<br /> Resume ExitHere<br /><br />End Function</pre><br /><p><font size="3" face="Calibri">6) Use a Form to Enter the Login Name and Password </font><br /><p><font size="3" face="Calibri">We can make it easy for users to create a Login by using a form. To do this we need to add two text boxes and a command button to the form. Both text boxes need to be unbound. Name the text box for the Login Name txtLoginName. Name the text box for the Password txtPassword. Name the command button cmdCreateLogin. The form should look something like this, but without the extra touches for appearance sake. </font><br /><p><font size="3" face="Calibri"><img style="display: block; float: none; margin-left: auto; margin-right: auto" alt="Create Logins Form" src="http://gainingaccess.net/articleimages/frmCreateLogin.png"></font><br /><p><font size="3" face="Calibri">Add the code below to the command button’s Click event. After the code verifies that a Login Name and Password has been entered, it calls the ExecuteMasterDBSQL Function to create the Login in our SQL Azure master database. </font><pre>Private Sub cmdCreateLogin_Click()<br /><br /> 'Prepare a Variable to hold the SQL statement<br /> Dim strSQL As String<br /><br /> 'Build the SQL statement<br /> strSQL = "CREATE LOGIN " & Me.txtLoginName & " WITH password = '" & Me.txtPassword & "'"<br /><br /> 'Verify both a Login Name and a Password has been entered.<br /> If Len(Me.txtLoginName & vbNullString) = 0 Then<br /> 'A Login Name has not been entered.<br /> MsgBox "Please enter a value in the Login Name text box.", vbCritical<br /> Else<br /> 'We have a Login Name, verify a Password has been entered.<br /> If Len(Me.txtPassword & vbNullString) = 0 Then<br /> 'A Password has not been entered.<br /> MsgBox "Please enter a value in the Password text box.", vbCritical<br /> Else<br /> 'We have a Login Name and a Password.<br /> 'Create the Login by calling the ExecuteMasterDBSQL Function.<br /> If ExecuteMasterDBSQL(strSQL) = False Then<br /> MsgBox "The Login failed to be created.", vbCritical<br /> Else<br /> MsgBox "The Login was successfully created.", vbInformation<br /> End If<br /> End If<br /> End If<br />End Sub</pre><br /><p><font size="3" face="Calibri">The code in the Form checks the return value of the ExecuteMasterDBSQL Function and informs us whether or not the Login was successfully created. Once we have created a Login we can create a Database User for the Login and grant the User access to the data in the SQL Azure Database. Creating a Database User for the Login appears to be a good subject for another article. </font><br /><p><strong><font size="3" face="Calibri">Get the free </font><a href="http://gainingaccess.net/SQLAzure/SADownloads.aspx"><font size="3" face="Calibri">Demonstration Application</font></a></strong><font size="3" face="Calibri"> that shows how effectively Microsoft Access can use SQL Azure as a back end. </font><br /><p><font face="Calibri"><font size="3"><b><i>More Free Downloads:</i></b><br></font></font><a href="http://gainingaccess.net/GainingAccess/FreeDownloads.aspx"><font size="3" face="Calibri">Us or UK/AU Pop-up Calendar</font></a><br><a href="http://gainingaccess.net/GainingAccess/FreeDownloads.aspx"><font size="3" face="Calibri">Report Date Dialog Form</font></a><font size="3" face="Calibri"> in US or UK/AU Version. <br></font><a href="http://gainingaccess.net/GainingAccess/FreeDownloads.aspx"><font size="3" face="Calibri">Free Church Management Software</font></a><font size="3" face="Calibri"> with Contributions management. <br></font><a href="http://gainingaccess.net/GainingAccess/CodeSamples.aspx"><font size="3" face="Calibri">Code Samples</font></a><br /><p><font size="3" face="Calibri">Get the </font><a href="http://gainingaccess.net/Products/AppointmentManager.aspx"><strong><font size="3" face="Calibri">Access and Outlook Appointment Manager</font></strong></a><font size="3" face="Calibri"> to manage all of your Outlook Calendar Appointments and Access dated information.</font><br /><p><font size="3" face="Calibri">Happy computing, <br>Patrick (Pat) Wood <br>Gaining Access <br></font><a href="http://gainingaccess.net"><font size="3" face="Calibri">http://gainingaccess.net</font></a></p></blockquote><br /><p><font size="3" face="Calibri">It’s unfortunate that Access Web Databases don’t support VBA, which is necessary for Patrick’s approaches outline above.</font><br /><p><br /><hr><br /><br /><p><br /><div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:8b46b71d-6122-4ed7-841f-52d907de2671" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Access" rel="tag">Access</a>,<a href="http://technorati.com/tags/SQL+Azure" rel="tag">SQL Azure</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0tag:blogger.com,1999:blog-4760178173405088846.post-16367258104039354022011-08-01T09:10:00.000-07:002011-08-01T09:11:47.552-07:00A SharePoint 2010 Primer for Access 2010 Developers by Ben Clothier<p><font face="Calibri"><font size="3"><strong>Ben Clothier</strong> answered </font></font><a href="http://accessexperts.net/blog/2011/06/15/sharepoint-and-access-how-do-they-fit-together/"><font size="3" face="Calibri">SharePoint and Access: How do they fit together?</font></a><font size="3" face="Calibri"> in a 6/15/2011 post to the AccessExperts.net blog:</font></p> <blockquote> <p><a href="http://lh5.ggpht.com/-TkWaksm6Nig/TjbMueqRvZI/AAAAAAAANtA/yyKmZ73Kqc0/s1600-h/image%25255B3%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-eRbiXaXRWRI/TjbMu9n855I/AAAAAAAANtE/bF1rU4L5c8U/image_thumb%25255B1%25255D.png?imgmax=800" width="65" height="63"></font></a><font size="3" face="Calibri">Hello Access with SQL Server blog readers! This is my first post since joining [IT Impact] and I look forward to many more! Both Juan and I will be blogging here on Access, SQL Server and SharePoint and we love reading your comments, so please leave us feedback below.</font> <p><font size="3" face="Calibri">My specialty is Access and SharePoint, so what better way to get started than with a Series on both?</font> <p><strong><font size="3" face="Calibri">Access and SharePoint</font></strong> <p><a href="http://lh5.ggpht.com/-_GgWofMCDII/TjbMvTmJ3oI/AAAAAAAANtI/w-qHXZ4xJ5g/s1600-h/image%25255B6%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/-G1yUedGCXGo/TjbMv0wjp-I/AAAAAAAANtM/4HqkNqd9BWY/image_thumb%25255B2%25255D.png?imgmax=800" width="242" height="52"></font></a><font size="3" face="Calibri">If you’ve been working with Access for a while, it’s hard to miss the push Microsoft has been putting behind SharePoint integration with Access. The integration started as early as Access 2003, continued through 2007 and has blossomed with 2010. Without question the number one feature in the latest release is publishing your database to the web with SharePoint. In this first post I’m going to discuss what is SharePoint and why was it used to publish Access web databases, replace the Jet Replication and other integration features.</font> <p><strong><font size="3" face="Calibri">So what is SharePoint?</font></strong> <p><font size="3" face="Calibri">If you were to go over to </font><a href="http://sharepoint.microsoft.com/en-us/Pages/default.aspx"><font size="3" face="Calibri">Microsoft SharePoint page</font></a><font size="3" face="Calibri">, you may see a long list of features and success stories described in nebulous terms. Indeed, there is no one simple definition to tack SharePoint to. Instead of discussing its capabilities, I believe we’re better off discussing the intention behind its capabilities, and I’m going to use Access as an analogy.</font> <p><strong><font size="3" face="Calibri">SharePoint is to Web Apps as Access is to Visual Studio</font></strong> <p><font size="3" face="Calibri">Why do we use Access instead of Visual Studio? Isn’t Visual Studio more powerful and lets us do all those cool things in code? Or better yet, why not just hire a professional to set up everything for us? The fact is, Access is accessible (excuse the pun) to *non*-programmers; people who need to track their data as part of their job and this is the crucial difference. To use Access, you’re not required to be a professional to get started, and because you can do it yourself. You don’t have to go and talk with your IT department who may be overworked with other, more important projects. Precisely because of this ability to create something on a worker’s desktop, Access is has become the most popular database in the world. Some of you may be thinking, “but there are complex Access applications that requires specialized consultants out there!” – Well, yes, but that usually comes later in the Access databases’ life and not all Access databases out there end the same way and to me that’s the beauty of Access – it enables companies to develop a line-of-business applications at far less risk, whether monetary or time & effort than if we opted to use Visual Studio.</font> <p><font size="3" face="Calibri">SharePoint solves a different set of problems exactly the same way as Access does: it enables *non*-web-designers to build web pages quickly and without any specialized knowledge because it’s merely a part of their workflow and not their livelihood. Likewise, it enables workers to manage files without having to think about the organization of the files, backing up the files and how they would find the files again. SharePoint helps the company saves money by reducing the overall IT administration in contrast to traditional file sharing/networked hard drives, management of documents and so forth. Some people has suggested that SharePoint be thought of as a platform providing easy-to-use building blocks, and suddenly the analogy from Access is much more accurate; we get to build web applications or develop a certain document management strategy at far less risk and expenses than if we built one from scratch.</font> <p><font size="3" face="Calibri">In fact, when you examine the difference between Jet Replication and the Offline mode with SharePoint, it’s impressive in how simple it is. All you have to do is just use SharePoint as your data source and everything else about working offline and synchronizing is automatic. No manual configuration. No coding or additional installation. This truly demonstrate the raison d’être behind Access and SharePoint; solving IT problems for common people. And that’s just one of many integration points between Access and SharePoint.</font> <p><strong><font size="3" face="Calibri">SharePoint is not a relational database</font></strong> <p><font size="3" face="Calibri">Access and SharePoint are very similar when we look at how they can solve business problems. Given the similarity in the audience both programs addresses, it seems conceivable that they’d be peas in pod, right? Well, here’s the other thing. Many professional Access consultants are actutely aware that SharePoint is anything but a true relational database system and they’re right. Even though SharePoint is powered by SQL Server, it introduces additional abstractions in the form of “lists” which are not similar to a SQL table and has some ramifications on how we can use lists in Access. That’ll be addressed in a future post.</font> <p><strong><font size="3" face="Calibri">SharePoint 2010 + Access 2010 = Instant Web Database!</font></strong> <p><font size="3" face="Calibri">The latest version of Access will allow you to publish your work to SharePoint 2010 with just one URL and a click. Gone are the days were you needed to upload files, configure security, configure the web server and countless other details. Granted, not all of the functionality of Access made it into this version of SharePoint’s Access Services, but we expect improved functionality in the years to come.</font> <p><strong><font size="3" face="Calibri">Sounds great! So why isn’t SharePoint used in small businesses?</font></strong> <p><font size="3" face="Calibri">Price is another point where SharePoint and Access diverge, SharePoint’s cost are much higher, both in terms of licensing fees and man hours required to support and maintain it. Microsoft has made some headway in this area by offering </font><a href="http://www.microsoft.com/en-us/office365/online-software.aspx"><font size="3" face="Calibri">Office365</font></a><font size="3" face="Calibri"> which essentially promises to brings SharePoint functionality to small businesses. Of course, there’s also </font><a href="http://www.accesshosting.com/"><font size="3" face="Calibri">AccessHosting</font></a><font size="3" face="Calibri"> which specializes in hosting Access web databases and we provide consultation for developmental work on web database that get hosted. This inevitable reality of moving services off the desktop and into “cloud” will also be examined in a future post.</font> <p><font size="3" face="Calibri">My </font><a href="http://accessexperts.net/blog/2011/07/07/so-what-are-sharepoint-lists-actually/"><font size="3" face="Calibri">second post</font></a><font size="3" face="Calibri"> [see below] will discuss SharePoint lists in more detail.</font></p></blockquote> <p><font size="3" face="Calibri">For more details about SharePoint and Access integration, see links to my Webcasts in my </font><a href="http://accessindepth.blogspot.com/2011/03/three-microsoft-access-2010-webcasts.html"><font size="3" face="Calibri">Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011</font></a><font size="3" face="Calibri"> post of 5/3/2011.</font> <p><font size="3" face="Calibri"> <hr> <strong>Ben Clothier</strong> continued his Access and SharePoint series with a </font><a href="http://accessexperts.net/blog/2011/07/07/sharepoint-lists-and-microsoftaccess/"><font size="3" face="Calibri">SharePoint Lists and Microsoft Access in depth</font></a><font size="3" face="Calibri"> post of 7/7/2011 (missed when posted):</font></p> <blockquote> <p><a href="http://lh3.ggpht.com/-CrcvQtwFYoY/TjbMwcYXj7I/AAAAAAAANtQ/mgQT43Y0CKU/s1600-h/image%25255B13%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-Mn044NHrUhU/TjbMw4aA65I/AAAAAAAANtU/x6kXBP-PMDs/image_thumb%25255B4%25255D.png?imgmax=800" width="65" height="63"></font></a><font size="3" face="Calibri">In a </font><a href="http://accessexperts.net/blog/2011/06/15/sharepoint-and-access-how-do-they-fit-together/"><font size="3" face="Calibri">recent post</font></a><font size="3" face="Calibri">, I discussed about how SharePoint and Access address similar audiences and provide easy solutions to different problems. Now we’ll examine the main object you’ll interact with SharePoint within Access: Lists. If you’re using the new web database with Access 2010, the “web tables” are in fact SharePoint Lists by a different name. Therefore, if you know something about them, you’ll also know something about web tables. There are some key differences between the two we will discuss later in the article.</font> <p><a href="http://lh4.ggpht.com/--ys77tXnaBM/TjbMxRLIdxI/AAAAAAAANtY/7ccxUXfabqA/s1600-h/image%25255B9%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-6Xq92Qkyoic/TjbMxud279I/AAAAAAAANtc/3zzfzbB2wCo/image_thumb%25255B3%25255D.png?imgmax=800" width="242" height="52"></font></a><font size="3" face="Calibri">As linked tables go, SharePoint Lists are definitely a horse of different color. I’ve said it before and I’ll say it again: SharePoint is anything but a true relational database. The lists totally reflect this. Therefore many techniques you may have picked up in optimizing your operations with linked tables may be inapplicable and we need to consider new ones.</font> <p><strong><font size="3" face="Calibri">Is it *gasp* <em>de-normalized</em>?</font></strong> <p><font size="3" face="Calibri">In a way, lists are denormalized. If you cared to peek inside the SQL Server database that SharePoint uses to store Lists, you would realize that all list items are stored in one giant sized table with columns names int1, int2, int3 and so forth. There’s another large table that describe the definition of the list and maps its columns to those wildcard columns. In a way, you could say SharePoint Lists are based on the </font><a href="http://en.wikipedia.org/wiki/Entity-attribute-value_model"><font size="3" face="Calibri">Entity-Attribute-Value data model</font></a><font size="3" face="Calibri">. After all, there is a reason why they’re called “Lists” and not “Tables”. Historically, they were just that – a simple list of values. In fact, up to SharePoint 2010, referential integrity wasn’t supported, a sore point that could very well have been the #1 blocking reason for adoption of SharePoint as a data source among Access developers. In prior versions you could relate a list to other lists in a loose way. The lists had no formal structure, since on average, they were created by non developers who uses SharePoint as a means to get the job done rather than developing comprehensive and robust solution for others. We’ll examine the implications this has for us when we use SharePoint Lists.</font> <p><strong><font size="3" face="Calibri">SharePoint Lists operates on ISAM model</font></strong> <p><font size="3" face="Calibri">Whenever we link to a ODBC data source, Access only needs to submit a SQL statement which is then executed on the server and sent back for Access’ consumption. However, when we query a SharePoint List for the first time, Access has to fetch all records and download them to a local cache in order to process the query which is then executed locally. In 2007, the local cache was stored in a XML format but with 2010, Access will use actual local Access tables (hidden from the UI) to work as a cache.</font> <p><strong><font size="3" face="Calibri">What SharePoint lists offers that linked tables don’t</font></strong> <p><font size="3" face="Calibri">This enables at least four things that we do not normally enjoy with ODBC linked tables:</font> <p><em><font size="3" face="Calibri">1) Offline data access </font></em> <p><font size="3" face="Calibri">A persistent connection to the backend is not required, the tables have more tolerance to disconnections & network interruptions than with a regular ODBC linked table.</font> <p><em><font size="3" face="Calibri">2) Built-in replication</font></em> <p><font size="3" face="Calibri">Even better, when users have a connection restored, they can sync their changes without any special configuration as was the case with old Jet Replication.</font> <p><em><font size="3" face="Calibri">3) Design changes</font></em> <p><font size="3" face="Calibri">With most linked tables, you probably had to shuttle between Access and the server’s management software (e.g. SQL Server Management Studio for SQL Server tables/views) if you wanted to make design changes. Even a simple thing such as adding a new field usually meant you had to go over there and issue the ALTER TABLE command, come back to Access and refresh the links to get that new field. With SharePoint Lists, whether as a web table or linked table, you get to make design changes without leaving Access.</font> <p><em><font size="3" face="Calibri">4) Automatic Auditing & Versioning</font></em> <p><font size="3" face="Calibri">SharePoint is designed from grounds up to provide auditing and data recovery. Whenever you delete a list item from the list, it’s not actually deleted but rather moved into a recycle bin which gives the user an opportunity to undo the accidental deletion. Furthermore, there’s second-tier Recycle Bin accessible to the administrators so data recovery is always possible. SharePoint also supports versioning of data out of the box and has tools to track all changes made to the data saved within the list. All of this great functionality is free; no configuration is required for recycle bin and you simply need to flick the switch for versioning.</font> <p><strong><font size="3" face="Calibri">Server-side filtering</font></strong> <p><font size="3" face="Calibri">There are no server-side operations other than fetching all the records and keeping the cache synchronized, which means your queries are always executed locally using Access database engine, unlike ODBC linked tables. If you want to have true server-side filtering consider using SharePoint Views which are analogous but not identical to SQL View. Unfortunately, there is no simple UI process to link to a SharePoint view; it has to be done in code:</font><pre><font size="3" face="Calibri">DoCmd.TransferSharePointList _<br /> acLinkSharePointList, _<br /> "http:\\server\site", _<br /> "MyList", _<br /> "{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}", _<br /> "tblMyList", _<br /> True</font></pre><br /><p><font size="3" face="Calibri">You may be wondering how we came up with GUID “{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}”. SharePoint assigns a GUID to each object, you can’t use the View’s name as you could with a List’s name to select the object. To find out the GUID for your View, you’d have to open your SharePoint site in your web browser, navigate to the List and on the ribbon select Modify View. The GUID will then appear in the URL. Depending on which web browser you are using, it may be encoded so you’ll have to replace “%2D” with “-”, “%7B” with “{” and %7D” with “}”. Two things to note –</font><br /><p><font size="3" face="Calibri">1) You can link to the same list multiple times, using different views so you are not necessarily restricted to choosing one view out of all views available for a given list.</font><br /><p><font size="3" face="Calibri">2) Views only work as a linked SharePoint list – views can be created with web tables but are ignored in the web database, unless you create a separate linked table object which would not be accessible in the web browser, only in Access (this is what they call “hybrid application”, which we’ll talk about later).</font><br /><p><font size="3" face="Calibri">You can use the View to provide additional filtering. This can be helpful particularly in cases where you may have a large amount of items but only need a handful of active, current, pending or open records. However, there’s one major difference between a SharePoint View and a SQL View. As you know, a SQL View has its own set of permissions and you can use Views to manage security. That’s not the case with SharePoint Views. You can prevent people from editing the view’s definition but you can’t prevent people from linking to the underlying SharePoint List directly.</font><br /><p><strong><font size="3" face="Calibri">How many columns can I really have?</font></strong><br /><p><font size="3" face="Calibri">As those familiar with SQL Server know, we can have a large table with many fields which while quite rare for normalized relational design may be desirable for a OLAP data model. SharePoint also allows a large number of fields. I already mentioned earlier that the List basically has a number of columns of certain data type. How does it handle the scenario when we need more than a given number of a certain data types? The answer is that SharePoint LIst uses </font><a href="http://technet.microsoft.com/en-us/library/cc262787.aspx#Column"><font size="3" face="Calibri">Row-Wrapping</font></a><font size="3" face="Calibri">; a single SharePoint List Item may be actually stored on more than one SQL Server row. By default, SharePoint allows you to row-wrap up to six SQL rows. Let’s take the example of Date and Time data types. The article linked above says we can have 48 Date and Time data type in a single List. If we divide 48 with 6, that actually means we only have 8 Date and Time data types that will fit a single SQL row. So, if we create a SharePoint List that had 20 columns and it happened that there was 9 Date and Time, then we’d have one SQL row populated with data from 19 fields and second SQL row populated with only one excess Date and Time data type. If your list then has 100 list items, we’d need to read 200 SQL rows to gather data from 20 columns including the 9th Date and Time data type. If performance is a important consideration in your design and you expect to store large amount of data, you should be mindful of how many columns and what data types you plan to have so you can minimize row-wrapping.</font><br /><p><strong><font size="3" face="Calibri">Conclusions</font></strong><br /><p><font size="3" face="Calibri">We’ve only scratched the surface when it comes to SharePoint but I’m confident you’ll find the information much more relevant to Access development and we’ll explore more about web database in my next post.</font></p></blockquote><br /><p><font size="3" face="Calibri">Ben is a Senior Access Developer at IT Impact, Inc.</font><br /><p><font size="3" face="Calibri">For more details about SharePoint and Access integration, see links to my Webcasts in my </font><a href="http://accessindepth.blogspot.com/2011/03/three-microsoft-access-2010-webcasts.html"><font size="3" face="Calibri">Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011</font></a><font size="3" face="Calibri"> post of 5/3/2011.</font><br /><hr><br /><font size="3" face="Calibri">Thanks to Steven Thomas for the heads-up in his <a href="http://blogs.office.com/b/microsoft-access/archive/2011/07/31/access-and-sharepoint-a-look-at-integration-points-from-2003-to-2010.aspx">Access and SharePoint: a look at integration points from 2003 to 2010</a> post of 7/31/2011.</font><br /><hr> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:fd6d19ff-94c5-4c87-b3e4-f6b48737b540" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Microsoft+SharePoint+2010" rel="tag">Microsoft SharePoint 2010</a>,<a href="http://technorati.com/tags/SharePoint+2010" rel="tag">SharePoint 2010</a>,<a href="http://technorati.com/tags/SharePoint+Lists" rel="tag">SharePoint Lists</a>,<a href="http://technorati.com/tags/Office+365" rel="tag">Office 365</a>,<a href="http://technorati.com/tags/Access+Services" rel="tag">Access Services</a>,<a href="http://technorati.com/tags/Access+Web+Databases" rel="tag">Access Web Databases</a>,<a href="http://technorati.com/tags/Access+Hosting" rel="tag">Access Hosting</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com2tag:blogger.com,1999:blog-4760178173405088846.post-39806417837726250842011-07-17T11:38:00.000-07:002011-07-17T11:38:50.241-07:00Migrate Access 2000 or Later Databases to Public or Private Rollbase Clouds<p><a href="http://lh5.ggpht.com/-5eGzXOnvAFo/TiMiCW04jmI/AAAAAAAAM_c/UOsbdPiZR3g/s1600-h/image%25255B5%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-BreBsMHIl6U/TiMiCuj1NMI/AAAAAAAAM_g/FxPsDJmMpfI/image_thumb%25255B1%25255D.png?imgmax=800" width="186" height="48"></font></a><font size="3" face="Calibri">Now that Office 365 has been released to the Web (RTW) for commercial use, there is considerable interest in taking advantage of SharePoint Online’s Access Services to create Web-based data management applications (Access Web Databases) at a monthly cost of US$6 per user. An advantage of this approach is that Access Services supports migration of tables, queries, forms and macros to SharePoint lists, Web pages and workflows. Alternatively, you can move just the Access tables to SharePoint Online and link them to on-premises Access front-ends. Optional local data caching improves data access performance and enables offline data entry. You can learn more about migrating Access 2010 applications to SharePoint Online in my <a href="http://accessindepth.blogspot.com/2011/05/learn-how-to-create-access-web.html">May 2011 Webcast</a>.</font></p> <p><a href="http://lh6.ggpht.com/-SdhSDwR3Fqw/TiMiDNOSHEI/AAAAAAAAM_k/OGK7dQK6lLg/s1600-h/image%25255B10%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-8eIy80fytgI/TiMiDrY4H8I/AAAAAAAAM_o/3DjjTLGBDJg/image_thumb%25255B4%25255D.png?imgmax=800" width="200" height="41"></font></a><font face="Calibri"><font size="3"><strong>Note:</strong> The current version of Office 365’s Access Services doesn’t support reports. If you need printed reports, </font></font><a href="http://accesshosting.com/pricing.asp#sharept"><font size="3" face="Calibri">Access Hosting</font></a><font size="3" face="Calibri"> offers hosted SharePoint 2010 for up to 10 users at a flat rate of US$99 per month. Access Hosting offers many advantages over SharePoint online, as described </font><a href="http://accesshosting.com/sharepoint-2010-hosting.asp"><font size="3" face="Calibri">here</font></a><font size="3" face="Calibri"> (scroll down.) My <a href="http://accessindepth.blogspot.com/2011/03/upsizing-northwind-web-database-to.html">Upsizing the Northwind Web Database to an Updated SharePoint 2010 Server Hosted by AccessHosting.com</a> post contains links to my March 2011 Webcast about the topic.</font></p> <p><a href="http://lh6.ggpht.com/-tzdjwWHXvx8/TiMiD6QrWZI/AAAAAAAAM_s/bZGkV3OqUTw/s1600-h/image%25255B2%25255D.png"><font size="3" face="Calibri"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh3.ggpht.com/-0I71RJnvg1U/TiMiEXBCvMI/AAAAAAAAM_w/Htt2ZQ66AxY/image_thumb.png?imgmax=800" width="146" height="52"></font></a><font size="3" face="Calibri">Rollbase is a </font><a href="http://www.rollbase.com/hostedcloud.shtml"><font size="3" face="Calibri">cloud application platform</font></a><font size="3" face="Calibri">, which includes a wizard for importing Access 2000 or later *.mdb and Excel *.xls and *.csv files into Rollbase Objects and Fields to create Rollbase Applications for the Web. Rollbase’s primary claims to fame are its reported capability to </font><a href="http://www.readwriteweb.com/cloud/2011/07/one-comment-i-hear-regularly.php"><font size="3" face="Calibri">import Salesforce and Force.com applications</font></a><font size="3" face="Calibri"> and availability in public and private cloud versions. There’s no indication on the Rollbase site of support for importing Access objects other than tables. Hosted applications are US$15 per month per user.</font></p> <p> <hr> </p> <p><font face="Calibri"><font size="3"><strong>P.S.</strong>: Bruce Kyle reported </font></font><a href="http://blogs.msdn.com/b/usisvde/archive/2011/07/17/200-sessions-announced-for-sharepoint-conference-2011.aspx"><font size="3" face="Calibri">200 Sessions Announced for SharePoint Conference 2011</font></a><font size="3" face="Calibri"> in a 7/17/2011 post to MSDN’s US ISV Evangelism blog:</font></p> <blockquote> <p><a href="http://lh5.ggpht.com/-0J3SF7MTrYw/TiMsM7qS-nI/AAAAAAAAM_0/yQFhstBkYAc/s1600-h/image%25255B14%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-ov8xDS09CsU/TiMsNWA9rbI/AAAAAAAAM_4/2dtdEMfb2ro/image_thumb%25255B6%25255D.png?imgmax=800" width="65" height="80"></a><font size="3" face="Calibri">SharePoint Conference 2011 is your only opportunity this year to see over </font><a href="http://www.mssharepointconference.com/Pages/sessions.aspx"><font size="3" face="Calibri">200 sessions</font></a><font size="3" face="Calibri"> focused on SharePoint 2010 and related technologies both in the cloud and on-premises. All current session content including abstracts and speakers have been posted to </font><a href="http://www.mssharepointconference.com/"><font size="3" face="Calibri">www.mssharepointconference.com</font></a><font size="3" face="Calibri">!</font> <p><font size="3" face="Calibri">The conference will be held in Anaheim, CA on October 3 – 6.</font> <p><a href="http://www.mssharepointconference.com/"><font size="3" face="Calibri"><img title="image" border="0" alt="image" align="right" src="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-68-67-metablogapi/7851.image_5F00_18F1B607.png" width="244" height="96"></font></a><font size="3" face="Calibri">This year’s conference will have a breadth of both technical and non-technical sessions and will have suitable topics for everyone regardless if you are new to SharePoint 2010 or working on continuing your SharePoint education.</font> <p><font size="3" face="Calibri">SPC11 will provide you with the training, insight, and networking you need to develop, deploy, govern and get the most from SharePoint. You’ll also hear from Microsoft Engineers, Product Managers, MCMs and MVPs who will discuss topics such as cloud services, best practices and real world project insights. </font> <p><font size="3" face="Calibri"><a href="http://lh5.ggpht.com/-xQCIipj5pmM/TiMsNzQmQGI/AAAAAAAAM_8/FN9NMSOch_g/s1600-h/image%25255B17%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh6.ggpht.com/-Zb-Bgo8NCPI/TiMsOGaCqJI/AAAAAAAANAA/vN8JlajSguE/image_thumb%25255B7%25255D.png?imgmax=800" width="243" height="53"></a>Don’t miss your chance to attend and learn from these sessions by </font><a href="http://www.mssharepointconference.com/"><font size="3" face="Calibri">registering now</font></a><font size="3" face="Calibri">! Conference registration is only $1,199 and seats are selling fast with only 2.5 months until the event!</font> <h5><font size="3" face="Calibri">Pre & Post Conference Training Opportunities</font></h5> <p><font size="3" face="Calibri">SharePoint Conference announces five ancillary conference training opportunities with limited space! Act fast before space sells out! </font> <p><font size="3" face="Calibri">To view more information on each session below click on the training title to learn more about the session including abstracts, agendas, speakers, costs and maximum attendance. </font><a href="http://www.mssharepointconference.com/"><font size="3" face="Calibri">Register now</font></a><font size="3" face="Calibri"> to reserve your seat!</font> <h5><font size="3" face="Calibri">Sunday, October 2</font></h5> <ul> <li><a href="http://www.mssharepointconference.com/Pages/pre-post-conference-training.aspx#3"><font size="3" face="Calibri">Enable Better Decision Making with Microsoft Business Intelligence</font></a><i></i></li> <li><a href="http://www.mssharepointconference.com/Pages/pre-post-conference-training.aspx#1"><font size="3" face="Calibri">Learning to develop a high-end search solution with FAST Search for SharePoint 2010</font></a></li> <li><a href="http://www.mssharepointconference.com/Pages/pre-post-conference-training.aspx#2"><font size="3" face="Calibri">Project 2010 Technical drill-down for SharePoint Partners</font></a></li></ul> <h5><font size="3" face="Calibri">Thursday, October 6 (2pm-6pm)</font></h5> <ul> <li><a href="http://www.mssharepointconference.com/Pages/pre-post-conference-training.aspx#4"><font size="3" face="Calibri">SharePoint 2010 and Windows Azure Bootcamp: Developing SharePoint 2010 Applications that Integrate with the Cloud</font></a><i></i></li></ul> <h5><font size="3" face="Calibri">Friday, October 7 </font></h5> <ul> <li><a href="http://www.mssharepointconference.com/Pages/pre-post-conference-training.aspx#5"><font size="3" face="Calibri">SharePoint 2010 Administrators Deep Dive</font></a></li></ul></blockquote> <hr> <p><font size="3" face="Calibri"></font></p> <p><font size="3" face="Calibri"></font></p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:9034e06b-7def-4be6-8d86-0cad524f453a" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010" rel="tag">Access 2010</a>,<a href="http://technorati.com/tags/Access" rel="tag">Access</a>,<a href="http://technorati.com/tags/Access+Services" rel="tag">Access Services</a>,<a href="http://technorati.com/tags/Office+365" rel="tag">Office 365</a>,<a href="http://technorati.com/tags/SharePoint+Online" rel="tag">SharePoint Online</a>,<a href="http://technorati.com/tags/SharePoint+2010" rel="tag">SharePoint 2010</a>,<a href="http://technorati.com/tags/Access+Hosting" rel="tag">Access Hosting</a>,<a href="http://technorati.com/tags/Rollbase" rel="tag">Rollbase</a>,<a href="http://technorati.com/tags/Salesforce.com" rel="tag">Salesforce.com</a>,<a href="http://technorati.com/tags/Force.com" rel="tag">Force.com</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com1tag:blogger.com,1999:blog-4760178173405088846.post-19872079366718192562011-07-09T07:45:00.001-07:002011-07-09T07:45:55.277-07:00Problem Reported with Access Wizards and 64-bit Access 2010 SP1<p><font size="3"><font face="Calibri"><strong>Stephen Thomas</strong> suggested </font></font><a href="http://blogs.office.com/b/microsoft-access/archive/2011/07/08/sp1-vbe7-update-impacts-64_2d00_bit-installations-of-access.aspx"><font size="3" face="Calibri">Using 64-bit Access 2010? You may want to wait on [Installing] SP1</font></a><font size="3" face="Calibri"> in a 7/8/2010 post to the Access blog:</font></p> <blockquote> <p><font size="3" face="Calibri">A </font><a href="http://social.technet.microsoft.com/Forums/en-US/officeitpro/thread/5b864685-ab00-4e07-b236-e05d628f2335"><font size="3" face="Calibri">customer's post on TechNet</font></a><font size="3" face="Calibri"> brought one of my colleague's attention to an error folks are seeing after </font><a href="http://technet.microsoft.com/en-us/library/cc179069.aspx"><font size="3" face="Calibri">applying SP1</font></a><font size="3" face="Calibri"> to 64-bit Access installations and then trying to use a wizard: </font> <p><strong><font size="3" face="Calibri">The database cannot be opened because the VBA project contained in it cannot be read. ... To open the database and delete the VBA project without creating a backup copy, click OK.</font></strong> <p><font size="3" face="Calibri">On clicking OK, Access doesn't open the database, offering this error message:</font> <p><strong><font size="3" face="Calibri">The code contains a syntax error, or a <DB_NAME> function you need is not available. If the syntax is correct, check the Control Wizards subkey or the Libraries key in the <DB_NAME> section of the windows registry to verify that the entries you need are listed and available.</font></strong> <p><font size="3" face="Calibri">Apparently, </font><a href="http://support.microsoft.com/kb/2533794"><font size="3" face="Calibri">the VBE7.DLL file update included in the service pack prevents the opening of .ACCDE files compiled using RTM 64-bit Access</font></a><font size="3" face="Calibri">. Because wizards are .ACCDE files, they could trigger the error depending on when they were compiled. </font> <p><font size="3" face="Calibri">The customer who posted reports that uninstalling the service pack restores the functionality, and advises that people with 64-bit Access wait until a solution is provided before applying SP1. TechNet agrees (the mod marked it as an Answer), and so do I. </font> <p><font size="3" face="Calibri">Stay tuned for that solution…</font></p></blockquote> <p><font size="3" face="Calibri">Forewarned is forearmed.</font> <p> <hr> <p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:ef141016-8d75-47ee-ad44-af0f495b99b7" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/Microsoft+Access+2010" rel="tag">Microsoft Access 2010</a>,<a href="http://technorati.com/tags/Access+2010+SP1" rel="tag">Access 2010 SP1</a>,<a href="http://technorati.com/tags/Access+2010+64-bit" rel="tag">Access 2010 64-bit</a>,<a href="http://technorati.com/tags/Access+Wizards" rel="tag">Access Wizards</a>,<a href="http://technorati.com/tags/Access+2010+Problems" rel="tag">Access 2010 Problems</a></div> Roger Jennings (--rj)http://www.blogger.com/profile/01700526164060680385noreply@blogger.com0