Problem using Access Upsizing Wizard .MDB-->SQL Server 2005

[Ed White]

I'm trying for the first time to upgrade a .mdb database created with Access
2003 to use with the SQL server. I have Visual Studio 2005 installed, which
includes Microsoft SQL Server 2005 (Express Version). I've gone to the SQL
Server Configuration Manager and entered the same name and password as I have
for the computer administrator (which is me), and ensured that the SQL Server
is running. I then go into Access 2003 and run the Upsizing Wizard. I check
'Create new database', and the next window asks "What SQL Server...?", and
defaults to "(local)", which I leave as is. I type in the same Login ID and
Password as with the Configuration Manager, and click Next. After a short
while, a error window shows up "Microsoft SQL Server Login", with "Connection
failed: SQLSTATE: 01000, SQL Server Error: 2", and also "Connection failed
failed: SQLState 08001 SQL Server Error 17". As I have never done this
before, I suspect there is something simple I'm missing. Suggestions?

 

[Kevin3NF]

It may be as simple as Access 2003 having no clue what SQL 2005 is...

We ran into this when SQL 2000 was released as well IIRC...

Try importing from the SQL Server side, instead of pushing form the Access
side

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Ed White]

I've not done this before. Where do I find out how to import from the SQL
Server side? Should I also try installing an earlier version of SQL Server,
or would there be a problem having two versions of SQL Server on the same PC?

 

[Sylvain Lafontaine]

The SQL Server Configuration Manager is for chosing which local machine
account to use for running the SQL Server 2005 service and has nothing to do
with login to the SQL-Server.

You must use Trusted Connection or possibly, if you have installed
SQLExpress in mixed mode, the SA account. Later, if you are using the mixed
mode and have created other accounts on the SQL-Server, then you could also
use of these accounts.

Also, if you have chosen to install a named instance instead of a default
instance, then you must also add the name of this instance after the name of
the machine: (local)\MSSQL2005 for exemple.

There is no problem upsizing from MDB to SQL-Server 2005 but after this
step, the design tools for ADP won't work. For this, you can use Visual
Studio 2005 or the new Microsoft SQL Server Management Studio Express, for
which the CTP is now available:

http://www.microsoft.com/downloads/...59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en

 

[Ed White]

Thanks, Sylvain. I had to change (local) to (local)\SQLEXPRESS (the "\" as
opposed to a "/" was critical), and checked Trusted Connection, and it
worked. I found the SQLEXPRESS name in the Tools-Options-Database Tools-Data
Connections section of VS 2005.

As to whether I installed SQL 2005 in the mixed mode, I don't know. I
installed it as part of a full installation of Visual Studio 2005
Professional Edition.

 

[InvestorDrew]

Ed,
Thanks for explaining how you fixed it. I too had the same problem. From
your example I discovered \machinename\SQLEXPRESS worked.
Cheers,
Drew

 

[Ed White]

Hi Drew,
I had installed the Beta version of VS 2005, and then went through the
uninstall process detailed online before installing the release version.
However, I eventually had to reformat my disk and do a clean install before
all the bugs I was getting went away. Aparently, the uninstall does not
completely uninstall the beta versionl