Access 2007 and ADP to SQL Server 2005 in Vista

  • Thread starter Thread starter Tekton
  • Start date Start date
T

Tekton

Hi group, I must miss something obvious but I cannot manage to create an ADP
and connect to SQL Server using Access 2007 and Vista. My desktop workstation
has Vista Ultimate, SQL Server 2005 express (with the 'default' instance name
SQLExpress) and a local database I'd like to open with Access. I can access
the database using SQL Server Managemente console and my own software
(created with VS2008), but whenever I try to create an ADP I get an error
telling me that the server is not found.

Here's what I do: I start Access 2007, create new blank database, select ADP
as file type in the 'open' button; I then answer I want to connect to an
existing SQL server database, and in the Data Link Properties window I enter
required data (server name = (local)\SQLExpress, NT integrated security,
database name). If I click Test connection or just try to click OK I get the
error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen(Connect()).]Specified SQL server not found.

Any hint?
 
Thanks Tom, I did not mention this but I had already tried with the 'real'
computer name. Same result... any other suggestion?
 
If in the SQL Server 2005 Surface Area Configuration | Remote Connection,
only the Local connection are enabled, then only .\ or np:(local)\ or
np:NameOfYourServer\ will work; other thing like (local) won't work in most
cases because ADP will first try to use TCP and TCP is deactivated when
using Local connection only.

Either use np:(local) or enable TCP in the Local and Remote connctions
option in the SQL-Server 2005 Surface Area Configuration for Services and
Connections.
 
Hello Tekton,

You may want to use sqlcmd to test if you could connect to the SQL server
properlyL

sqlcmd -S servername\SQLExpress -E

sqlcmd -S (local)\SQLExpress -E

sqlcmd -S tcp:servername\SQLExpress -E

If you encounter any error message, please feel free to let's know. Thank
you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi all, thanks for your replies. Here are some more details:

* if I enable TCP/IP remote connection Access 2007 sees the server
(local)\SQLExpress and connects to it. Other PC's on LAN can connect to the
database, too.

* if I disable remote connection, whatever name I use Access cannot see the
server: I've tried with (local), .\, np:(local), true name, etc.

* sqlcmd like any other client software works with no issue using
(local)\SQLExpress -E.

* the connection to a SQL Server 2005 Express 'default' instance from Access
2007 with local access only works fine in Windows XP PC's: I can access the
local SQL server even if TCP/IP remote is disabled. The same procedure with
the same SQL Server settings does not work in Vista.

* just to complete the picture, once both PC's on the same LAN, one with
Vista and another with XPSP2, have their SQL Express configured for remote
TCP/IP access, the XP pc can connect to the Vista pc SQL server but not
viceversa; both PC's have the same SQL server settings and the same firewall
& antivirus software configured in the same way (I've also tried disabling
it).

BTW, happy new year ;)
 
After enabling remote access via TCP/IP I also started the browser service
(to do this I had to stop the SQL engine and restart to apply changes, and
then enable the browser service to be started manually and started it).
 
If the firewalls (client and server) are disabled and the SQL-Server Browser
started, I really don't understand why you cannot connect from a Vista
machine.

Vista 32 bit or 64 bit? I remember seeing some reports of people having
trouble connecting with SQL-Server Express when running on a 64 bit machine.

Also, what kind of authentification are you using? If you are using
Integrated Security, then try with a SQL-Server account such as "sa".
 
Hello,

If you try to connect to the server as "SA" or a different login with
sysadmin role as Sylvain mentioned, what's the result?

If you create a new database other than connect to a existing database, do
you see the same the error message?

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Sorry for the delay in my response:

- using Access 2007 in my Vista ultimate 32 bit machine I cannot access an
existing database but it seems I can create a new one (trusted access, server
name = 'true' machine name \SQLExpress).

- as for the non-trusted login, I'm not allowed to do such a test at this
time, I'm restricted to trusted access...
 
Hello,

Since the issue doesn't occur if you create this database, it seems to be
that a permission related problem on the existing database. I suggest that
you contact to sql admin to let him grant you database owner of that
database to test. Also, please let them test on a domain user with sysadmin
role of the SQL Server.

If you have any further qustions, please feel free to let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi,

I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top