LINKING TO AN SQL SERVER 2005 DB THROUGH ACCESS 2007

[bifteki via AccessMonster.com]

I am new in Access 2007 (and Office 2007 in general) and the environment
seems too strange for me.
I want to link to an SQL Server 2005 DB but I don't know how.
I opened the External Data tab -->Import -->more -->ODBC database, I followed
the steps but the resulting db was a number of tables, some of them the
original tables, some of them the queries in the original db and about 75% of
them were tables whose names started with INFORMATION_SCHEMA_ or sys_.
Also I've noticed there are no dependencies. Can someone help me see the data
as in the original db?

 

[AkAlan via AccessMonster.com]

When you created your dsn you must specify the default database. Its the page
after you select the server.

 

[bifteki via AccessMonster.com]

I have selected the default db. It's the db I aqcuire the data from.
The tables exist in the resulting Access DB but they aren't related to each
other. Also the queries are shown as tables. And there are a lot of other
tables that -as far as I can tell- contain information about the database,
information however that shouldn't be in the form of a table.
Is this the way data should be imported? Because in the first step it says
that I am going to Import the source data into a new table in the current
database. Does that mean that all information concerning the db is going to
be imported as tables?

When you created your dsn you must specify the default database. Its the page
after you select the server.

I am new in Access 2007 (and Office 2007 in general) and the environment
seems too strange for me.

[quoted text clipped - 5 lines]

Also I've noticed there are no dependencies. Can someone help me see the data
as in the original db?

 

[bifteki via AccessMonster.com]

Alsto do I have to attach a database filename after I select the default db?
(If so, please explain what db filename I should attach because I don't quite
understand its significance)

 

[AkAlan via AccessMonster.com]

Are you importing or linking. If you select import then the tables will
reside on your database and they won't be shared with other users. When you
select "Link", the tables stay on the SQL server and your mdb just points to
them. That way as you distribute the mdb all users are seeing and updating
the same data. When you see the list of tables from the server just deselect
the system tables. Queries on the server will show as tables, you should have
a good naming convention so you can differentiate while developing. You won't
see the stored procedures or user defined functions. You need to use another
product for them like Enterprise Manager or I think Visual Studio will work.
As far as selecting a database other than the default, I'm not sure about
that cause I only select the default. If I need to link to more than one
database I create another DSN but not sure if that is the best way to do
things.

Another option to think about is making an Access Project. Then you simply
connect to the database in the SQL Server and don't need a dsn. There are
pros and cons to both and if you do a search of this site you will see plenty
of discussion about it. Hope this helps.

 

[bifteki via AccessMonster.com]

The naming I use for the views makes them easy to tell from the tables. But
my main problem is the relationships between the tables. Will I have to
define the relationships between them from scratch?

Are you importing or linking. If you select import then the tables will
reside on your database and they won't be shared with other users. When you
select "Link", the tables stay on the SQL server and your mdb just points to
them. That way as you distribute the mdb all users are seeing and updating
the same data. When you see the list of tables from the server just deselect
the system tables. Queries on the server will show as tables, you should have
a good naming convention so you can differentiate while developing. You won't
see the stored procedures or user defined functions. You need to use another
product for them like Enterprise Manager or I think Visual Studio will work.
As far as selecting a database other than the default, I'm not sure about
that cause I only select the default. If I need to link to more than one
database I create another DSN but not sure if that is the best way to do
things.

Another option to think about is making an Access Project. Then you simply
connect to the database in the SQL Server and don't need a dsn. There are
pros and cons to both and if you do a search of this site you will see plenty
of discussion about it. Hope this helps.

I am new in Access 2007 (and Office 2007 in general) and the environment
seems too strange for me.

[quoted text clipped - 5 lines]

Also I've noticed there are no dependencies. Can someone help me see the data
as in the original db?

 

[AkAlan via AccessMonster.com]

No you don't have to change any relationships, they reside on SQL Server not
in access project. To see them you have to create a new diagram. On the
ribbon click the Create tab and under the far right tab (Other) click the
drop down arrow for more options if you don't see diagrams and select
diagrams. You will have to list the tables you want to see relationships for
but they will still be there if you created them in SQL Server. You can
manage the relationships from the project as well as from Enterprise Manager.

The naming I use for the views makes them easy to tell from the tables. But
my main problem is the relationships between the tables. Will I have to
define the relationships between them from scratch?

Are you importing or linking. If you select import then the tables will
reside on your database and they won't be shared with other users. When you

[quoted text clipped - 20 lines]

 

[bifteki via AccessMonster.com]

In the place you told me to search there is no such thing as Diagrams as an
option. I went to Database tools -->Show/Hide and in neither Relationships or
Object Dependencies can I see any relationship between the tables.
However, I just saw that even in the SQL Server Management Studio I am not
able to see the Database Diagrams, a message appearing telling me that this
database does not have a valid owner. I guess that's why they weren't
imported to Access. Do you know where I could ask for help about this?
(because I think now the problem is a little irrelevant to this forum)

No you don't have to change any relationships, they reside on SQL Server not
in access project. To see them you have to create a new diagram. On the
ribbon click the Create tab and under the far right tab (Other) click the
drop down arrow for more options if you don't see diagrams and select
diagrams. You will have to list the tables you want to see relationships for
but they will still be there if you created them in SQL Server. You can
manage the relationships from the project as well as from Enterprise Manager.

The naming I use for the views makes them easy to tell from the tables. But
my main problem is the relationships between the tables. Will I have to

[quoted text clipped - 5 lines]