Myself, I don't have A2007 installed on my machine precisely because of the
compatibility problems between A2003 and A2007. I'm waiting for the first
service pack for A2007 before thinking of it. The SP3 for Office 2003 also
seems to be the source of a lot of problems.
The most common source of connection problem with SQL2500 Express is to
forget to mention the name of the instance or to not mention the protocol to
be used (go with TCP/P if possible; to do it, use the tcp: prefix, use a tcp
adress (4 numbers) or mention the network protocol; for example: «
Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=myDataBase;User
ID=myUsername;Password=myPassword;
»), the port to be used (in the case of TCP/IP, not necessarily 1433 as in
the above example) and to check the firewall. Creating an alias under the
tab SQL Native Client Configuration / Alias in the SQL Server Configuration
Manager is a good way to solve these kind of problems.
Here are some good articles on resolving connection problem with SQL2500
Express:
http://www.datamasker.com/SSE2005_NetworkCfg.htm
http://blogs.msdn.com/sql_protocols/archive/2007/07/19/tcp-provider-no-connection-could-be-made-because-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.connectionstrings.com/?carrier=sqlserver (connection strings for
sql providers)
http://www.carlprothman.net/Default.aspx?tabid=81 (idem)
For your identity column, I don't know why the autonumber field has not been
translated to an identity field, so I cannot tell you anything more on this
but yes, you're right, it's easier to upsize a schema when there is no data
on it because you can easily change the schema later to correct these kind
of bugs. Later, you can easily transfert the data by using a copy of the
database.
if there is already data in the column on the server and you want to keep
these data because of relationship with other tables and whatever other
reasons, then you must create a new table with the identity property set,
transfert the data from the old table to the new one using the option SET
IDENTITY_INSERT table ON:
SET IDENTITY_INSERT NameOfYourNewTable ON
Insert records
SET IDENTITY_INSERT NameOfYourNewTable OFF
Then remove the relationships, delete the old table, rename the new one to
the older name and reetablish the relationships. Not very hard but not very
easy, either. It's easier to first upsize only the schema, then correct it
and after that, transfert the data.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by lousaintThanks for this response too, Sylvain. This post is really a result of my
1) I can't get the upsize wzard to work in either A2003 or A2007 - can't
connect to my SQL2005 Express server ('tho I can manage it outside of Access
and use all my tools, e.g. Ironspeed)
2) Because of (1) I looked into SSMA for Access but encountered the Windows
Installer problem
I did use full SQLServer's import to bring data into the db's on my W2003
server, but all my "autonumber" fields were simply set to INT and not
UniqueIdentifier. I tried to manually make this change but was told "can't
do that." Would I be able to do it if my imported tables did NOT contain any
data? Is that's what is preventing SQLServer from setting-up my primary keys
correctly?
You said you use "something else" to get your data into SQL Server. What do
you use?
Post by unknownI never tried them personally but to my knowledge, you can directly
detach/attach or backup/restore a database from SQL-Server 2005 Express to
one of the full edition; so there is not really any need for the
Import/Export Wizard if you want to upgrade to one of the full edition.
Moreover, if you have a full edition, then you have a copy of the
Import/Export Wizard. Don't forget that exporting from SQL-Server 2005
Express to a full edition is the same as importing from a full edition of
SQL-Server 2005 from a SQL-Server 2005 installation.
And also, from my personal experience with the previous versions of the
Import/Export wizard, you are better not to use this wizard if your database
is anything but very simple. I don't remember the last time that I tried to
use this tool instead of using something else.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by lousaintI assume the answer is "money" (isn't it always?), but ....
Why on earth, if you want people to REALLY use the above and then
upgrade
to
full edtions, did you exclude the Import/Export wizard?
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=48b0f2f8-bad7-4a56-8d5a-09b87c045d17&dg=microsoft.public.access.sqlupsizing