Can't connect Access 2010 to SQL Server 2008 R2

[LAazsx]

Hi, I've made an adp file using Access 2010. I can't seem to connect it to SQL Server. I've entered the server name and user name fields properly but i keep on getting the error


"{DBNETLIB][ConectionOpen (Connect()).]SQL Server does not exist or access denied.". I tried using the PC name for server name and windows authentication just like how i login into SQL server. Any thoughts?

[ssanfu]

Haven't ever created an adp file, but maybe one of these links will help:

http://www.connectionstrings.com/sql-server-2008


http://www.sqlstrings.com/SQL-Server...on-strings.htm

[pkstormy]

ADP in 2010? Yuch. I thought MS was ditching ADP support starting with MSAccess 2007. I'd personally consider just designing an accdb, create an ODBC DSN, and then link the SQL Server tables into the accdb frontend. I do this all the time without any problems and working with an accdb is much easier than an adp.

You really don't gain a lot of benefits with an adp versus an accdb using linked tables. It's much easier working with an accdb versus an adp. I gave up on adp's back in the year 2000 since they were more hassle than anything.

Otherwise have you tried using the IP address versus the name?

Also keep in mind that MSAccess 2010 still hasn't worked out all their bugs. I've had problems with 2010 and reverted all our users back to 2007. SQL Server 2008 though is nice.

[LAazsx]

ADP in 2010? Yuch. I thought MS was ditching ADP support starting with MSAccess 2007. I'd personally consider just designing an accdb, create an ODBC DSN, and then link the SQL Server tables into the accdb frontend. I do this all the time without any problems and working with an accdb is much easier than an adp.

You really don't gain a lot of benefits with an adp versus an accdb using linked tables. It's much easier working with an accdb versus an adp. I gave up on adp's back in the year 2000 since they were more hassle than anything.

Otherwise have you tried using the IP address versus the name?

Also keep in mind that MSAccess 2010 still hasn't worked out all their bugs. I've had problems with 2010 and reverted all our users back to 2007. SQL Server 2008 though is nice.

Oh. I thought adp is the required format for that. Thanks.

[pkstormy]

ADP is different than a normal accdb type file. ADP has a direct connection to the backend SQL Server tables whereas an accdb uses linked SQL Server tables.

With an ADP, you can modify the tables directly within the ADP project instead of opening SQL Server. I'd personally still use SQL Server to do any table modfications (and again, avoid using an ADP alltogether.)

An ADP typically requires 2-3 times to develop versus an accdb (or mdb). Mostly because everything has to be coded. In an accdb/mdb, you can take advantage of some of MSAccess's features to make thing easier and quicker for designing.

I remember the first time I was training other developers on ADP. They deleted all the tables in the ADP (thinking they were the same as linked tables) and didn't realize that they had actually just deleted all the tables on SQL Server itself!

[Mgomp]

I'd personally consider just designing an accdb, create an ODBC DSN, and then link the SQL Server tables into the accdb frontend. I do this all the time without any problems and working with an accdb is much easier than an adp.

Hello!
I have the same problem using adp with 2010.
After the new "web-lookalike" form and menu system, it is not getting any better...

But, Could you please - step by step - tell me how you use an ordinary accdb as a front end to an sql? I have MS sql server express installed on my comp for testing.

What I need to know is how you deal with all queries which (at least by the book....) should stay on the sql-server. And what type of connection you are useing. ...and other tips...

Regards.

[pkstormy]

If you're using SQL Server Express, (I think but I'm not 100% sure) you can't distribute your MSAccess application for others to use since (again, I think) SQL Server Express doesn't support connections outside the computer it's on.

But to set up a SQL Server linked MSAccess file:

1. Create your SQL Server tables/fields/db.
2. Create an ODBC DSN connection on the computer(s) (ODBC Administrator in Control Panel) you're going to have the application running on - (Important Note: Every ODBC DSN connecting to the SQL Server db MUST have the EXACT same ODBC DSN name - this is important.) I personally use code (in the code repository section on this site) to put code in my MSAccess application to automatically create the ODBC DSN when the MSAccess app is opened.
3. Open MSAccess and link the tables (just as you would linking in another MSAccess table.) The difference is that you'll select ODBC Databases (ie. File -> Get External Data -> Link Tables, and then select ODBC Databases as the driver. You should then see the ODBC Manager popup to select the SQL Server db.)
4. Continue designing your MSAccess application as normal. The SQL Server linked tables act like any other linked tables. Design your queries in MSAccess based on the linked SQL Server tables. You don't need to write Stored Procedures on SQL Server unless you want to learn a whole new process. Normal queries in MSAccess work just fine against the linked tables. I do this all the time without any issues.