SUS PbR Data Load Tool

1
SUS PbR Data Load Tool

• Version 1.00

2
Introduction
3
About the file loader

• The file loader has been developed to help users
  load the SUS PbR Extract files into an MS-Access
  environment
• It provides a wrap around the 4 PbR extract
  definitions previously provided and
• Supports simpler file loading
• Provides a log of the load which includes key
  characteristics of loaded files
• Provides an mechanism to pass files to microsoft
  excel
• Provides a degree of security
• In order to support the additional functions
  around security, the file must be loaded with a
  custom workgroup file APC.mdw.
• More details are provided later in this pack

4
Changes

• The supplied structure tables (labelled str)
  are now explicitly maintained as structure files
  used to generate new instances of the files to be
  loaded.
• These files cannot be loaded with data but are
  used to generate the files to be loaded
  reflecting the original guidance on the use of
  the file structures
• There is a simple workaround if you have
  developed reports based on the file structure
  tables this is covered in the appendix
• Post processing is applied to AE and Spell
  tables
• To null default derived age (8000) and date
  (1/1/1000) values
• To convert times for fields which are output in
  old NHS format (hhmm) into properly maintained
  time values
• Amongst other things, these changes offer a
  mechanism for loading data into SQL server as
  they allow data to be transferred to
• SQL server using the Access to SQL server route.

5
Corrections

• Corrections have been made to the previous file
  structures
• Some field names were issued with a trailing
  comma in the name of the field due to an error in
  the software used to generate them. These have
  been removed and the field name will have changed
  in consequence
• For AE (only) the fields INTERCHANGE_BULK_REPORT_
  START and INTERCHANGE_BULK_REPORT_START were
  previously transposed and this has been corrected
• For AE and Outpatients the field
• DOB has been renamed BIRTH_DATE for consistency

6
Getting started
7

• Two versions of the database are provided on the
  SUS website at www.cfh.nhs.uk/sus/reference under
  the PbR subheading
• 0607structures_v100_s.mdb for use with Access
  2002 2003
• 0607structuresV100_2000_s.mdb for use with
  Access 2000
• These have been compiled under the relevant
  Access version there are no other differences

8
Getting Started - 1 Join to apc.mdw file

• The database now has a custom file apc.mdw (or
  apc2000.mdw for Access 2000 users) - supplied
  with it. This is an MS-Access workgroup file used
  to enforce security.
• The database will not open unless linked to this
  file. This can be done either
• By setting up a custom shortcut this is the
  recommended approach
• By using the workgroup administrator within
  MS-Access.
• Details of both approaches will be found in the
  appendix

Security has been added in recognition of the
fact that some users have been using the database
to hold patient identifiable data. While this
improves matters, it is to be emphasised that
users retain responsibility for, and MUST take
action to, ensure that any locally maintained
patient level data is kept in a fully secure
environment.
9
Getting Started 2 - Users

• You will be prompted for a user name. Two are
  pre-defined
• NHSAdmin which has administrator rights
• NHSUser which does not
• There is no password the first time you use
  either. Once you enter the application for the
  first time, you will be prompted for a new
  password and cannot continue until you create one
• NHSAdmin can set up new users. The password
  process is the same

10
Getting Started 3 Digital Certificate
The application has been digitally signed as
SUSPbRLoadTool_R9Depending on your local
system rights, you may be able to accept the
certificate on a continuing basis by clicking the
Always trust.. box
11
Getting Started 4 - First use screen
If you do not enter a password you will not be
able to continue (This requirement also holds for
locally created accounts)
12
Loading the data

• Note this part of the slide pack should be
  viewed as a slide showWhen you click to start
  the animation, it then proceeds automatically up
  until the next command

13
Initial menu
14
Click to start animation
Initial view of load screen Load for PbR extracts
is pre-set, but the form will support load of
other datasets
15
If import errors have been found you can see them
by clicking here or click advanced
Identify cases to be loaded (can be more than
one)
Click to start animation
Click Load
Results
16
If load errors occur you can Click here to see
the access generated import error table
Load messages in this case the data loaded but
with errors
but no problems with post processing
Click to start animation
17
Following data load of all PbR Extracts
Newly created files
18
Sending files to Excel
19
Click to identify table or Query
Click to open file dialog for file save
Click to see drop down of available tables /
queries

• Note To extract a subset of data to excel
• use MS-access to write a select query
• Save the Query
• In the MS-Access query window click right against
  the query, and write a description for the query
• The query will be available for extract through
  the Save to Spreadsheet form

Click Export
20
The load screens in more detail
21
Appending data to a table

• The Append flag to append multiple files into the
  same table. The append flag can only be set from
  the Advanced screen, though it is repeated for
  information on the main screen.
• If it is important that files are processed in
  order, this can be controlled by inserting a
  numeric value in the SEQ field data is loaded
  in ascending order.
• If the append flag is not sent any existing table
  with the same file as the output will be deleted

22
Loading Your Own Data

• Tables names are based on the following
  conventions
• Structure files used as the framework for
  loading data commence with str
• Output files which will contain loaded data
  start with tbl
• These naming conventions are used primarily to
  limit drop-downs
• An output file name with a leading Tilde, once
  specified, cannot be changed and is used when a
  fixed table name is required to support post
  processing
• Specification files
• Are specified via the Advanced tab within the
  native text load functionality of MS-Access

FilegtImportgtExternal Datagtchoose text
filegtAdvancedgtCreate SpecificationgtSave as

• Once saved the specification can be accessed via
• the dropdown on the detailed screen

23
Flags

• The following flags are available for use by
  administrators through the advanced window. They
  may be useful when loading local data
• IsPbRExtract
• Indicates that after load data should be
  extracted from the data to populate the fields
  relating to organisation, unique ID etc
• Protect
• Prevents the relevant record being deleted from
  the database. (Note that output table names are
  not protected from change unless preceded by a
  tilde ()
• IsRepeatLoad
• Indicates that the most recent version of the
• record should be copied back to provide a
  structure for the next load

24
Setting up your own loads

• The following is not the only approach, but works
• Choose the load option at the bottom of the
  screen and identify a temporary table name and
  identify the file to be loaded.
• You may need to change the heading for
  with/without headers on the detailed screen (open
  by clicking Advanced)
• Load the file
• Inspect the file and rename columns and set
  column parameters as required.
• Delete all episodes in the file and rename to
  str
• If necessary to maintain a specification file,
  undertake a manual load, naming the str file.
  Save the specification
• Clear the str file by deleting all records
• Set IsRepeatLoad and Protect flags

25
Appendix 1

• Security set up

26
Appendix - Workgroup files

• Important
• if you create a new database of your own while
  joined to APC.mdw, you will only be able to open
  it while linked to APC.mdw and with the users
  identified in APC.mdw
• Backup the APC.mdw file and keep it secure if
  it becomes corrupted you cannot access the
  application
• Note
• Although you can save the .mdw file anywhere, it
  is best to locate it in the same directory as the
  system.mdw file as it becomes easier to switch
  between the system and apc files
• The default directory for the system.mdw file is

"C\Documents and Settings\ltusernamegt\Application
Data\Microsoft\Access
27
Option 1 - Using Workgroup Administrator

• For Access 2002 2003 work group administrator
  can be found by following the menu path
• ToolsgtSecuritygtWorkgroup AdministratorgtJoin
• For Access 2000 you must use the separate
  workgroup administrator supplied with office
  tools
• Note the location of system.mdw (the default
  workgroup file) as you may want to rejoin it
• Browse to and open the apc.mdw file (APC2000.mdw
  for Access 2000)

28
Creating a custom icon
Option 2 - Using a custom shortcut

• Open the folder where Microsoft Access is
  installed (located by default in C\Program
  Files\Microsoft Office\Office11).
• Right-click the Microsoft Access program icon,
  and then click Create Shortcut.
• Right-click the shortcut icon just created, click
  Properties, and then click the Shortcut tab.
• Replacing the ltfilepathgt in the text below with
  the file path appropriate to your system, replace
  the entry in the Target field with the text
  below
• the text assumes that access is loaded in the
  default location
• It is easiest to prepare the string you want to
  use in a text editor and then cut and past it
  into the Target field
• There are advantages in placing the APC.mdw file
  in the same location as the system.mdw file see
  next slide

"C\Program Files\Microsoft Office\OFFICE11\MSACCE
SS.EXE" "ltfilepathgt\0607structuresV100_S.mdb"
/wrkgrp "ltfilepathgt\apc.mdw"
Note  After you create a shortcut you can put it
on your desktop, or in a folder or add it to the
Start menu. Search Microsoft Windows Help for
"adding shortcuts" for more information.
29
Appendix 2

• If you have already written queries based on
  files with names starting str

30
Appendix 2 existing use of str table names

• Use this application to load text files into
  defined tbl files
• Take a backup copy of the database which contains
  the existing application
• Link from the old application to the relevant
  files in the load application using
• FilegtGet external datagtLink Tables
• Create the links
• Delete the tables in the old application which
  will replaced by those loaded through the load
  application
• Rename the linked tables to the old table names