Hi Bob,

> We're trying to get ODBCSE/32 to get along with Microsoft Access 97.
>
> Setting up took about four hours, which included deciphering both the
> Image/SQL and ODBC administration manuals.

hat's why we're here ;-)   Seriously though - it's one of the things we at
CSI do - training in the client-server arena and ODBC...  but anyway - off
the plugs for a moment...

> Now, we're trying to get it to actually do something useful (silly us).
>
> It sucked us in by letting us update a character field with data, but now
> when we try to erase the data, we get the error "You tried to assign the
> Null value to a variable that isn't a Variant data type." from Access.
> Obviously, TurboImage isn't going to let us modify the database structure
> through Access, so does anyone have any suggestions on where to go from
> here?
>
> It also appears to display each record twice, or at least I have
> two copies
> of each record being displayed in Access, which may or may not be the same
> thing.

First thing about updates, if you already don't know... there must be a
unique key.  Masters will always produce and report a unique key to
ODBCLink/SE.  Details will never report a unique key.  This is because of
their inherent properties.

To create a unique key, link the tables to the Access database by using the
File/Get External Data command.  Ensure the Data Source is defined and
correct, and then select ODBC Data Source in the "type" field of the
resulting "open" dialog.

When the tables (datasets) appear, select those you are using.  You will
automatically be prompted to provide a unique key.  Select each field in
that dataset/table which comprise a unique key.  These fields need not be
contiguious.

If you have a dataset which does not have any combinations which can result
in a unique key, then the only way to handle that is to add some type of
field to make it so - such as a sequence number.  ODBC and SQL does not have
access to the Record Number (Image Record Number, that is).

Once a unique key can be identified, then the record can be updated with
ease with either an UPDATE query, or using the JET Edit and Update methods.

The reason for this is not an ODBCLink/SE issue - but a Microsoft JET
Database Engine issue.  JET requires a unique key, and since Access is based
on JET, all linked tables also require this.

As for handling NULL data - IMAGE cannot handle it, and fields cannot
contain a null value.  It is best to ensure that spaces are moved to these
fields.  The easiest way to do this is to pull the entire row, update only
the fields needing update, then UPDATE the row.  This way spaces stay there.

If INSERTing - ensure that spaces are moved to the field.  SQL will default
to NULL.  JET allows one to set a property of No Nulls - but linked tables
cannot have their structures updated.

As for the duplicates - lower powered CPUs and/or PCs with low memory do
have this problem with Access and Linked Tables.  You may try this on a PC
which is at least a Pentium 120 or better, with at least 16MB memory
(Windows 95) or Pentium 166 or better with at least 32MB of memory (prefer
64 for NT, though).  It could very well be a display problem.  Every time I
encountered this, it was a display/refresh problem, cured by a higher
powered machine.  (There's a lot going on in there, not just Access).

Lastly, ensure that you have a good amount of available disk space.  JET
uses disk space for caching, and it's done in the TEMP directory (as
specified in the SET TEMP variable in AUTOEXEC.BAT, or \WINDOWS\TEMP if none
specified).  Also cached there are transactions to be committed.

I hope this novella helps you.

Best Regards,
Joe

Joe Geiser
CSI Business Solutions, LLC
 ** Your Client-Server and Internetworking Specialists **
140 Bristol-Oxford Valley Road, Suite 102
Langhorne, PA 19047-3083
Phone: +1 (215) 945.8100  Fax: +1 (215) 943.8408
Toll-Free (US/Canada): (800) 498-4802


>
> Any help would be greatly appreciated.
>
> Bob "There has to be an easier way to make a living" Graham
>