Reading and updating an Sql Server 2005 database from Access 2007

[Oriane]

Hi there,

I'm a newbie in Access. I would like to use Access 2007 to connect to a sql
server 2005 database to view and update data in the sql tables. I'm
currently using OBDC to link my "*.accdb" project to the Sql Server 2005
database. The problem is that I have (of course) foreign keys (and therefore
relations) in the sql database and that I can't see these relations in
Access.

My goal at first was to have a "nice" way to fill in the foreign data (I
imagine a sort of listbox filled in with all the primary keys available in
the database). Perhaps I'm wrong and Access can't do that for me.

Anyway, I wonder if the "adp" project is the solution or if there is another
way to proceed ?

Oriane

 

[Tom van Stiphout]

On Fri, 23 Nov 2007 10:27:21 +0100, "Oriane" <[email protected]>
wrote:

Whether you use MDB, ACCDB, or ADP, you will have to program that
support yourself. Typically by providing a dropdown list with foreign
key values (ID and Description).

-Tom.

 

[Sylvain Lafontaine]

Unlike Access, SQL-Server won't show you all the relationships between the
tables; you must build one or more diagrams yourself. You can use SSMS or
even an Access 2007 ADP project to create/see these diagrams. For ADP 2007,
go to the Database Diagram, create a new diagram and add the tables that you
want on it. Access will automatically add all the references that exists
between these tables. By dragging/dropping a foreign key, you can also add
a new relationship.

Notice that with ODBC linked tables, the fact that you don't see any diagram
on the server doesn't mean that you cannot make a JOIN between two tables in
your Select/Update/Insert/Delete statement.

Finally, if you're a newbie, then I must warn you that creating an ADP
project is more difficult then simply using ODBC linked tables.

 

[Oriane]

Hi Sylvain,

Unlike Access, SQL-Server won't show you all the relationships between the
tables; you must build one or more diagrams yourself. You can use SSMS or
even an Access 2007 ADP project to create/see these diagrams. For ADP
2007, go to the Database Diagram, create a new diagram and add the tables
that you want on it.

I can't see this item.. The "Database tools" panel is greyed out.

Thank you

 

[Tom van Stiphout]

I had the same experience, so I'm using the database diagrams in SQL
Server Management Studio.

-Tom.