Using Access as a frontend to a sql server 2000 DB

Yes, if the "lookup table" is available as a linked or local table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


JD wrote:
 > I am trying to set up Access as a frontend to a sql server 2000 database,
 > what I have done is converted an access database to sql server, and I am
 > trying to set up the front end so that the client can still update the
 > database the way they use to do. However, I am running into a wee problem,
 > is it possible to set up lookup columns and datasheets within the tables in
 > the access front end?
 >> Stay informed about: Using Access as a frontend to a sql server 2000 DB 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


1) Open a table in design view in the Access front end.
2) Click on the column name you want to put a "lookup" on.
3) In the Column Properties (lower part of screen) click on the "Lookup"
tab.
4) Set the Display Control property to ComboBox.
5) Set the Row Source Type property to Table/Query.
6) Set the Row Source property to a query name or an SQL string.
7) Set the Bound Column property to the column number that will be the
column's (the column you selected in 2, above) saved value when the row
is saved.


Say I had 2 tables, 1 a lookup table, the other the table that
references the lookup table:

CREATE TABLE t1 ( -- lookup table
code INTEGER IDENTITY(1,1),
descrip VARCHAR(25) PRIMARY KEY
)

CREATE TABLE t2 (
emp_id INTEGER NOT NULL,
t1_code INTEGER NOT NULL REFERENCES t1 (code)
)

In table t2, t1_code column I'd have the following Lookup properties:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT code, descrip FROM t1 ORDER BY descrip
Bound Column: 1 -- code column
Column Count: 2 -- code & descrip columns
Column Widths: 0;1.5 -- don't show code, show descrip in 1.5"

This set up will create a Combo Box on the table t2.t1_code column that
will show the descrip[tions] that go w/ the code. IOW, this

t2:
emp_id t1_code
====== =======
1 marshmallows
2 some-mores
3 chocolate

instead of this:

emp_id t1_code
====== =======
1 25
2 30
3 12

You can consider the Row Source property having the ability to "see"
only tables that are in the Table Definition's scope. Since that Table
Definition is in the Access file, the Row Source can only refer to (use)
tables/queries that are in (linked to) the Access file.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgqOgoechKqOuFEgEQIaowCghNezA/uvS9IwY8fDJmDb47TTgYcAnirJ
vXfXi339gGvWOuBdiuvuMnW3
=j3/j
-----END PGP SIGNATURE-----

JD wrote:
 > how do you do that? I have not had too much success with it so far.
 >
 > I thought I had the solution when I created an access project, but even
 > though that lets me add the subdatasheet, I can't add anything to it and I
 > don't see any way to add in a lookup column.
 >
 > So any help would be appreciated on this.
 >


 >
  >>Yes, if the "lookup table" is available as a linked or local table.
  >>
  >>--
  >>MGFoster:::mgf00 <at> earthlink <decimal-point> net
  >>Oakland, CA (USA)
  >>
  >>
  >>JD wrote:
  >>
   >>>I am trying to set up Access as a frontend to a sql server 2000 database,
   >>>what I have done is converted an access database to sql server, and I am
   >>>trying to set up the front end so that the client can still update the
   >>>database the way they use to do. However, I am running into a wee
   >>>problem, is it possible to set up lookup columns and datasheets within
   >>>the tables in the access front end?
 >
 >
 >
 >> Stay informed about: Using Access as a frontend to a sql server 2000 DB