assistance developing sub to remove all extended properties from DB

[Movian]

Hey so I have found an annoying quirk with access connected to SQL back-end where by if a table or any of the columns in the table have an extended property you are unable to view that table directly from the access front end via docmd.opentable 'tablename' as it generates an error.

I do not make use of extended properties and these seem to be items that have been automatically added by access itself....

as such I am trying to generate a sub that can be run to systematically remove all extended properties from a table and its contained columns.

The only sql methods I have found to do that rely on multiple queries that use results from the previous query which does not lend itself to being run directly from the access front end.

is there a way to get a results set of all the extended properties in a single query? so I could then create a loop that will cycle through each one and deleted it?

my research has turned up little in that regard so far aside from the aforementioned solutions that would be a little tricky....

I suppose I could investigate turning this into a stored procedure and triggering it but again I havn't found many solutions in that vein either.

[jonh]

I've no experience of this but if access made changes to a sql server database I would be worried.

[HiTechCoach]

I've no experience of this but if access made changes to a sql server database I would be worried.

Why would you worry? That is by design. Any front end/tool that has the correct permissions can add an Extended property. The key is permissions.

[HiTechCoach]

See:

Using Extended Properties on Database Objectshttps://technet.microsoft.com/en-us/library/ms190243%28v=sql.105%29.aspx

[jonh]

Why would you worry? That is by design. Any front end/tool that has the correct permissions can add an Extended property. The key is permissions.

I forgot to add a 'without my knowledge'.

Obviously it should be allowed to make changes, but the OP seems to suggest that changes were made to the sql database automatically by Access without anybodys knowledge, which I would find a bit worrying.

[Movian]

it appears to have automatically added properties when editing tables in the access table edit view (rather than editing through management studio or sql statements), I am sure it was just trying to help but its causing more hassle than help....

thanks for the info I will see what I can come up with and will post my results here!

[Movian]

Ok I think I may have a solution....

I can create a recordset with a list of all extended properties with the following query

"SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]FROM sys.extended_properties EP
LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id
LEFT JOIN sys.schemas S on O.schema_id = S.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id"

il generate that as an adodb.recordset then just do while not myrs.eof then for each record I generate a new SQL query using sp_addextendedproperty

Can I pass paramaters for a sql query as part of a currentproject.connection.execute "", dbfailonerror? something like this?

~EDIT~

I programed the sub but can't test it at the moment so if you want to review and mention any problems before I run it please feel free.


Public Sub RemoveAllExtendedProperties()Dim myrs As New ADODB.Recordset
Dim SQL As String


myrs.Open "SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property] FROM sys.extended_properties EP LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id LEFT JOIN sys.schemas S on O.schema_id = S.schema_id LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id", CurrentProject.Connection, adOpenKeyset, adLockReadOnly


Do While Not myrs.EOF
SQL = "EXEC sp_dropextendedproperty @name = '" & myrs("Name") & "'"
CurrentProject.Connection.Execute SQL, dbFailOnError
myrs.MoveNext
DoEvents
Loop
myrs.Close
Set myrs = Nothing
End Sub

[HiTechCoach]

Curious,what version of Access and SQL Server?


I find that the version of Access needs to be same or newer than the version of SQL Server to get the most compatibility.

Access does not fully understand (compatible with) all the features in MS SQL Server. Example, some data types are not compatible. Because of this I have learned to NEVER edit any design of an SQL Server database from Access.