How to import a large number of Individuals into an I/NET Access Control database

[Product_Support]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.

Issue

Want to be able to import a list of individuals quickly without having to retype all the details from a spreadsheet or text file again into I/NET

Product Line

TAC INET

Environment

• I/NET Seven Revision 2.xx and higher
• Microsoft Access 2003
• Microsoft Access 2007

Cause

Customers usually have a spreadsheet or database of employees and / or users which they want to quickly import into their I/NET Seven Access control database.  The process below describes how to do this along with its limitations.

Resolution

Process to import Individuals via the IDWorksView table using Microsoft Access 2003 / 2007

The import process will NOT update any existing data; it will only add new entries and leave existing entries as they were.  So this is typically a process used for a new installation.  If you are attempting to import into an existing Access control database with individuals then it is strongly recommended to backup your I/NET SQL database. It is also strongly recommended you test this in an ‘offline’ state, that is, it is not connected to any physical controllers until you are comfortable that the process provides the desired results.

The same limitations that apply with ID Works (Refer to TCON 301 Chapter 5) apply to this import process.

• TAC I/NET Seven (not just I/O Server) must be running on the server specified in TAC I/NET Seven’s ODBC data source in order for records created or modified from ID Works to be downloaded to affected DPUs.
• ID Works does not allow you to add or modify multiple cards for a single individual.
• ID Works does not allow you to remove a card from an individual without deleting the individual record.
• You cannot disable an individual’s card without disabling the individual.
• You cannot create a new group or a new tenant.  All Tenant and Group information must already exist in I/NET
• You cannot make an individual’s record be DPU resident.
• You cannot assign a decimal card number to an individual.
• You cannot set an individual’s APB scheme (that is, hard, soft, or graced anti-pass back).
• You cannot assign a PIN to an individual.

The steps to import are as follows:

1. Download ZIP file containing necessary files here. If you are using Microsoft Access 2007 use the following files
2. Format customer data into the Excel template (dbo_IdWorksView.xlt in the ZIP file) or provide this to the customer to complete the Employee details.  Pay attention to required data shown in the comments for each title cell.
3. Open db1.mdb file in the ZIP file using Microsoft Access 2003 /  idworks.accdb if using Microsoft Access 2007.
4. Double click (open) the table dbo_IdWorksView to establish the connection to I/NET on the respective machine.  You may be prompted for login details – you must use the ‘sa’ login and password installed on the PC.
5. If successful you should see any individuals that already exist in the database (unless blank).  Close the table. If you are using Microsoft Access 2007 Click Here to view the changes for steps 6 though to 8.
6. Right–click in any of the white space and select Import (or go to File>Get External Data>Import)
7. Browse to the location of the Excel Spreadsheet, saved in step 2, containing the data to be imported.  Be sure to change the File Type to *.xls otherwise you will not find the file.  Select the file.
8. Import wizard should appear, now select the following options within the import wizard:
   1. Select the Check box “First row contains column headings..”
   2. Select Next
   3. Select “In an Existing Table” and select the table from the drop down list – there should only be one there. dbo_IdWorksView. Select Next.
   4. Select Finish.
   5. Be patient – the process can take a few minutes based on the number of entries that you are trying to import.  Once imported you should receive a message indicating that the import was completed.  Select OK.
9. If you have images referenced in your import file, be sure that the Images are in place before modifying ANY individual in I/NET.  Otherwise the image path will be deleted. 
10. The next step is to get the imported data downloaded to the controllers.
    1. For each SLI perform a station SAV and station restore – it may take some time for all this information to be downloaded to the DPUs and may fail in some instances.  You may also notice the system slow up for some time (hours) until all of this information has been successfully downloaded to the controllers. 
    2. It may also be necessary to force the data into the DPU by performing staggered DPU Restores on specific doors that are experiencing incorrect functionality.

There is also a way to realize this function using Microsoft SQL Server Management Studio Express instead of Microsoft Access. See How to Import a Large Number of Individuals from Excel to I/NET SQL Database at One Time for more information.