The knowledge base blog https://www.esdm.co.uk/knowledge http://www.rssboard.org/rss-specification mojoPortal Blog Module en-GB 120 no A Guide to the Ordnance Survey Archaeological Records Our HBSMR application is for collecting and managing Historic Environment Record (HER) data, usually as the permanent inventory for an area, and sometimes for short-term data collection or research initiatives.

Historic Environment Records evolved from the County "Sites and Monuments Records" that developed in the 1970s and 1980s, often pioneering the use of the emerging database technologies of the day. These "SMRs" in turn owe their origins to the records collected by the Ordnance Survey ("OS"), driven by O.G.S. Crawford in the 1920s and 1930s, which used a systematic method of cataloguing sites on index cards and 1:10,000 (or earlier 1 mile=6") map sheets. 

This fascinating guide to the Ordnance Survey records was recently found in the records of the Somerset Archaeological Society. It was presumably distributed by Ordnance Survey whenever they sent out copies of the cards.

The guide

THE ORDNANCE SURVEY'S ARCHAEOLOGICAL RECORDS

A. INTRODUCTION

The records consist of 8" x 5" index cards ('Record Cards') which give information about antiquities, and 1:10,000/1:10,560 maps ('Record Sheets') on which the sites of the antiquities are marked. The Record Card is described under B, the Record Sheet under C, and under D is a Glossary of the common abbreviations used on both cards and sheets.

B. THE RECORD CARD

The component parts of the current record card are as in Fig 1 below. There were some differences on older cards though the same purposes were served. The main differences are noted in the explanation.

Fig 1

 

1. ANTIQUITY NUMBER

This consists of the National grid number of the 1:10,000/10,560 sheet within which the antiquity falls (eg TQ 37 SW) plus a serial number within that sheet (eg TQ 37 SW 4), and used in its full form identifies the antiquity uniquely. The serial numbers are simply allotted consecutively as each record is compiled, and consequently have no bearing on where the site falls within the sheet, nor any typological significance.

There can also be subsidiary number (eg TQ 37 SW 4.1), used when a component part of an antiquity is allotted a card to itself.

There can in addition be a 'Miscellaneous Information card' for any sheet. This gives very brief information on items of marginal interest which fall on the sheet, and there is only one such card (or card series) for each sheet. It is identified by the sheet number plus a bracketed 'M' (e.g. TQ 37 SW (M)).

2. COUNTY

The name of the county at the date of the last entry. Abbreviations are those adopted by the Ordnance Survey.

3. PARISH

The name of the civil parish at the date of the last entry.

4. CLASSIFICATION

Abbreviations of the archaeological period(s) within which the subject(s) of a card falls, and also abbreviations to show that the period is unestablished, or that the subject of the record not an antiquity (within OS specification). The abbreviations and their meanings are given in the glossary under D below.

5. SPARE

Reserved for future extension of the classification.

6. PAGE NUMBER

The card side number whenever the record extends over than one card side.

7. SCHEDULING

The letter 'S' is entered when the antiquity is scheduled as an Ancient Monument by the Department Of the Environment. On old cards, scheduling was mentioned as text information and authorized. 'S's have not been comprehensively entered, and the lack of an 'S' is no guarantee that the antiquity is unscheduled.

8. SITING SUMARY

A Map-reference of the latest established site or find-spot of the antiquity, whenever this is known to at least a six-figure accuracy (i.e. to within about 100 metres). When siting is less precisely known, the name of the town, village or other place associated with it is entered with a four-figure reference. On old cards the siting summary was in the space serving for both purposes.

9. TYPOLOGY SUMMARY

A brief summary of the (latest established) nature of the antiquity in modern terms, taken from the information recorded, and accounting for each item in the record which has a different period classification.

10. PUBLICATION DIRECTION

Direction (for internal use) as to whether the antiquity is to be published on standard basic scale map, and if so the form the name should take and the type character to be used.

11. TEXT

The information necessary to demonstrate the siting, nature and condition of the antiquity (or to give as much as possible towards those ends), taken from whatever sources provide it, these sources being shown in the adjoining 'Authorities' part - see 12 below. The information can be entered either 'objectively' (i.e. by separate precis from each source) or 'subjectively' (i.e. by a single entry compiled from as many sources as are necessary), or by a mixture of these methods.

Normally the record will start with the objective entry of an Ordnance Survey publication. In subjective entries references are given to the various sources by use of the author's name or bracketed numbers. In objective entries the whole of the text is attributed to a single authority. The recorder can himself make interpolated comments, or even a separate entry of his own opinions. Field reports are always attributable to the Field Investigator himself unless otherwise stated.

12. AUTHORITIES

Each authority (source) consulted and used is given a number. With objective recording this number, with the title of the authority concerned, is positioned opposite the top line of the relevant text entry. With subjective recording, the numbers and authority titles are listed in sequence without interval, the first being opposite the first line of the text entry.

Secondary authorities (or sub-references) are lettered in lower case, and this lettering is in sequence throughout any one record (i.e. letters are not repeated even though under a different primary authority). Secondary authorities are only noted when they make necessary contributions but do not need to be used as primary authorities; it should therefore be appreciated that the primary authorities contain references which are not noted on the record cards. Secondary authorities may in fact have been consulted by the recorder, but the implication of the identification by lower case letters is that they have not. The recorder responsible for entries is identified by R1, R2 etc, with his initials and date, entered opposite the line of the last entry. The Field Investigator is similarly identified by F1, F2 etc, entered opposite the top line of his field report (and covering any recording between that entry and the last recorder's ascription). In the field reports informants are usually treated as secondary authorities, but they can be given separate entries as primary authorities. On old cards Recorders were designated 'OR' and Field Investigators FI and full names were given.

References dealing with relevant typology, but not with the specific antiquity which is the subject of a record, are mentioned in the text, not the Authorities compartment.

13. ILLUSTRATIONS

Illustrations are mounted on separate 'Illustration Cards' numbered in the same way as the record cards. The illustrations may be air photographs, ground photographs, diagrams, plans, or surveys, and they are folded to size if necessary. The photographs do not reproduce.

All Archaeology Division field surveys are ultimately included as illustrations, but there is delay in annexing them, as they have to pass through drawing stage (for the production of new OS maps) before becoming available.

C. THE RECORD SHEET

An example of the current record sheet marking is given in Fig 2 below. The essential ingredients are:

a. a convention marking the site

b. the antiquity number

c. a typological summary.

There were some differences on older record sheets though the same purposes were served. The main differences are noted in the explanations.

Ordnance Survey Archaeology Record Sheet Fig2
Fig 2 (Examples fictitious)

 

1. SITE MARKING

Various markings are used, and on the master copy record sheets different colours are also employed for further refinement. But the main purposes of the record sheets are to serve as indices to the record cards and to record the sites of antiquities topographically, and these purposes are just as well served by monochrome copies. All that is lost are certain nuances of siting and typology useful for quick reference but fully covered on the record cards.

The markings are: Crosses, Published detail, Sketched outline, Bands, Linear markings and Broken linear markings.

The following explanations deal with their monochrome fom only, so more than one meaning sometimes attaches to a single convention. The separate implications however can be readily clarified by reference to the record cards.

Crosses

a. Accurate find-spot or site, whether or not extant (Ex 5).

b. Inaccurate site or find spot (Ex 1).

c. Centre of an extensive antiquity when the outline cannot be properly recovered (Ex 11). 

Published detail

This is normally left to stand by itself (Exs 9, 2.1) but it can be shaded or blocked in when it is necessary to distinguish it from adjacent features (Ex 12). 

Sketched outline

This is used in preference to a cross for large antiquities which are not published on the record sheet (Ex 10).

Bands

a. Defining a group of antiquities treated on a single record card (Ex 4).

b. Defining the known outline of a feature or site with no perimeter feature (eg a DMV) (Ex 2).

c. Defining the area within which an antiquity occurs, when that area is already defined by published detail, such as hedges (Ex 7).

Linear Markings

The course of a linear antiquity; including extensive enclosure banks etc such as park pales (Exs RR46, Lin 21).

Broken linear markings

The course of a linear antiquity when uncertain (Ex RR46).

2. ANTIQUITY NUMBERS

A plain number in a circle is the number allotted to the record card. It should be noted that to identify the antiquity uniquely the sheet number must be added to this (Exs 1-12).

A decimalised number in a circle refers to a subsidiary record (cf B 1 above) (Ex 2.1).

A smaller number in brackets applies to miscellaneous information (cf B 1 above) (Ex (1)).

The entry of a number in the east margin of the sheet conveys that the antiquity concerned probably falls on that sheet but cannot be sited with any accuracy (Exs 6, 8).

A full number in brackets entered in the margin adjacent to the marking of an antiquity which extends over the edge shows that the card is allotted to the adjacent sheet (Ex (TQ 37 NW6), in north margin).

Linear antiquities are numbered according to a separate Linear system, which is not controlled by sheets. Roman Roads are given the Margary numbers ('Roman Roads in Britain'), otherwise the numbering system is the Ordnance Survey's. Linear numbering is entered on the sheets in brackets, normally along the axis of the Linear (Exs RR46, Lin 21).

3. TYPOLOGICAL SUMMARIES

These are similar to the typological summaries on the record cards (cf B9 above), though they need not be exactly the same.  They are entered on the sheets for convenience and as a safeguarding connection with the record card, additional to the number.

D. GLOSSARY OF ABBREVIATIONS USED ON THE ORDNANCE SURVEY'S ARCHAEOLOGICAL RECORD CARDS AND SHEETS

This is not comprehensive, but should account for the majority of the abbreviations which might be puzzling. It does not account for the book title abbreviations used in the 'Authorities' part of the record card, which should be easily understood, nor for the initials of the compiling staff (see B 12 above) Which are irrelevant to the understanding of the records.

ABBREVIATION MEANING AREA OF OCCURRENCE
(Number of component
part of record card,
as in Fig 1, or 'S')
AAO Assistant Archaeology Officer 12
Acc No Museum Accession number 11
Accn No "              "                "  11
AO Archaeology Officer 12
" Archaeology Division 12
AP Air photograph 11, 12
AS Anglo-Saxon 9, 11, S
AT Antiquity Tpe character 10, 11
Authy Authority 11
BA Bronze Age 4, 9, 11, S
BM British Museum 11
C or c Century 9, 11
c Circa 11
Corr Ordnance Survey arch. correspondent  12
Corr 6" Correspondent's annotated 6" sheet 12
Crem Cremation 9, 11
DA Dark Ages 9, 11, S
DOE Department of the Environment 12
DMV Deserted medieval village 9, 11, S
diss Dissolved (monasteries) 11
EBA Early Bronze Age 9, 11, S
EI Early Industrial 4, S
EIA Early Iron Age 9, 11, S
Ewk Earthwork 9, 11, S
Ed Editor 11, 12
Ed Edition 11, 12
F1, F2 etc First, second, etc OS Arch fd investigator  12
fd Found 11, S
" Founded 11, S
GP Ground photograph 11, 12
GS Gil Sans type character 10, 11
GT German Text type character 10, 11
HHR DOE Historic Houses record 12
IA Iron Age 4, 9, 11, S
IAM DOE Inspectorate of Ancient Monuments 11, 12
Inf Informant, or Information from 12
Inhum Inhumation 9, 11
LB Lutheran Black type character (Obs) 11
LB Long Barrow 11
LBA Late Bronze Age 9, 11, S
Lin Class name for linear feature other than a Roman road  11, S
(M) Miscellaneous Information card 1
MBA Middle Bronze Age 9, 11, S
Md Medieval 9, 11, S
Me Mesolithic 4, 9, 11, S
MHLG Ministry of Housing & Local Govt (Obs) 12
MOW Ministry of Works (Obs) 12
MPBW Ministry of Pub Works & Buildings (Obs) 12
Mus 6" Annotated Museum 6" sheet 12
NAT Non-antiquity type character 10, 11
Ne Neolithic 4, 9, 11, S
NMR National Monument Record 11, 12
NR Non-Roman Type character 10, 11
Occ Occupation 9, 11, S
ONB Ordnance Survey 'Object Name Book' 11, 12
OR Office Recorder (OS staff) (Obs) 12
OS Ordnance Survey 11, 12
P Continue to publish in same form 10
Pa Palaeolithic 4, 9, 11, S
Poss Possible 9, 11, S
PN Post-Norman 4
PR Post-Roman 4
Prob Probably 9, 11, S
Pte 6" Private 6" sheet, annotated 12
R1, R2 etc First, second, etc OS Arch recorder 12
Rec 6" Records 6" (obsolete annotated sheet) 12
Rems Remains of 9, 11, S
RB Romano-British 9, 11, S
R & F Ridge and furrow 9, 11
Ro Roman 4, 9, 11, S
RR Roman Road with Margary number 1, 9, 11, S
RRX Roman Road without Margary number 1, 9, 11, S
S Scheduled by IAM (qv) 7
SS Rev OS Small Scales reviser 12
S/Svyr Senior Surveyor (Archaeology Division) 12
TI Times Italic type character 11
TU Times Upright type character 11
U Period unestablished 4, 9
X Not an antiquity 4
X Do not publish on standard maps 10
     

 


Crispin Flower  ...]]>
https://www.esdm.co.uk/a-guide-to-the-ordnance-survey-archaeological-records crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/a-guide-to-the-ordnance-survey-archaeological-records https://www.esdm.co.uk/a-guide-to-the-ordnance-survey-archaeological-records Sun, 07 Apr 2019 17:58:00 GMT
Fixing ArcGIS personal geodatabase autonumber problem A number of HBSMR clients have been experiencing problems with creating new features in ArcGIS personal geodatabases via MapLink, resulting in a error similar to this:

Your edits could not be saved
Error HRESULT E-FAIL has been returned from a call to a COM component.
Would you like to keep the temporary edit layers in the map?

The problem is due to a fault with the MS Access database that forms the geodatabase, which means after a feature has been moved or deleted the database tries to reuse that ID value and this upsets the autonumber process, or so we believe.

At present we do not have a solution to this problem but we do have a bit of code that can be used to reset the autonumber values for any chosen field. This code can be used as a function and bundled in an mdb with a macro that runs the function and sent out to clients as needed. The code for the functions is as follows (this was originally Hartmut's code but I modified from the a sub to a function adding the return and msgbox, just so I could see that something was happening):

 

Public Function ResetAutoNumber(ByVal tableName As String, ByVal autoNumberColumnName As String)
    Dim maxAutoNumber As Long
    maxAutoNumber = DMax(autoNumberColumnName, tableName) + 1
    DoCmd.RunSQL "ALTER TABLE " & tableName & " ALTER COLUMN " & autoNumberColumnName & " COUNTER(" & maxAutoNumber & ",1)"
    ResetAutoNumber = True
    MsgBox tableName + " done!"
End Function

 

And macro simple needs to have a number of RunCode lines that call this function for each of the layers in the personal geodatabase, like so:

 

RunCode

       Function Name ResetAutoNumber ("Mon_point", "OBJECTID")

RunCode

       Function Name ResetAutoNumber ("Mon_poly", "OBJECTID")

RunCode

       Function Name ResetAutoNumber ("Mon_line", "OBJECTID")

 


Steve Ellwood  ...]]>
https://www.esdm.co.uk/fixing-arcgis-personal-geodatabase-autonumber-problem stevee@esdm.co.uk (Steve Ellwood) https://www.esdm.co.uk/fixing-arcgis-personal-geodatabase-autonumber-problem https://www.esdm.co.uk/fixing-arcgis-personal-geodatabase-autonumber-problem Wed, 29 May 2013 12:42:00 GMT
MapLink issue when packaged with ArcMap 10 and HBSMR or CAMS using AppV ArcGIS (v10 on) no longer reads component categories from the registry, instead component category information is read from configuration files (*.ecfg) stored in:

C:\Program Files (x86)\Common Files\ArcGIS\Desktop10.0\Configuration\CATID

When ArcGIS 10 is packaged in an AppV sequence (bubble), to be used with HBSMR or CAMS, in their own sequences, {a4d1597d-bc1c-4f37-b12a-4bcb2cd2ec2f}_xgmaplinkarcmapextension.ecfg must be present in the real file store rather than Virtual File Store. Ie :

%ProgramFiles(x86)%\Common Files\ArcGIS\Desktop10.0\Configuration\CATID\{a4d1597d-bc1c-4f37-b12a-4bcb2cd2ec2f}_xgmaplinkarcmapextension.ecfg

The GUIDs shown above are for MapLink v4. The following GUID: {a315d61f-0f18-41c8-88dc-f650ae76014c} is for MapLink v5 for ArcGIS.


  ...]]>
https://www.esdm.co.uk/maplink-issue-when-packaged-with-arcmap-10-and-hbsmr-or-cams-using-appv () https://www.esdm.co.uk/maplink-issue-when-packaged-with-arcmap-10-and-hbsmr-or-cams-using-appv https://www.esdm.co.uk/maplink-issue-when-packaged-with-arcmap-10-and-hbsmr-or-cams-using-appv Mon, 17 Dec 2012 15:53:00 GMT
Handling different character encodings when importing tabular data from CSV I’ve been fixing some minor issues with the utility in HBSMR that imports data from the Portable Antiquities Scheme. One issue was that some characters were mojibaked during the import, for example this text:

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.


Crispin Flower  ...]]>
https://www.esdm.co.uk/handling-different-character-encodings-when-importing-tabular-data-from-csv crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/handling-different-character-encodings-when-importing-tabular-data-from-csv https://www.esdm.co.uk/handling-different-character-encodings-when-importing-tabular-data-from-csv Mon, 24 Sep 2012 11:47:25 GMT
Windows updates break MS Common Controls Tree Control This is a replacement blog-post, based on our developing understanding of the problem and users' experiences. It appears that the update that disrupts the operation of 'tree' controls in HBSMR v3 and v4 is Security Update MS12-060. Symptoms include inability to select items in a tree or, in the case of v3, an inability to add new MonTypes, Components etc. The problem relates to the version and registration of mscomctl.ocx

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

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

 

 


Crispin Flower  ...]]>
https://www.esdm.co.uk/windows-updates-break-ms-common-controls-tree-control crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/windows-updates-break-ms-common-controls-tree-control https://www.esdm.co.uk/windows-updates-break-ms-common-controls-tree-control Thu, 16 Aug 2012 08:42:00 GMT
HBSMR Consultation Stages not displaying correctly HBSMR Consultation Stages not displaying correctly. Fixed by updating screen driver.


  ...]]>
https://www.esdm.co.uk/hbsmr-consultation-stages-not-displaying-correctly () https://www.esdm.co.uk/hbsmr-consultation-stages-not-displaying-correctly https://www.esdm.co.uk/hbsmr-consultation-stages-not-displaying-correctly Wed, 08 Aug 2012 15:36:00 GMT
Multiple SQL Server log-in failures caused by connection string error - HBSMRv3 A SQL Server DBA reported many login failures in his SQL logs - variously quoted as “hundreds per day” and “50+ in a minute”. These failures were not apparent to system users. Two user communities were accessing this data - one using trusted connections the other using SQL Server Authentication. Only the Trusted Connection group were affected by this issue. The connection string included the clause <Trusted_Connections=Yes>, this should have been <Trusted_Connection=Yes>. Once the connection string had been corrected the log-in failures stopped.

Example of repeated log-in failures from SQL logs:

2012-04-18 09:19:44.50 Logon       Login failed for user 'smruser'. [CLIENT: 159.15.250.xxx]
2012-04-18 09:19:44.53 Logon       Error: 18456, Severity: 14, State: 5.
2012-04-18 09:19:44.53 Logon       Login failed for user ‘yyyy.xxxx’. [CLIENT: 159.15.250.xxx]
2012-04-18 09:19:44.54 Logon       Error: 18456, Severity: 14, State: 5.
2012-04-18 09:19:44.54 Logon       Login failed for user 'smruser'. [CLIENT: 159.15.250.xxx]
2012-04-18 09:19:46.87 Logon       Error: 18456, Severity: 14, State: 5.
2012-04-18 09:19:46.87 Logon       Login failed for user ‘zzzz.yyyy’. [CLIENT: 159.15.250.xxxx]
2012-04-18 09:19:46.90 Logon       Error: 18456, Severity: 14, State: 5.


  ...]]>
https://www.esdm.co.uk/multiple-sql-server-log-in-failures-caused-by-connection-string-error-hbsmrv3 () https://www.esdm.co.uk/multiple-sql-server-log-in-failures-caused-by-connection-string-error-hbsmrv3 https://www.esdm.co.uk/multiple-sql-server-log-in-failures-caused-by-connection-string-error-hbsmrv3 Mon, 23 Apr 2012 08:28:00 GMT
HBSMR v3.x MIDAS XML export: bad schema reference The MIDAS xml export from the HBSMR Monument Index form includes an out-of-date URL link for the MIDAS xml schema. This means that anybody trying to import the data into a database or other application from the exported xml is unlikely to succeed because the schema is required to understand the xml data.

The schema location is added to the export by the xsl transform file xgMIDASxmlMon.xsl which is located in the mdb\xsl folder of the HBSMR application. You can correct the URL link to the schema by opening the xsl file (best done using Notepad) and replacing the section near the top between

<xsl:template match="/">

and

<!-- metadata inserted here - do not remove or alter these tags -->

with

<monuments xmlns="http://www.heritage-standards.org.uk/midas/schema/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.heritage-standards.org.uk/midas/schema/1.0 http://www.heritage-standards.org.uk/midas/schema/1.0/midas_monument.xsd">

This will be fixed in the version 4 upgrade.


Crispin Flower  ...]]>
https://www.esdm.co.uk/hbsmr-v3x-midas-xml-export-bad-schema-reference crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/hbsmr-v3x-midas-xml-export-bad-schema-reference https://www.esdm.co.uk/hbsmr-v3x-midas-xml-export-bad-schema-reference Thu, 01 Mar 2012 14:19:33 GMT
AGI Cymru Conference 2011: HBSMR Mobile presentation Held at Cardiff City Hall at the end of November, the AGI Cymru winter conference was attended by around 80 people and had a fair mix of managers and developers from different organizations around Wales. The mobile stream proved to be quite interesting with three presentations from ESDM, Cardiff Uni and MapAction.

Laine Skinner from ESDM presented a paper about a mobile application we have written for our HBSMR product, which is used by Historic Environment Records to manage heritage inventories and to support casework.

The presentation described how we created a mobile application over a short-targeted project in January 2011. The project itself was completed in a RAD/agile manner and highlights a successful way of working with a technology which itself is also undergoing huge change every few months.

The presentation is available online here.


Crispin Flower  ...]]>
https://www.esdm.co.uk/agi-cymru-conference-2011-hbsmr-mobile-presentation crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/agi-cymru-conference-2011-hbsmr-mobile-presentation https://www.esdm.co.uk/agi-cymru-conference-2011-hbsmr-mobile-presentation Fri, 13 Jan 2012 18:54:00 GMT
MS Access DAO recordset + SQL Server: how to get the ID of a newly inserted record Sometimes when inserting records programmatically you want to create a parent record first, then add records into related tables where the Foreign Key is on an autonumber field. Therefore you need to know the new primary key value for the new parent records.  When using DAO against a SQL Server table, a recordset behaves slightly differently from MDB/ACCDB tables. Here’s the easiest way to get the ID of a newly inserted record.

   1:  Dim rs As Recordset, newID As Long
   2:  
   3:  
   4:  
   5:  Set rs = CurrentDb.OpenRecordset("Memory", dbOpenDynaset, dbSeeChanges)
   6:  
   7:  rs.AddNew
   8:  …populate fields
   9:  rs.Update
  10:  
  11:  ' the record has been committed, but the cursor has jumped to absoluteposition 1
  12:  ' move to the new record and get the ID (my autonumber field is called ID)
  13:  rs.Move 0, rs.LastModified
  14:  newID = rs("ID")
  15:  ' now use this ID in creating related records
  16:  
  17:  

Crispin Flower  ...]]>
https://www.esdm.co.uk/ms-access-dao-recordset--sql-server-how-to-get-the-id-of-a-newly-inserted-record crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/ms-access-dao-recordset--sql-server-how-to-get-the-id-of-a-newly-inserted-record https://www.esdm.co.uk/ms-access-dao-recordset--sql-server-how-to-get-the-id-of-a-newly-inserted-record Fri, 23 Dec 2011 16:09:00 GMT
How to prevent MS Access truncation or mangling of expressions over 255 characters in length There’s a weakness in Access/JET that if a expression based on a memo field outputs a string longer than 255 characters, the result gets garbled when used in a recordset. This does not affect normal queries, forms or reports, unless they rely on a recordset (e.g. the expression uses a custom funciton that uses a recordset, or the whole expression is processed in a recordset).

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:

 


Crispin Flower  ...]]>
https://www.esdm.co.uk/how-to-prevent-ms-access-truncation-or-mangling-of-expressions-over-255-characters-in-length crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/how-to-prevent-ms-access-truncation-or-mangling-of-expressions-over-255-characters-in-length https://www.esdm.co.uk/how-to-prevent-ms-access-truncation-or-mangling-of-expressions-over-255-characters-in-length Mon, 05 Dec 2011 08:19:00 GMT
HBSMR/PACS reports fail if no default printer present If trying to print a report in HBSMR or PACS (or any other MS Access application) gives an error message along the lines of "cannot find report or form MonFullRpt", even though this report does exist, this is likely to be caused by the PC not having a default printer set. This is because Access cannot preview a report if it doesn't know what printer it is working with.


Crispin Flower  ...]]>
https://www.esdm.co.uk/hbsmrpacs-reports-fail-if-no-default-printer-present crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/hbsmrpacs-reports-fail-if-no-default-printer-present https://www.esdm.co.uk/hbsmrpacs-reports-fail-if-no-default-printer-present Wed, 06 Feb 2008 16:38:00 GMT
Setting Microsoft Access Macro Security Settings using the registry If the Macro Security setting cannot be changed through the Access application (can be disabled in the installation), this registry setting can help.
To set the security to "Low":

[HKEY_Local_Machine\SOFTWARE\Microsoft\Office\11.0\Access\Security]
"Level"=dword:00000001

This applies to Office 2003, but similar keys exist for earlier versions. This may not apply to Office 2007 - further research needed.

 

 


Crispin Flower  ...]]>
https://www.esdm.co.uk/setting-microsoft-access-macro-security-settings-using-the-registry crispin.flower@idoxgroup.com (Crispin Flower) https://www.esdm.co.uk/setting-microsoft-access-macro-security-settings-using-the-registry https://www.esdm.co.uk/setting-microsoft-access-macro-security-settings-using-the-registry Tue, 27 Feb 2007 12:47:18 GMT