ODBC and Visual Integrator often appear in the same Sage 100 conversation because both can be involved when data needs to move between Sage and another application.
But they are not the same thing, and treating them as interchangeable can make an integration harder to understand than it needs to be.
That distinction is the starting point for understanding how the two fit together.
What ODBC Does
ODBC stands for Open Database Connectivity.
Sage describes ODBC as an industry standard developed to help business applications and databases work together.
At a high level, ODBC gives compatible software a common way to connect to and work with data sources.
That can be useful when Sage 100 needs to exchange information with another application that also supports ODBC.
What Visual Integrator Adds
Visual Integrator, or VI, is the Sage 100 tool designed to facilitate integration between Sage 100 data and other business applications.
VI can import from or export to an ODBC-compliant source without requiring an intermediary file such as an ASCII-delimited file or Microsoft Excel workbook.
That means an ODBC connection can become the source or destination for a Visual Integrator job.
Visual Integrator then adds the Sage-oriented pieces around that connection:
- Field selection
- Import and export definitions
- Validation logic
- Selection and skip criteria
- Conditional assignment
- Testing and preview
- Scheduling
- Job chaining
- Additional scripts and subroutines
That is why the two technologies are related but not interchangeable.
Visual Integrator Can Link Multiple ODBC Tables
Sage documentation describes Visual Integrator’s ODBC connectivity as being able to link multiple ODBC tables together.
Users can point and click to select fields for importing, and Visual Integrator can generate the SQL statement used for the import or export.
For advanced users, Sage also documents the ability to edit the generated SQL statement.
That can make VI useful when the source data is spread across more than one related table and needs to be assembled before it is brought into Sage.
The Connection Is Only the Beginning
A successful integration is not defined only by whether two systems can connect.
The data still has to make sense when it reaches Sage 100.
That is where Visual Integrator’s validation and mapping features become important.
Sage states that validation logic is built into Visual Integrator to help ensure imported data conforms to Sage 100 rules.
The validations used by a job are listed automatically, and the job can also be configured with additional validations for specialized requirements.
Sage gives an example of preventing the import of future-dated invoices.
Conditional Logic Can Change Values During Import
Visual Integrator also supports conditional importing.
That allows one value to be assigned based on the value of another field.
Sage gives the example of assigning a sales tax code based on the state in a customer address.
This is useful because external systems do not always organize information in exactly the same way Sage 100 does.
A VI job can provide some of the translation logic needed between the source and the Sage environment.
Selection and Skip Criteria Help Control What Moves
Not every record in a connected data source necessarily belongs in the import.
Visual Integrator supports multiple selection criteria for choosing records and multiple skip criteria for excluding them.
Sage describes the use of standard nesting logic, including and and or conditions.
That gives the job more control over which records are actually processed.
User-Defined Fields Can Participate Too
If user-defined fields have been created in Sage 100, Sage documents that those fields appear automatically for selection when Visual Integrator operates on the entity to which they are attached.
For example, a user-defined field attached to customers can become available to a customer-related VI job.
That matters for customized Sage environments because the data an organization depends on may not live only in standard Sage fields.
Testing the Data Before the Full Import
Visual Integrator includes a Test button that provides a visual check of data to be imported.
Sage also describes test and preview functionality that can sample data and display or print import log statistics.
That is especially valuable when the source is an ODBC connection because the job may be pulling information directly from another live data source rather than from a static file someone can inspect first.
Testing gives the person building the job a chance to confirm that the selected data, mappings, and rules are behaving as intended.
Jobs Can Require Passwords
Sage documents the ability to assign a password to each Visual Integrator import or export job.
The password is then required before the job can be run.
This is a job-level control within Visual Integrator and can help prevent a defined integration process from being run casually or accidentally.
ODBC Does Not Mean the Job Has to Be Manual
Once a Visual Integrator job has been defined, it can be scheduled.
Sage documents several scheduling methods:
- Running a job manually from an Integrator job menu
- Running a job through Windows Scheduler
- Running a job from an external Windows program or script
- Running a scheduled cycle within Visual Integrator
Sage gives examples of cycles that run once per day, once per hour, or once per minute.
That makes ODBC plus VI useful for recurring integrations where the same data movement needs to happen on a predictable schedule.
Jobs Can Run in Sequence
Visual Integrator also supports job chaining.
A job can be configured to run after another job completes.
Sage gives the example of importing invoices immediately after a customer import.
That sequencing is important when one set of records depends on another.
It lets the integration reflect the order in which the business data actually has to exist.
Visual Integrator Can Trigger Additional Processes
Sage documents the ability to run additional commands, scripts, or other functions based on Visual Integrator events.
Those events can include:
- Before a job starts
- When a job succeeds
- When a job fails
- Before or after individual record writes
- Before or after field assignments
This makes it possible for a VI job to participate in a broader automated process rather than operating as an isolated import.
ODBC Can Also Be an Export Destination
Visual Integrator can move data in both directions.
Sage states that data from Sage 100 files can be exported to ODBC databases or ASCII-delimited files using the same kinds of logic and selection criteria available to import jobs.
That can be useful when another application needs a controlled subset of Sage 100 data.
When Does ODBC Plus Visual Integrator Make Sense?
The combination is especially useful when:
- Another business application exposes an ODBC-compliant data source
- The data movement needs to happen repeatedly
- The records need selection, validation, or conditional logic before entering Sage
- Multiple data sources or tables need to participate in the process
- The integration needs scheduling or sequencing
- The process needs to export Sage data to another ODBC-compliant destination
The exact design still depends on the source application, the Sage 100 module involved, the data being moved, and the business process surrounding it.
What This Does Not Mean
It is important not to reduce the conversation to “ODBC means we can connect directly, so the rest does not matter.”
Sage’s own Visual Integrator documentation puts considerable emphasis on validation, selection logic, testing, job controls, and sequencing.
That tells us something important about integration design:
ODBC addresses connectivity.
Visual Integrator can provide the controlled Sage 100 workflow around that connectivity.
Start With the Business Process
Before deciding how an integration should be built, the useful questions are usually practical ones:
- What information needs to move?
- Where does it come from?
- Where does it need to go?
- How often does the process happen?
- Which records belong in the process?
- What rules have to be satisfied?
- Does another import have to happen first?
- How will the result be tested?
Once those answers are clear, the technology becomes much easier to reason about.
The Simple Version
ODBC and Visual Integrator work well together because they solve different parts of the integration problem.
ODBC provides a standard way for compatible systems to connect to data.
Visual Integrator gives Sage 100 a structured way to select, validate, transform, schedule, sequence, import, and export that data.
Used together, they can turn a recurring data exchange into a defined Sage 100 process instead of a collection of manual steps.