How to Use SQL Server 2008 Database as Back-End for Access 2007 Database

[eneyardi]

I want to use the sql 2008 server database as a database of access 2007?
How can i do this thing? any suggestions are highly appreciated.

[NeoPa]

In Access there is an option to select a SQL Server database when linking tables.

This is not a SQL Server question but an Access one so I'll move it across to the correct forum for you.

[eneyardi]

thanks Neopa, i thought that option linking access database to sql, not sql database to access. correct me if i'm wrong

[NeoPa]

As what you say doesn't quite make it clear what it is you think you're saying, I will simply state the situation as I understand it and you can tell me if that confirms or contradicts what you thought you were saying :

1. Access can have a virtual (Linked) table which is simply a link through to a SQL Server table.
2. I don't believe there has ever been a facility in SQL Server that allows the revers (A link through to an Access table).
3. There is a facility in SQL Server (Data Transformation Services or DTS) that enables it to manipulate data from an Access database and even to get data imported from there into its own databases.

This is my understanding from SQL Server 2000. I may be wrong on this side of things and hopefully someone will correct me if I am, but I'm pretty sure that's how things are.

[AR Ratheesh]

You can use your linking tables as local table,Everythin g will be fine such as access table.

I don't know is it possible to link Access 2007 to SQL Server 2008 due to higher version than access. I think it can be linked to SQL Server 2005 or upgrade Access 2007 to 2010.

AR Ratheesh

[patjones]

You can in fact link Access 2007 to SQL Server 2008, as I am currently doing so with two of my projects at work.

On the ribbon go to External Data > More > ODBC Database and follow the steps to link to SQL tables. You'll need a DSN file in order to do this.

Pat

[Rabbit]

i thought that option linking access database to sql, not sql database to access.

What's the difference?

[patjones]

The distinction is that if you have a table in SQL Server then one can link to it from Access; but we're saying that if you have a table in Access then one cannot connect to it from SQL Server. I however have never tried to do the latter. When I have data in Access that I want to put in SQL Server, I CREATE the table in SQL Server, link to it in Access, then do an INSERT from the local table to the linked table.

Perhaps there's a wizard somewhere in SQL Server that makes this process easier? I can't say as I have not even a year of experience with SQL Server Management Studio.

Pat

[NeoPa]

Rabbit:
What's the difference?

Post #4 has the two different meanings laid out in points #1 & #2.

[AR Ratheesh]

Yes,
we have easier tool to migrate SQL Server.

Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool to automate migration from Microsoft Access to SQL Server.

Follow this link for more details.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26709

I tried this and very useful one.

AR Ratheesh.

[eneyardi]

Thank you guys for your help.. I know now how to do it..