| inGOT
Notification #010009
HOWTO: Reporting
via MS-SQL inGOT Database
Information in this
article applies to:
- inGOT v3.x and
v4.x
- Microsoft SQL Database Logging
Overview
The inGOT system
comes bundled with a set of MS-Access reports. These reports can
be used even if inGOT is storing data in a MS-SQL database. However
some modification to the MS-Access database will be required. This
article explains how to modify the MS-Access inGOT database so it
can be used against an MS-SQL database.
ODBC Connection
To enable MS-Access
to access an MS-SQL database an OBDC connection must be established
from the local workstation to the MS-SQL server. These steps are
required to create an ODBC connection:
- Open the ODBC Data Source Administrator (via Control Panel/Administrative
Tools)
- Select the ‘System DSN’ tab
- Press the ‘Add’ button to create an new DSN
- Select the ‘SQL Server’ driver and press ‘Finish’
- Give the DSN a unique name
- Select your MS-SQL Server and press ‘Next’
- Select ‘SQL Server’ authentication, enter the SQL
UserID & Password and press ‘Next’
- Change the default database to the ‘inGOT’ database
and press ‘Next’
- Press ‘Finish’
- Test Data Source to ensure connectivity is working correctly.
You will now be able
to access the inGOT SQL database from within MS-Access via this
data source connection.
Database
Update
These steps are required
to update the MS-Access database to access the MS-SQL database:
- Open the inGOT.MDB
database in MS-Access
- Minimise the Report Console if it opens automatically
- From the Database window select the ‘Tables’ view
- Select ‘File/Get External Data/Link Tables’ from
the menu
- Change the ‘Files of Type’ drop-down box to ‘ODBC
Databases’
- Select the ‘Machine Data Sources’ tab
- Select the ODBC Data Source created above and press ‘OK’.
- From the Tables window select all tables and press ‘OK’
- Duplicate SQL tables, with a preceding ‘dbo_’ name
should now be available from the MS-Access Tables window.
- In turn, delete each corresponding MS-Access table and rename
the SQL linked table, removing the ‘dbo_’.
- NB: The table names must be identical to the previous MS-Access
table names
- DO NOT delete MS-Access tables that do not have a corresponding
MS-SQL linked table
- There should now be an identical set of tables in MS-Access
except that most of the tables are linked to the MS-SQL server database.
It should now be
possible to run any of the existing reports, from the Report Console.
The data in the reports will now be retrieved from the SQL database.
For further clarification
please contact the Liverton support team at .
|
