Connect Access 2007 to SQL Server 2008 Database

Question

Hi, I've seen numerous answers to similar questions like this one. I haven't seen on the web many people have asked the seemingly simple question "How do I connect Access 2007 to an SQL server 2008 database" - but all of the answers describe how you can migrate from access 2007 to an sql server 2008 database, or they describe how to connect access 2007 to an sql server 2005 database. I can't find any simple solution to my problem (and probably this is a problem for many others). Here is the question (sorry for the over emphasis):

How do I connect to an sql server 2008 database (and I mean 2008, not 2005 :) ) from access 2007? Apologies again for the over emphasis, but this very simple question, and what I thought should be a very simple task seems, yes, ... impossible! I tried running sql server browser, enabling pipes, TCP etc, but it seems that with 2008 SQLEXPRESS just isn't recognised! Please can someone help with this. Peter

Answer 1

One solution (though this may not apply to SQL Express) is to use an Access Data Project, although in 2007 it's not that intuitive how to do this.

Create a new (blank) DB, but rather than accept the default .accdb extension, change it to .adp. You can also select Access Data Project (.adp) format if you use the browse dialog to set the file location.

Once created, you will be asked if you want to link to an SQL DB... the rest is pretty intuitive, but ask for further details if you need to.

Answer 2

There should be no issue with connecting Access 2007 to a SQL Server 2008 database.

You need to make sure that:

1. Your SQL Server 2008 database is accessible, ie that it isn't locked down and that it is accessible to the machine(s) where you will have your Access 2007 application.
   A few things to check:

   • In SQL Server 2008, go to Properties > Connections > Check "Allow remote connections to this server".
   • Enable TCP/IP in the Configuration Manager.
   • Make sure the firewall allows incoming connections on TCP port 1433.
   • You can also start the SQL Server Browser Service so your SQL Server instance can be found.

2. You have created an ODBC DSN (a System DSN) using Windows ODBC administration tool. If you're running on a 64 bit system, make sure that you're using the 32 bit version of ODBC to create your DSN, otherwise it will never be visible to Access which is a 32 bits application.

3. Once you have created the ODBC link (and tested it works) on the machine where Access is installed, you can just link the tables: In Access 2007, in the External Data ribbon tab > import > More > ODBC Database.
   Then select the DSN you create for your SQL Server 2008 database and chose which tables you want to link.

Answer 3

Guys - you really should check whether this works before firing off random answers. It takes all of about a minute to run up an Access project and discover that it doesn't actually connect to SQL 2008 easily.

There are plenty of reasons to use projects instead of ODBC, but here's a few:

• Existence of DSN on deployment machines cannot be guaranteed
• Network traffic - does not do pass-through queries by default
• Poor support for stored procedures

Assuming that appropriate protocols have been enabled, the solution is to go to the Advanced tab of the data link dialog and delete the value in the Network Library setting.

Answer 4

I still have the same question, and I still don't see a path to an answer. I could not get to step 1 "•In SQL Server 2008, go to Properties > Connections > Check "Allow remote connections to this server". I am using visual web developer express with SQL Server Express. The only way that I have been able to be "in SQL Server 2008" is in web developer, and I cannot find andy connections property. How do you get "in SQL Server 2008"?

Answer 5

download the SQL Server Management Console.

Answer 6

Have a problem with this too. I have created a new database in SQL Server 2008 express. Then to create a dsn I connect to (local)\SQLExpress, but then the Default Database dropdown only shows master, model, msdb and tempdb. I can't see the database that I created. There seems to be something happening with user or login permissions - any clues?

Answer 7

Ray, The reason you are not seeing your database is because of permissions. You need to grant the user name you are using access to your database. In MS SQL Server Management Studio expand the database you created and go to the security tab to configure the user name you are using. Either add it or create a new user

Hope this will help....