Upsize to SQL

[ChoonBoy]

I am trying to use SQL express for my Access2003.mdb
My PC has been installed with SQL server 2005 express.
I use the Upsizing wizard \ create new database \ use trusted connection and
use the default (local) as there is no other choice. When I click next the
error message on the last line is sql server does not exist or access is
denied.

I know I am missing something but do not know what, I have also subsituted
the (local) with IBM-0F0652AD13E\SQLEXPRESS and still cannot succeed.

How can I step by step make this work?
How do I create a server name for my PC (using winXP_home)?
Infact how do I even run the SQL server as I have never done this before.

Appreciate any help. Thanks in advance.

 

[Sylvain Lafontaine]

Well, this is more a configuration/installation/setup/connection problem
than an ADP problem. You can try different things like (local)\SQLEXPRESS,
..\SQLEXPRESS, tcip:IBM-0F0652AD13E\SQLEXPRESS,
np:IBM-0F0652AD13E\SQLEXPRESS; however, your first step should be to know
what you have in front of you. For exemple, did you really installed SQL
server 2005 Express using SQLEXPRESS as the instance name?

Many things could block the communication; for example a local firewall
blocking the port 1433. Trying to switch protocol (using tcp instead of np
or vice-versa) could help; here a some more info to help you with your
problem (I didn't took the time to check them again):

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm

 

[ChoonBoy]

Dear Sylvain,

Thank you so much, I had been trying for days, looking thru' tons of text
unsuccessfully.

I used your suggested np:IBM-0F0652AD13E\SQLEXPRESS and it works,

by the way what makes this np: so special to be able to trigger the process

Regards

 

[Sylvain Lafontaine]

tcp: is to indicate to specifically use the TCP/IP protocol and np: to use
the Named Pipes protocol. Some server address, like 127.0.0.1 or localhost
or even IBM-0F0652AD13E,1433 will indicate to use TCP/IP while other will
indicate to use a named pipe.

However, some server address - like IBM-0F0652AD13E - can indicate both and
in such occasions, you don't know what will be used. Sometimes, this is not
important as both protocol are available and functional but in other cases,
it does because one of them is unavailable or blocked. In such occasions,
Access doesn't take the time to try the second if the first one doesn't work
or is not available.

 

[ChoonBoy]

RePost (net line failed while posting earlier)

Hi Sylvan,

Thanks & need your help again. Few things happened when I upsized.

1) Most of my queries were wiped out. I suspect this could be due to too
many expressions in my fields).

eg, Sman Name: IIf([choice]="state","[Statecode] " &
[statecode],IIf([choice]="ASM",[asm],IIf([choice]="total",[Ctry],IIf([choice]="Nation",[Nation],IIf([choice]="Rep+",[SmanGrp],IIf([choice]="ASM+",[ASMgrp],[Smanname])))))) ( wonder if there is a better way to replace this monster)

2) I cannot design or add tables and queries. Just realise that A03 can
harmonize only with sql2k. So I installed MSDN using my Off03pro =
c:\sql2ksp3.

The problem is how do I use the new installation to create a server name
which I can use? or where can I get to download something like sql server
2000 express to use.

SQL is difficult, but have to adopt as my data >500K now.

Thanks in advance.

 

[Sylvain Lafontaine]

Answers in line.

RePost (net line failed while posting earlier)

Hi Sylvan,

Thanks & need your help again. Few things happened when I upsized.

1) Most of my queries were wiped out. I suspect this could be due to too
many expressions in my fields).

eg, Sman Name: IIf([choice]="state","[Statecode] " &
[statecode],IIf([choice]="ASM",[asm],IIf([choice]="total",[Ctry],IIf([choice]="Nation",[Nation],IIf([choice]="Rep+",[SmanGrp],IIf([choice]="ASM+",[ASMgrp],[Smanname]))))))
( wonder if there is a better way to replace this monster)

Normal, there is no iif() function in T-SQL. You will have to use a Case
statement. BTW, this monster will look better when you will use the Case
statement.

There are a lot of differences between Access/JET and T-SQL, so instead of
upsizing to an ADP project, you should upsize to an MDB file with ODBC
linked tables and go on from that point.

2) I cannot design or add tables and queries. Just realise that A03 can
harmonize only with sql2k. So I installed MSDN using my Off03pro =
c:\sql2ksp3.

This is MSDE 2000, not MSDN. In you case, install and use the SQL-Server
Management Studio - Express (SSMS-E) for manipulating your SQL-Server 2005
Express instance/database. The SSMS-E is available separately as a free
download or with the full version of SQL-Server 2005 Express.

The problem is how do I use the new installation to create a server name
which I can use? or where can I get to download something like sql server
2000 express to use.

Look at the services installed and running on your machine, you will be able
to find the name of the MSDE instance. (This will be the part following the
$ if I remember correctly). Personnally, I don't see any advantage in using
MSDE-2000 instead of SQL-Server 2005 Express; as the later is more recent
and powerful.

Simply don't forget to use the refresh command (F5 from the View menu) for
both the tables and the queries database windows of your ADP project after
each time you will make a change to the backend database with SSMS-E.

SQL is difficult, but have to adopt as my data >500K now.

You are just at the beginning of your problems; especially if you go with
ADP. I would suggest that you go with a MDB file and ODBC linked tables if
you don't know much about SQL-Server.