Part of Community Services Data Set (CSDS) guidance
Sending your data as an Access file
Microsoft Access is a database management system from Microsoft. It is a member of the Microsoft 365 suite of applications, included in the Professional and higher editions or sold separately.
An Access database can store many different types of objects, such as tables, queries, forms, and reports. For the purposes of making a CSDS data submission, an Access file we refer to as Intermediate Database (IDB) has been created and this file contains only table objects. This static set of tables is built around the data submission requirements for CSDS.
A table contains a collection of fields that accept certain types of data. The tables have relationships, although this is not enforced in the IDB file. An example of such a relationship is having a table for referrals and a table for appointments. In this example we would expect to see a single record in the referral table for each distinct referral and then one or more records in the appointment table for each referral (a single referral can have many appointments).
Where to find the Intermediate Database (IDB) Access file
The IDB can be downloaded from the Technology Reference data Update Distribution (TRUD) site.
Step 1
Use the links in the top right of the page to either log in or create an account if you don't have one.
Step 2
Once logged in you can then search the site for the file you want. Here we have entered 'csds' in the search box. If you have not previously accessed this item in TRUD, you will need to click the Subscribe link.
Step 3
You should then be able to get to this screen. From here you can download the Access file.
Step 4
A zip file will then be presented. Once opened, the zip file should look like this. The Access file can then be taken out of the zip file.
CSDS submissions must be made using the latest and relevant CSDS IDB.
The structure of the IDB must not be altered in any way. Changes to the structure of the IDB will result in a rejected data submission.
The only part of the IDB that can be changed is the file name. This then allows organisations to name the file logically around the month submission they are making.
An option is to save a master copy from the TRUD site. Then for each submission window take a copy of the master file with the name of the reporting period in the file name. In that way it should only really be expected to go back to TRUD when a new version of the IDB comes out.
What the IDB Access database looks like
When opened, the IDB Access file should appear like this.
On the left-hand side you can see the tables. This is the full extent of the tables for CSDS and, even if not populated, each table must still be present in the submission.
Double-clicking a table will open it up as shown below. This then allows adding, viewing, editing, and deleting of records.
The asterisks on the record indicates that the row is empty.
The structure of the table can be viewed by looking at a table in 'Design View'.
Methods to populate the Access database
Manually
Each of the tables can be populated manually by simply typing into the row. This is commonly used when writing the single required record to the Header table. This, however, is not a practical solution for other tables.
Copy and paste
Copy and pasting data from an Excel sheet for example, directly into an Access table, is acceptable. Although individual columns or individual rows can be copied, it is much easier to simply copy all data across as one action.
Access has built in functionality that attempts to aid the user if Access has been unable to successfully paste data into a table. If records could not be pasted, because the data is too long for a certain field for example, then Access can create a table called paste_errors.
This is useful in seeing which records could not be pasted and to assist in resolving the data issue. However, this table must be deleted before attempting to make a submission using the portal. This is a structural change to the IDB and therefore the submission will not recognise the IDB file and will reject it.
Data Import Wizard
Built into Access is a Data Import Wizard process. This is a step-by-step process that allows you to tell Access a file you are attempting to populate to whichever table.
See Using the Access Import Wizard for a demonstration of how to use the Data Import Wizard
Similar to the point made above on the paste_errors table, Access can also create tables if there has been difficulty in importing data. These tables will contain the name import_errors and again must be removed before making the data submission.
Programmatically
Some organisations have looked to take away the time spent using the above methods by writing a piece of computer code (such as using Visual Basic) to automatically populate the Access database directly from their database or source files.
Last edited: 25 May 2021 5:00 pm