Use SQL 2000 table for a list box source

[Norman]

Hello again,

I have a SQL 2000 database that has two tables; tblSystems and
tblGroups. In the tblSystems, there is a field 'GroupAllowed' that
needs to be selected from an Access 2003 front end. The GroupAllowed
should be selected from the tblGroups table in SQL 2000.

When I try to make this change in Access, I get an error saying that I
can't make changes since the Access table is a linked table (to the
SQL 2000 tables). When I use the Lookup Wizard, I get to the end of
the wizard then a warning that says 'Microsoft Access can't save
property changes for linked tables' and asks if I want to save it
anyway. Then a warning that the wizard was unable to create the
relationship.

It seems this would be something that should be possible but I'm not
doing something correctly. Can someone point me towards what I should
try?

Thanks!
Norman

 

[Albert D. Kallal]

This should work.

#1) setup a linked table to tblsystems in ms-access.

Can you now open this table (in the access table view) to view and edit this
data?

You *must* get the above working.

#2) setup a linked table to tblGroups in ms-access

Can you now open this table (in the access table view) to view and edit this
data?

You *must* get the above working.

#3) If, and ONLY if you have the above two steps working, now create a form
with its data source based on tblSystems (our linked table).

create a combo box using the wizard, and choose tblGroups as it source.

The form should work.

 

[Norman]

This should work.

#1) setup a linked table to tblsystems in ms-access.

Can you now open this table (in the access table view) to view and edit this
data?

You *must* get the above working.

#2) setup a linked table to tblGroups in ms-access

Can you now open this table (in the access table view) to view and edit this
data?

You *must* get the above working.

#3) If, and ONLY if you have the above two steps working, now create a form
with its data source based on tblSystems (our linked table).

create a combo box using the wizard, and choose tblGroups as it source.

The form should work.

Thank you Albert, I'll work with this for a bit and see if I can get
it to work. I can get the two requirements to work (the links appear
to work OK).

Norman