Solved Alterate a SqlAzure table field

[Etxezarreta]

Hello everyone,
I would like to alterate a field, from short text to long text in a SqlAzure table, used via an Access FE.
What would be the best way to do so, I don't know if I need to delete and rebuild the relations between the tables.
Many thanks in advance.
Exte.

 

[Minty]

The relationships would be set in the Azure Database not in Access.
Simply make the change in the Azure table and refresh the table link in the Access Front End.

If you have any Access queries that join on that field you will have to move them to Azure Views as I'm pretty sure (from memory) that Access won't allow a join on a memo field.

 

[Etxezarreta]

Ok, I am connected with SSMS, now I need to learn how to make a change directly in SqlServer!

 

[Etxezarreta]

I wrote the following query:

ALTER TABLE [dbo].[Contacts_t_ListeContacts]
ALTER COLUMN [Nom_contact] nvarchar(255) NOT NULL

and the following message appears:

Msg 5074, Niveau 16, État 1, Ligne 1
The index 'Contacts_t_ListeContacts$t_ListeContactsNom_contact' is dependent on column 'Nom_contact'.
Msg 4922, Niveau 16, État 9, Ligne 1
ALTER TABLE ALTER COLUMN Nom_contact failed because one or more objects access this column.

No one is connected , I do not understand what happens.

 

[Minty]

There are two ways - easiest right click the table name in the object explorer and select design.

In the field change it to an Nvarchar(Max) as the datatype.
Then save. You may get a message that you can't alter the table without dropping it.
If so you will need to change a setting in azure to allow you to make design changes.

 

[Minty]

You may need to drop the index first, change the field then recreate the index.

nvarchar(255) is a short text field still?

 

[Etxezarreta]

Ok, I will try this evening, I will tell you, many thanks again Minty

 

[Etxezarreta]

Ok, it worked: I droped the index, then I successfuly ran the query (it didn't work righ-clicking).
Thanks again.
Etxe.

 

[Minty]

Glad you sorted it out. Good luck with the rest of your project.

 

[Pat Hartman]

When you make a structural change to the SQL Server BE, you MUST relink the changed tables/views. This isn't necessary when the BE is Jet/ACE because Access is always talking to itself.

 

[Etxezarreta]

Thanks Pat, yes, I just learnt it.

 

[Pat Hartman]

You're welcome. Glad to help.