|
Items to Note:
| 1. | If you are running Seradex 6.2 or later you do not have to setup a Linked Server. See Option C for setup details. |
| 2. | If you run SQL 2005, do not proceed with Option A. Go directly to Option C. |
Overview
A linked server is how SQL Server connects to external data sources that are not SQL Server databases. This section will show you how to link a Microsoft Access database to your SQL server. This access database will contain the links to the appropriate accounting system tables.
Setup Procedure
| 1. | Create a Blank Access Database called SQLLink.mdb (you may use a different name if you choose to). Place the database you created on the local drive of the SQL Server machine; the database MUST be local to the SQL Server. You are required to grant rights to the folder, so if you do not want users to have rights to the entire drive, place the database in a sub folder. This is important because you must create a share to this folder. |
IMPORTANT: If you are on Multi-Company you must create one Database per Company and each one must point to the appropriate Accounting data for that company. Keep all of the databases inside the same folder
| 2. | Now that you have the database in place, link in the Appropriate Accounting system tables. Note any ODBC connection you use must be setup on every machine that is going to run update accounts or it will not work. Refer to the appropriate section for your Accounting System starting at page 129 in this manual to see how to link tables to an access database. The specific section is shown in the chart in section 18.2. |
| 3. | Create a Share to this folder and decide which users require access to it. Note you do not have to share the actual folder the database is in; however, the lower the level you go in the structure the more you are exposing to your users. In the example below, additional folders can be created inside the AccountingShare folder and because the AccountingShare folder is shared, users who can access the AccountingShare folder can also access any folders contained within. |
| 4. | Now that you have the Access database in place and the folder created and shared you must now setup the Linked Server within SQL Server itself to point to it. The first step is to create a new Linked Server. Do this in enterprise manager, expand the trees until you get to this point illustrated below: |
| 5. | With the linked Servers option highlighted, right click and select New Linked Server from the menu, then fill in the fields as below. |
Notes:
| • | The Data Source is the Local Drive to the SQL Server. |
| • | For Multi Company Users, you must set one linked server up per Company each pointing to the proper Database you created in that server folder. Each one has to be named the same. Now the extra step you will need to do is to tell Seradex which linked server each company uses. To do that, open up the sxRuntime database for each company and open the databases table. Inside the databases table is a field called Linked Server Name. Fill in the name of the linked server you created for that company in this field. |
| 6. | If you are using Seradex version 6.0 or earlier, you will need to tell Seradex the true machine name of the SQL server, which may not be the name of the SQL Server itself. To do this go to the table DBVersion in your Seradex database and enter in the real machine name. Note this is not used in 6.1 or above. |
| 7. | Now that you have your share, you must now setup Seradex so update accounts understands the share and the real path of this share for the SQL Server. To do this open up your SQL Server within Enterprise manager, open your Seradex database and find the table called SharedDrives. |
The local Drive name is the actual path to the folder containing the access database as the SQL Server knows it. User workstations do not see this folder. The Shared Drive name is the share name you gave this folder in Step 3.
Update accounts uses all this information as follows: Since the user workstations cannot see the Local drive name, it must be converted so they can see it. This is where the share and the other steps come together. Here you resolve the Share you created with the path that share represents. Seradex builds the path this way: It first takes the Path in the linked server, then finds the Local Drive Name and replaces it with the share. Then the value entered in step 5 is used if it is setup.
For example, in this case my linked server database is located in D:\rfishsx\AccountingShare\Testing\, so the format becomes: \\ROBINF\AccountingShare\Testing\.
The value is taken from the SQLLINK, replaces the value in shared Drive and adds the server. Thus, users can access the database. If my share was called TestingShare the result would have been: \\ROBINF\TestingShare\Testing
| 8. | Now your users should be able to run update accounts. Remember, all users must belong to a group that has at least Domain rights to the drive/share and each machine must have the ODBC setup with the SAME name you used to link the tables to begin with |
|
