Solved Alterate a SqlAzure table field (1 Viewer)

Etxezarreta

Member
Local time
Today, 13:48
Joined
Apr 13, 2020
Messages
175
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

AWF VIP
Local time
Today, 12:48
Joined
Jul 26, 2013
Messages
10,355
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

Member
Local time
Today, 13:48
Joined
Apr 13, 2020
Messages
175
Ok, I am connected with SSMS, now I need to learn how to make a change directly in SqlServer!
 

Etxezarreta

Member
Local time
Today, 13:48
Joined
Apr 13, 2020
Messages
175
I wrote the following query:
Code:
ALTER TABLE [dbo].[Contacts_t_ListeContacts]
ALTER COLUMN [Nom_contact] nvarchar(255) NOT NULL
and the following message appears:

Code:
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

AWF VIP
Local time
Today, 12:48
Joined
Jul 26, 2013
Messages
10,355
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

AWF VIP
Local time
Today, 12:48
Joined
Jul 26, 2013
Messages
10,355
You may need to drop the index first, change the field then recreate the index.

nvarchar(255) is a short text field still?
 

Etxezarreta

Member
Local time
Today, 13:48
Joined
Apr 13, 2020
Messages
175
Ok, I will try this evening, I will tell you, many thanks again Minty
 

Etxezarreta

Member
Local time
Today, 13:48
Joined
Apr 13, 2020
Messages
175
Ok, it worked: I droped the index, then I successfuly ran the query (it didn't work righ-clicking).
Thanks again.
Etxe.
 

Minty

AWF VIP
Local time
Today, 12:48
Joined
Jul 26, 2013
Messages
10,355
Glad you sorted it out. Good luck with the rest of your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:48
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom