Populate Combo Box with Data from Another Database

[HumanJHawkins]

I have a database that shares some common metadata tags with other
databases. So, to insure consistancy, we put the shared data in it's
own database and have all the other DBs tie into it.

In an Access Data Project that it connected to my main database, I
would like to have a comobo box on a form that is populated with
options from this other database.

The query I am trying to use for source data is:
SELECT pkThing, vchThing FROM Metadata.dbo.Things WHERE (NOT
(vchThing = '<Not Applicable>'))

This query runs fine in Enterprise Manager, so I know permissions are
good. But my combo box comes up empty. Is there anything extra I need
to do to make a combo box work with a query to an external DB?

Thanks!

 

[Burnsie]

I have a database that shares some common metadata tags with other
databases. So, to insure consistancy, we put the shared data in it's
own database and have all the other DBs tie into it.

In an Access Data Project that it connected to my main database, I
would like to have a comobo box on a form that is populated with
options from this other database.

The query I am trying to use for source data is:
SELECT pkThing, vchThing FROM Metadata.dbo.Things WHERE (NOT
(vchThing = '<Not Applicable>'))

This query runs fine in Enterprise Manager, so I know permissions are
good. But my combo box comes up empty. Is there anything extra I need
to do to make a combo box work with a query to an external DB?

Thanks!

 

[Stuart McCall]

I have a database that shares some common metadata tags with other
databases. So, to insure consistancy, we put the shared data in it's
own database and have all the other DBs tie into it.

In an Access Data Project that it connected to my main database, I
would like to have a comobo box on a form that is populated with
options from this other database.

The query I am trying to use for source data is:
SELECT pkThing, vchThing FROM Metadata.dbo.Things WHERE (NOT
(vchThing = '<Not Applicable>'))

This query runs fine in Enterprise Manager, so I know permissions are
good. But my combo box comes up empty. Is there anything extra I need
to do to make a combo box work with a query to an external DB?

Thanks!

You need to use the IN clause to specify the database file:

SELECT pkThing, vchThing FROM Metadata.dbo.Things
IN <PathToFile>
WHERE (NOT (vchThing = '<Not Applicable>'))

Replace the obvious with the actual path.

 

[HumanJHawkins]

You need to use the IN clause to specify the database file:

SELECT pkThing, vchThing FROM Metadata.dbo.Things
 IN <PathToFile>
 WHERE (NOT (vchThing = '<Not Applicable>'))

Replace the obvious with the actual path.- Hide quoted text -

- Show quoted text -

I should have explicitely stated, it's a SQL database. I don't have
access to the physical file. But the database is deployed on the same
SQL server. Do I really need to get access to the physical file? Or,
did I just not qualify the other DB name correctly for a combo box?

To clarify, the question is: In an .adp file connected to a SQL DB, if
the .adp project is connected to DB_One how do you populate a combo
box with data from a table in DB_Two?

Thanks!

 

[Stuart McCall]

I should have explicitely stated, it's a SQL database. I don't have
access to the physical file. But the database is deployed on the same
SQL server. Do I really need to get access to the physical file? Or,
did I just not qualify the other DB name correctly for a combo box?

To clarify, the question is: In an .adp file connected to a SQL DB, if
the .adp project is connected to DB_One how do you populate a combo
box with data from a table in DB_Two?

Thanks!

The fault was all mine. The clues were there but I didn't spot them.

I've never done an adp, so I'll not be of much help. Sorry. Perhaps someone
else will jump in...

 

[HumanJHawkins]

Solved... This wasn't so mush an issue to solve, as it was a goof to
find... Nothing was wrong with the query or the combo box... I had
simply mixed up the "Control Source" and the "Row Source" . The query
in my OP works fine if placed in the right spot.

D'oh!