Setup an ODBC Connection

An ODBC connection allows users to access the XenDirect data tables using Microsoft Access, Crystal Reports, or third party other software, and create queries and reports.

 

---

Setting Up the ODBC Connection

 

In order to make an ODBC connection, the user's IP must be entered into Xenegrade's safe IP database.   This helps secure databases and prevent unapproved access.  It is highly suggested to use a static IP address and not a dynamic IP address.  Submit the user's IP address via a ticket to start the approval process.  The process to approve and record a safe IP address can take up to 48 hours.

 

Follow these steps to create an ODBC connection.  An ODBC connection must be made separately for each database.  The IP address of the computer you are creating must be on file in our allowed access list.  It is highly suggested to create the ODBC connection on a computer that as a static (never changes) IP address and not a dynamic (can change) IP address.  With a dynamic IP address, your ODBC connection will fail every time the IP address changes.

1. Go to Control Panel on your computer.

2. Select Administrative Tools.

3. Select Data Sources.

4. Select the System DSN tab. You can use User DSNs, but System DSNs are faster.

5. Click the ADD button.

6. Select SQL Server

7. Click Finish.

8. Enter the Name of your XenDirect database (the name will be supplied to you by Xenegrade Corp.)

9. Enter an optional description.

10. Enter the server number (Server number will be provided to you by Xenegrade Corp.)

11. Click the NEXT button.

12. Choose the option "With SQL Server".

13. Click the "Client Configuration" button. Check to be sure that under Network Libraries, TCP/IP is chosen, and click OK.

14. Type in the user name and password. (These will be supplied to you by Xenegrade Corp.)

15. Click the NEXT button.

16. Select "Change the Default Database" and select the your database name from the list.  The database name includes a year.  Be sure to select the correct year for the ODBC name you entered in Step 8.

17. Click the NEXT button.

18. Click the Finish button.

19. Click the Test button.

20. If the test is successful, click the OK button to close and complete the connection.

21. Close the Add ODBC window.

 

---

Modify an Existing ODBC Connection

 

Follow these steps to modify an ODBC connection.  An ODBC connection must be made separately for each database.

1. Go to Control Panel on your computer.

2. Select Administrative Tools.

3. Select Data Sources.

4. Select the System DSN tab. You can use User DSNs, but System DSNs are faster.

5. Locate and highlight the existing ODBC.

6. Click the CONFIGURE button.

7. Modify the Name of your XenDirect database if desired.

8. Modify an optional description.

9. Modify the server number only if it is incorrect.  (Server number will be provided to you by Xenegrade Corp.)

10. Click the NEXT button.

11. Choose the option "With SQL Server".

12. Click the "Client Configuration" button. Check to be sure that under Network Libraries, TCP/IP is chosen, and click OK.

13. Type in the user name and password. (These will be supplied to you by Xenegrade Corp.)

14. Click the NEXT button.

15. Select "Change the Default Database" and select the your database name from the list.  The database name includes a year.  Be sure to select the correct year for the ODBC name you entered in Step 7.

16. Click the NEXT button.

17. Click the Finish button.

18. Click the Test button.

19. If the test is successful, click the OK button to close and complete the connection.

20. Close the Add ODBC window.

---

Linking ODBC and a Microsoft Access Database

 

Be sure to follow these steps exactly.

 

Existing Database

1. Open up Microsoft Access. 

2. Use the File > Open menu to locate an existing Access database.

3. Go to the File Menu.

4. Select Get External Data.

5. Select Link Tables.

6. Choose the ODBC file type, click the Machine Data Sources tab, and select the ODBC desired.

7. You will be prompted to enter your user name and password. This is the user name and password for your ODBC connection, not your XenDirect user name and password.

8. Click Select All or the tables desired.

9. Check the Always Prompt for New Location checkbox.

10. The process will start linking all of your XenDirect tables. If prompted for a primary key, click Cancel.

11. You can close the link window when you are prompted that all tables have been linked.

Backup/NRS Download Database (using menu panel)

1. Open up Microsoft Access. 

2. Use the File > Open menu to locate the NRS Download Access database.

3. Click the Refresh ODBC Links button.  (If button fails to function, close this process down and use Manual method in next group of steps.)

4. Click the Select All button.

5. Check the Always Prompt for New Location checkbox.

6. You will be prompted to enter your user name and password. This is the user name and password for your ODBC connection, not your XenDirect user name and password.

7. Locate and click on the desired ODBC connection in either the File Data Source or Machine data Source tabs of the Select data Source window.

8. Click the OK button.

9. The process will start linking all of your XenDirect tables. If prompted for a primary key, click Cancel.  This process may take several minutes.

10. You can close the link window when you are prompted that all tables have been linked.

Backup/NRS Download Database (using manual linking method)

If Access Runtime is installed on your computer, the buttons in the Menu Panel may not function.  Follow these steps instead.

1. Open up Microsoft Access. 

2. Use the File > Open menu to locate the NRS Download Access database.

3. Click the Database Window button.  The Menu Panel will close.

4. Maximize the database window that is most likely in the lower left corner of the Access window.

5. Go to the Access menu Tools >> Database Utilities >> Linked Table Manager.

6. Click the Select All button.

7. Check the Always Prompt for New Location checkbox.

8. You will be prompted to enter your user name and password. This is the user name and password for your ODBC connection, not your XenDirect user name and password.

9. Locate and click on the desired ODBC connection in either the File Data Source or Machine data Source tabs of the Select data Source window.

10. Click the OK button.

11. The process will start linking all of your XenDirect tables. If prompted for a primary key, click Cancel.  This process may take several minutes.

12. You can close the link window when you are prompted that all tables have been linked.

New Database

1. Open up Microsoft Access. 

2. Use the File > New and blank database to create a new Access database.

3. Type in an appropriate file name and location for your XenDirect Access file.

4. Click the Create button.

5. Go to the File Menu.

6. Select Get External Data.

7. Select Link Tables.

8. Choose the ODBC file type, click the Machine Data Sources tab, and select the ODBC desired.

9. You will be prompted to enter your user name and password. This is the user name and password for your ODBC connection, not your XenDirect user name and password.

10. Click Select All or the tables desired.

11. Check the Always Prompt for New Location checkbox.

12. The process will start linking all of your XenDirect tables. If prompted for a primary key, click Cancel.

13. You can close the link window when you are prompted that all tables have been linked.
    

---

Linking ODBC and Crystal Reports

 

Setting up Crystal Reports.  You must own and have knowledgeable using Crystal Reports.

For a New Report

1. When the Data Explorer window appears, expand the ODBC folder and select the ODBC data source you created earlier.

2. Enter your login name and password for your ODBC connection as supplied by Xenegrade Corp.

3. Add one or more tables as required for your report requirements.

For an Existing Report

1. Once the report is open, go to Tools/Add Database Source.

2. When the Data Explorer window appears, expand the ODBC folder and select the ODBC connection you created earlier.

3. Enter your login name and password for your ODBC connection as supplied by Xenegrade Corp.

4. Add one or more tables as required for your report requirements.

---

Understanding the Data Model

 

XenDirect utilizes over 200 tables to record your data. Some of the more complex reporting features of XenDirect are created outside of the XenDirect interface.

• Each table uses hidden primary keys that average user does not see. These fields will usually be the left most fields and the field names are often followed by "ID". An example is the Client Primary Key called "cliID".

• Because many users have more than one branch department using XenDirect, it is crucial to link tables not only by their primary key, but also by the Branch ID. Otherwise, data accuracy will be compromised.

• The Client table and the Staff table use a linked table called Client_Branch and Staff_Branch to record the branch associated with these two tables. This relationship allows the client or staff record to be shared among multiple branch units. 

• The speed a query runs is dependent on many factors including, but not limited to, the access speed of your location and computer, and the complexity of the query itself.
  

---