docmd.TransferDatabase acLink, "ODBC","ODBC;DRIVER=SQL Server;SERVER=myServer\myInstanceIfNeeded;DATABASE=myDatabaseName;Trusted_connection=yes;", acTable,"myTableName","myTableName",False,True

The final parameter ensures that login credentials are stored.

Of course our production applications like HBSMR and PACS manage database connections in a more sophisticated manner, with the ability to switch databases and define which tables/views are attached in one operation, but the above technique can be useful where MS Access is being used for quick querying/editing/reporting operations.

Of course MS Access has featured combo boxes (drop-down lists) with “auto expand” capabilities since time began; here’s an example where I’ve typed “pen” into the control and it has automatically selected the first matching value:

(The list can be opened by clicking on the down arrow, or by hitting F4 - one of the many keyboard shortcuts that nobody knows - or in code with the dropdown method).

This is fine for most situations, but there are cases where the auto-expanding combo box falls down:

• Very long lists (like many hundreds or thousands of rows in the drop-down): firstly this can be a performance problem, putting significant load on network and database whenever a user uses the control, secondly very long lists are not user-friendly, and worst of all the auto expand can just stop working or give strange results; in a system I was working on with SQL Server data storage, one combo with >4000 rows would frequently fail to find values in the list when the user started typing.
• Lists where the user may not know the starting text (e.g. trying to find Southampton in a list that includes it as City of Southampton).
• Situations where we want to give the user an easy way of selecting multiple items. Not the main focus of this post, but it does provide much of a solution if you need it.

This post describes a way of providing a nice user experience in these situations.

In the first simple example implementation the user is looking for the record for one of our clients based on knowing the unusual first name of a member of staff there. In this case the dropdown opens when the user has typed “bri”, showing lots of rows of people called “Bridges” etc, then when the user adds a “t” the list looks like this:

On selecting a row, the form updates to show the full record for the selected client.

An implementation like this starts with an unbound combo box, making sure its Auto Expand property is set to No. You will ultimately need to clear the Row Source, but it makes design easier to start with a row source for the dropdown that closely resembles the final version. Typically this might be a table with a primary key value that will be hidden (e.g. an ID), and a visible descriptive field (e.g. a Name). Other properties can be set to suit requirements, so perhaps (following this simple example) Column Count = 2, Column Widths “0cm;10 cm” and List Width “10cm”. Keep Limit To List set to “Yes”. When you have a combo box that is behaving nicely with a Row Source, switch back to design view and remove the Row Source. Leave the Row Source Type set to “Table/Query”.

Now we need to add some code to make it work. First we add a handler for the On Key Down event, like this:

Private Sub Combo0_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = 8 Or KeyCode = 17 Or KeyCode = 32 Or KeyCode = 46 Or KeyCode > 47 Then
    Me.TimerInterval = 300
  End If
End Sub

This will make the form do something 300 milliseconds later if the pressed key is one we like, i.e. one that changes the contents of the control in a way that should affect the search. 8 is Backspace, 32 is Spacebar, 46 is Del so we need them. 17 is CTRL and can arrive if the user pastes with CTRL+V or cuts with CTRL+X. Most keycodes over 47 are characters.

See the bottom of this post for an extension to make this handle mouse and ribbon-based paste operations.

Why 300 milliseconds? Make it whatever you like, but I found that is a reasonable amount of time to wait before executing the search if a user has clicked into the control and started typing. Any longer and the user feels like they are having to hang around, any faster and it searches before they’ve had time to enter the characters they are confident about. Note that if the user presses more than one key, for example by typing “Rob”, each will set the form’s timer interval, which re-starts the timer and so defers the resulting action until 300 milliseconds after the last key press. So the 300 really defines the length of pause after hitting any one key.

At this point the observant will be wondering why we are using the Key Down event and not the Key Up event. Well I started off by using Key Up event, but I found that it simply does not fire if the user selects some of the entered text and removes it with the Del key; however using the Key Down event does need extra care, because when it fires the contents of the control have not yet changed to reflect the key press.

Now we need some code on the form’s Timer event.

Private Sub Form_Timer()
    TimerInterval = 0
    SearchByName Combo0.Text
End Sub

In this case we first switch off the timer, then pass the text that is now in the control into a method that will execute the search. Because we are harvesting the search value now, and not in the Key Down event, we get the value after all key presses have completed. Note that we do have to use the .Text property, and not the .Value property (which is the default if we just pass me!Combo0 as a string).

Now we need to process the search. In this simple example we have a private method in the form’s code-behind:

Private Sub SearchByName(SearchString As String)
Dim qryDef As QueryDef
  If Len(SearchString) >= 2 Then
    Set qryDef = CurrentDb.QueryDefs("qryNameSearch")
    qryDef.Parameters("NameSearch") = SearchString
    Set Combo0.Recordset = qryDef.OpenRecordset
    Set qryDef = Nothing
    If Combo0.Recordset.RecordCount > 0 Then Combo0.Dropdown
  Else
    Set Combo0.Recordset = Nothing
  End If
End Sub

This relies on a saved parameter query, which in the example above might be something like:

PARAMETERS NameSearch Text ( 255 );
SELECT Staff.ID, Staff.LastName
FROM Staff
WHERE Staff.LastName Like [NameSearch] & '*';

or if you want to find matches when the user enters characters from the middle of the name as well:

WHERE Staff.LastName Like '*' & [NameSearch] & '*';

And of course the query can be adapted to return more advanced expressions, as in the screenshot above, and executing the search however best suits the data. And the SQL could be inline if you don’t want a saved query. Or it could be a SQL Server stored procedure, or even a called to a web service that is converted to a recordset on return, etc etc.

If we are in a data entry form where the value always exists in the list, that’s it, job done. If the value may not exist (see the example below) we need to give the user a way of entering data. If the control is being used to navigate to a record, then we need to do something when the user selects a value using a handler on the control’s After Update event, such as:

Private Sub Combo0_AfterUpdate()

    If Combo0 > "" Then
      … do something …
    End If

End Sub

Moving on from this simple example, it’s also possible to show the list of values in a separate control that is made visible when a search finds rows. In the example below (from home!) I’ve used a list box, setting it’s record source in similar fashion to the above combo box. I prefer the look and feel of this approach, and it would also allow the user to select more than one row which could be useful in some situations. The example below is a form used for taking entries to fell races. A large proportion of runners have entered before, so it saves a lot of time to fetch and confirm their previous details rather than starting from scratch. On the other hand, lots of people share the same names, particularly in South Wales(!), so we need to see quite a lot of information to confirm a match.
When the user enters two or more characters of the surname, a list appears. On selecting a row, the remaining details are automatically filled in (in the parts of the form hidden by the list in this image). If a match is not found, the user has a simple data entry form to enter a new competitor.

In this case our search method looks like this, to show or hide the list:

If Len(RunnerSearch.Text) >= 2 Then
  Set qryDef = CurrentDb.QueryDefs("RegistrationFormNameSearchQry")
  qryDef.Parameters("NameSearch") = RunnerSearch.Text
  Set lstRunnerPicker.Recordset = qryDef.OpenRecordset
  Set qryDef = Nothing
  lstRunnerPicker.Height = 567 * 4.9 ' 567 twips per cm
  lstRunnerPicker_Label.Height 567 * 4.9
  lstRunnerPicker.Visible = True
Else
  lstRunnerPicker.Visible = False
End If

I hope someone finds this helpful. I’m sure these approaches can be refined and improved in all kinds of ways, so feedback welcome.

UPDATE @ 4th December 2016

When checking out comments from Rick Wiker below, I noticed my code above doesn't detect right-click > Paste, or Paste using the ribbon tool. I find this is easily solved using the On Change event, eg:

Private Sub RunnerSearch_Change()
  If Me.TimerInterval = 0 Then
    Me.TimerInterval = 300
  End If
End Sub

The condition is probably not needed - I just used it to be specific about when this was happening (i.e. when the KeyDown event has not already set the timer). This seems to work for a text box search control for both mouse and ribbon-based pasting. I've not yet tested this approach with a combo, and haven't spotted any negative side effects. Feel free to comment!

A Medieval copper alloy fragment of an ornate horse harness pendant (12th – 14th century). The pendant has an integral loop for suspension set at 90º to the pendant.

was being imported as

A Medieval copper alloy fragment of an ornate horse harness pendant (12th – 14th century).  The pendant has an integral loop for suspension set at 90º to the pendant. 

The user first selects the records they are interested in from the PAS database, and save this data to a local CSV file. The utility in HBSMR prompts for the location of the file and then imports the data into a temporary holding table using the Microsoft.Jet.OLEDB.4.0 provider. By default this assumes the incoming data is ANSI, whereas in fact the downloaded CSV files are encoded as “UTF-8 without BOM” (Unicode).

Our code is along these lines:

  schemaINI = CreateSchemaIni(PASFileName)
  Set db = CurrentDb()
  strConn = "[Text;FMT=Delimited;HDR=Yes;DATABASE=" & Left$(PASFileName, InStrRev(PASFileName, "\") - 1) & ";].[" & Right$(PASFileName, Len(PASFileName) - InStrRev(PASFileName, "\")) & "]"
  db.Execute "SELECT * INTO " & cPASDataTabName & " FROM " & strConn, dbFailOnError

The solution lay in the CreateSchemaIni function. This creates a file called schema.ini in the same folder as the incoming file, which is used by the text file driver to help read the file correctly (as described here). Note however that while that documentation page says the character set can be set to either ANSI or OEM, in fact it can be set to other code page identifiers as well (I don’t know how many are supported however).

I needed to add a line to this function to specify the character set, as shown in bold here, where 65001 is the appropriate code for “UTF-8 without BOM”:

  Open strPath & "Schema.ini" For Output As #1
  Print #1, "[" & strFile & "]"
  Print #1, "ColNameHeader=True"
  Print #1, "Format=CSVDelimited"
  Print #1, "CharacterSet=65001"

etc etc

Now the data imports correctly. Note: although we’re working in MS Access here, the same will apply if using the Microsoft.Jet.OLEDB.4.0 provider to read text files into .Net.

Microsoft has now published guidance on how to resolve this issue. Please see:

http://support.microsoft.com/kb/2748410

For users working in HBSMR, we are providing tools to add a new file to SharePoint, update the metadata for an existing file, and delete a file.

However, we ran into trouble with adding new files, and thought it worth writing up the solution because it is clear that others have encountered the same problem without any solutions being posted on the web.

We are adding the file by moving it into the WebDAV folder for the SharePoint list (e.g. \\sharePointserver\archaeology\reports). This automatically creates a record in the List, with several fields populated. We then want to update the new record with custom metadata, things like who wrote the report, copyright statement, etc. So how do we get hold of the new record that has been automatically created? The unique piece of information we have is the file name, so we do a lookup into the table to fetch the ID for the record that describes our file. We’ve not yet fully bottomed out the best way of doing this, given potential complications with illegal characters in filenames etc., but for the moment we are looking for the list folder and filename in one of the automatically-populated metadata fields in the SharePoint table.

We found that we could not “see” the new record initially. After some experimenting we found that to see the new record required either a) requerying a form that was bound to the table, or b) open and closing the table (programmatically).

But that’s where the trouble really started. We could find the new record, and move to it in an editable recordset (DAO), however whenever we tried to update it we got a 3314 error: You must enter a value in the ‘Name’ field. The “Name” field is a non-negotiable field in a document list, and it reveals itself as a hyperlink to the file (when viewed on the SharePoint web interface or in the linked MS Access table. We tried inserting every variation of hyperlink and filename we could think of, but no change. Meanwhile we were sure all along this should be a field that SharePoint itself populated; yet although we could see all the other auto metadata fields, this one was always NULL after the refresh.

Paul M spotted the solution – we observed that when we watched the same list in the browser interface, and refreshed after adding the new file, the list refreshed the Name column with a valid hyperlink. Yet we could not see this in Access. So we tried a more violent refresh – instead of simply re-opening the table, we tried deleting it and re-linking it – yes! the hyperlink appeared for the new record.

After some more experimenting we found that it is sufficient to refresh the link programmatically, along these lines:

dim td as tabledef, db as database

set db = CurrentDb

set td = db.TableDefs(“MySharePointTableName”)

td.RefreshLink

etc (clean up, the find new record)

After that we can see not only the new record, but the hyperlink Name field is populated. We can then edit all the other fields, and we do not need to touch the Name field at all.

I have tested this in Access 2010 and the problem remains.

One solution is  to force Access to think of the output as a “memo” field by using a UNION onto a table with the problem column as a “MEMO” field in the table design (generally using an alias to achieve this).  Here is an example to prevent truncation of a bibliographic reference in HBSMR, where we are pretending that the Mon.Descr field (MEMO) is the same as the calculated Reference from the second part (which is doing the actual work). Note that the first part says WHERE 1 = 0 so that it adds zero records to the output.

SELECT MonUID, Prefref as SourceUID, prefref as [Number], Descr as Reference, easting as SourceNumSort 

FROM Mon WHERE 1 = 0

UNION ALL

SELECT 

MonSourceLink.MonUID, SourceTbl.SourceUID, IIf(IsNull([SourceNo]),"---",

IIf([SourceNo]<>GetNumeric([SourceNo]),[SourceNo],"<" & [sourceNo] & ">")) AS [Number],

Nz([SourceTypeLUT]![Desc],"(No record type)") & 

": " & (IIf(IsNull([originator]),"",[originator] & ". ") & 

IIf(IsNull([origdate]),"",[origdate] & ". ") & 

IIf(([SourceTypeLUT].[SourceType]="PAR"),"'" & [Title] & "' " & "in " & [Var1] & 

", pp " & [Var2],(IIf(IsNull([title]),"",[title] & ". ") & 

IIf(IsNull([var1]),"",[var1] & ". ") & IIf(IsNull([var2]),"",[var2] & ". ") & 

IIf(IsNull([var3]),"",[var3] & ". ")) & [Ref] & ". ")) AS Reference,

GetNumeric([SourceNo]) AS SourceNumSort 

FROM (MonSourceLink LEFT JOIN SourceTbl ON MonSourceLink.SourceUID = SourceTbl.SourceUID) 

LEFT JOIN SourceTypeLUT ON SourceTbl.SourceType = SourceTypeLUT.SourceType 

WHERE MonSourceLink.MonUID="MWI20580";

However, this technique always produces a read-only recordset, so is only useful in some curcumstances.

More information on these pages:

• http://www.everythingaccess.com/tutorials.asp?ID=Concatenated-fields-yield-garbage-in-recordset
• http://allenbrowne.com/ser-63.html

1. setting up and accessing SQL Azure databases
2. using SQL Azure with ArcGIS + ArcSquirrel and MapLink
3. using SQL Azure with Quantum GIS and MapServer.

Azure Management Portal

Although I’ve never set up any cloud services before (other than Arvixe web hosting) it was incredibly easy to setup a trial SQL Azure subscription (“Windows Azure Platform Introductory Special”), create a database, and add some firewall rules to control access. I didn’t dig deep, but the “Management Portal” seems nice and simple:

It helpfully tells you your connection string for ADO.Net, ODBC and PHP (passwords not included!):

See this page for details of the Windows Azure Platform Introductory Special including what you get for no charge and the pricing for the rest.

MapLink, ArcSquirrel and Azure

Hartmut connected up our MapLink to the database and pushed some spatial data into my Azure database from ArcGIS – 20,000 polygons took about 75 minutes.  Hartmut had the layers drawing in ArcMAP, performing as well as in a normal SQL Server instance apparently, with only minor mods to our code to make the initial connection. However editing will not work without some more tweaks - MapLink and ArcSquirrel rely on some SQL metadata that is a little different on Azure.

SQL Server Management Studio and MS Access

Connecting with SSMS is simple, however you get reduced functionality – for example you cannot right-click > edit top 200 records or anything, so quick edits of a small table need to be done with SQL or in MS Access or some other client. Fortunately connecting with MS Access (ODBC + SQL Native Client 10) is also simple, and  the Azure database behaves just like a local SQL instance – remarkable querying speed (all things considered), though slower inserts (10,000 records took something like 15 minutes). NB you cannot directly link a spatial table into Access because it doesn’t recognize the geometry data type; but to update attributes you can link a view.

Also, in SSMS you get no designer tools, e.g. for creating a view; everything has to be done with T-SQL.

SQL Azure Database Manager

After writing the above I found the database manager in the Azure control panel, which has functionality similar to SSMS, including the ability to edit the tables*:

* spatial data types not supported for view or edit.

You get a designer for creating/modifying tables, but views and stored procedures are T-SQL only.

MapServer, OGR and Azure

Next I wanted to try serving the data through MapServer, and viewing/editing the data in Quantum GIS or MapInfo.  MapServer and QGIS require the “geometry_columns” and “spatial_ref_sys” tables, and the easiest way to create these is to push some data in with ogr2ogr.

So I tried this from one of our London servers. I loaded a few hundred linear features, but got four errors like “INSERT command for new feature failed… incorrect syntax…”. On checking the results, a) while the geometries had loaded fine, all the attribute fields were NULL, and b) the spatial_ref_sys table was empty.  I had four attribute fields, so my guess is that each syntax error was one field update across all records using a join on the incoming data.

Next I hooked up a MapServer WMS to my SQL Azure tables. MapServer had no trouble connecting to my Azure tables, but initially wouldn’t draw anything…  on investigating I found that ogr2ogr had not correctly set the SRID of the geometries, so I had to update them “manually” with:

update esdm_waters set ogr_geometry.STSrid = 4326

(which, incidentally, took 1 minute 52 seconds for 788 rows, demonstrating that updating an Azure database ain’t quick).

Clearly OGR needs a little work to do on SQL Azure support, but this was enough to get me going.

The WMS worked, so to challenge it a little more I set up another WMS onto a table with about 8000 sub-tidal habitat polygons, showing red in this OpenLayers map:

And here showing the rivers WMS as well (blue lines):

Performance of these two layers was perfectly acceptable, though actually about 100% slower than the same WMS working from a local SQL Server 2008 Express instance. It would be interesting to see what it could do with MapServer also running on Azure.

I also tried a WFS – again, performance was acceptable though significantly slower than running with a local SQL instance (though I didn’t do strict comparisons).

Quantum GIS and SQL Azure

I found this really helpful blog post to get started: http://woostuff.wordpress.com/2011/03/13/opening-ms-sql-spatial-in-qgis/

Adding the layers requires some python script, but I’m sure someone will write a GUI plugin for QGIS soon.

First make sure you are running a version of QGIS that has GDAL/OGR 1.8 libraries – I’m on 1.7.0 which is OK.

Open the Plugins menu > Python Console

and type in these two commands (modified for your data):

uri = "MSSQL:server={serverName};database={databaseName};tables={tableName};trusted_connection=yes"
qgis.utils.iface.addVectorLayer(uri,'{yourLayerNameHere}','ogr')

In my case:

uri = "MSSQL:server=tcp:a0awtn7gg9.database.windows.net;database=ArcSquirrel1;tables=esdm_waters;Uid=exegesis@a0awtn7gg9;Pwd=myPassword;"
qgis.utils.iface.addVectorLayer(uri,'Rivers','ogr')

Without any fuss my rivers dataset appeared:

And I can create and edit features and their attributes, here adding a test line off the Hebrides:

And to prove it, here’s my new feature in OpenLayers…

And finally, here we have the habitat polygons (EPSG:27700) colour coded by habitat type, as well as the rivers (EPSG:4326), in QGIS. Polygon editing also worked fine, once I’d worked out why my new polygons kept vanishing (doh, classified symbols).

Lots to think about, and I look forward to editing the same data with ArcGIS and ArcSquirrel soon…

INSERT INTO Orders ( CustomerID, EmployeeID, OrderDate )

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate

FROM Orders IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

Setting up a 32 bit ODBC connection in Windows Server 2008 x64. Start, Run, C:\Windows\SysWOW64\odbcad32.exe  (and best create a shortcut to this).

To setup the 64 bit ODBC connection go to: Control Panel, Administrative Tools, Data Sources (ODBC).

For consistency you should setup a matching 32 and 64 bit connections. Otherwise you may run into the error "[Microsoft][ODBC Driver Manager] Data source name not found".