Lookup fields and upsizing to SQL Server

[KarenH]

I have a large database that is going to be upsized to SQL Server. The
tables were designed using a lot of lookup fields containing links to other
tables or named queries, and they are multicolumn, so that in the table, what
will be displayed will be for example a region name rather than an ID. I
can't change these, these were done this way before me, and it would mess up
the way a lot of the forms display data (tried it on one, really messed
things up). Plus, although they shouldn't sometimes users can go in and
enter data directly into the Access tables.

The database is now going to be upsized to SQLServer, which does not
recognize lookup fields. Is it going to hurt anything to alter the link that
is in Access and add back in the lookup fields so that it still performs the
same way? The user won't be entering data into the SQL Server database
anyway.

 

[Vincent Johns]

I have a large database that is going to be upsized to SQL Server. The
tables were designed using a lot of lookup fields containing links to other
tables or named queries, and they are multicolumn, so that in the table, what
will be displayed will be for example a region name rather than an ID. I
can't change these, these were done this way before me, and it would mess up
the way a lot of the forms display data (tried it on one, really messed
things up).

I hope you're working using a COPY of the live database! Upgrading a
copy won't affect what the current users are seeing and doing. At some
point, you will have constructed the equivalent SQL Server version, and
I expect that you will import data from the Access version so you can
run some tests on the new version. After you're satisfied that it works
well, you can kick all the users off Access, copy the (now static)
contents to SQL Server, back up the SQL Server version, and then turn
the users loose on SQL Server. They can keep using all the current
features of Access until they have to switch, but I expect that well
before then they will have played with the SQL Server version enough to
be comfortable with it, and that you will have had time to provide all
the features they need.

Plus, although they shouldn't sometimes users can go in and
enter data directly into the Access tables.

I hope you make frequent backups.

The database is now going to be upsized to SQLServer, which does not
recognize lookup fields. Is it going to hurt anything to alter the link that
is in Access and add back in the lookup fields so that it still performs the
same way? The user won't be entering data into the SQL Server database
anyway.

AFAIAA, Access lookup fields are just for looks. When I design a Table,
I attach a lookup property just about every foreign-key field, to hide
the raw values of the key in Table Datasheet View. If that's what your
lookup fields are used for, all you need do is to write Queries in SQL
Server that display the same stuff that your Access Tables (with
lookups) do now. Your users will see the same display, it will just be
the result of running a Query instead of displaying a Table.

Not being familiar with data entry techniques in SQL Server, my
suggestion there would be to provide data-entry forms to support all
needed types of data-entry transactions. (It'd probably be a safer
procedure than what's happening now, anyway.) You might want to
determine what's missing from your Access version that inspires users to
update the Tables directly. Perhaps another Access Form or two is
called for, although you don't want to invest too much effort in an
obsolescent version.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

I have a large database that is going to be upsized to SQL Server. The
tables were designed using a lot of lookup fields containing links to other
tables or named queries, and they are multicolumn, so that in the table, what
will be displayed will be for example a region name rather than an ID. I
can't change these, these were done this way before me, and it would mess up
the way a lot of the forms display data (tried it on one, really messed
things up). Plus, although they shouldn't sometimes users can go in and
enter data directly into the Access tables.

Lookup fields are NEVER necessary.

You can put a Combo Box (a "lookup") on a Form, with or without the
table lookup field. The ONLY benefits that the Lookup Fields provide
are allowing the (bad practice of) direct use of table datasheets, and
making it about two mouseclicks easier to put a combo box on a new
form.

You're not losing ANYTHING of value when you lose the table lookups.

John W. Vinson[MVP]