Access 2003 Upsize Wizard can't find SQL Server (2005)

[Guest]

This post is similar to an existing regarding Access 2003 not able to upsize
to SQL Server 2005. However, I'm using SQL Server 2005 Developer Edition.
Both the Access .mdb and the SQL Server files are located on the same
machine, my PC. When I try to upsize the .mdb file, Access can't find the
server, which is the same name as my PC. I've tried several hinted
solutions, typing "(local)\MSSQL2005" and "[machine name]\MSSQL2005", with
Trusted Connection checked AND with a supplied user name and password ("sa" &
valid password). The results are the same: Server error 53 (connection
failed) and server error 17 (SQL Server doesn't exist or access denied).

 

[Norman Yuan]

1. You must know the SQL Server name: since multiple SQL Server 2005
instances are allowed to be installed in one computer, only one instance can
be default instance, which name is the same as computer name or "(local)",
other instances' name should be "computerName\InstanceName". Note, it is
possible to only have named instance. So, you need to know the SQL Server
you installed is a named instance or default instance.

You can go to Control Panel->Administrative Tools->Services, look for "SQL
Server" service, double click it to open "Properties" dialog box. If the
"Service Name" looks like "MSSQL$xxxxxxxx", then the SQL Server is named
instance, "xxxxxxxx" is the instance name, if "Service Name" looks like
"MSSQLSERVER", it is default instance.

2. In the connection string, if you use "Trusted Connection", you do not
supply username/password. Trusted Connection means SQL Server uses Windows
security authentication mode. If you want to pass username/pasword instead,
you must make sure the SQL Server 's mixed security mode is enabled
explicitly. Knowing SQL Server security is very basic for using SQL Server.
You may want to to a bit more study on this topic.