Solved

Why does Access 2007 make many calls to SQL Server looking for Extended Properties?

Posted on 2014-01-24
7
560 Views
Last Modified: 2014-01-29
I see this was asked and answered long ago here, but the accepted answer didn't really present a practical fix.

Problem: Legacy Access 2000 ADP application referencing a SQL Server database which has been ported up to Access 2007. The application works, but throws hundreds of calls to SQL looking for extended properties on tables per minute! Calls are like:

SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(N'MS_Description',N'user',N'dbo',N'table',N'Assessment_AG',NULL,NULL)

A good many folks on Google suggest setting the table's MS_SubdatasheetName extended property to "NONE", but this hasn't worked for me.

Has anyone managed to solve this problem?
0
Comment
Question by:kwebster7327
  • 4
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 39808855
Hi,

I also never saw any issue like this in the last 5 years where I constantly worked with A2007 ADPs. I can only guess that this has something to do with using an old file format in A2007 so it rather behaves like an A2000 ADP where this maybe had any sense.

I would also recommend to recreate your objects in a new A2007 ADP file. I have written a procedure to do that automatically which you can find here:

Option Compare Database
Option Explicit

Public Sub procRecreate()
    Dim strFile As String
    Dim strConnection As String
    Dim objAccess As New Access.Application
   
    strFile = InputBox("Filename: ", "Select:")
    If UCase(Right(strFile, 4)) = ".ADP" Then
        strFile = Left(strFile, Len(strFile) - 4)
    End If
    strConnection = Application.CurrentProject.Connection.ConnectionString
    Debug.Print "---- Create new database with current connection string ----"
    Application.CreateAccessProject CurrentProject.Path & "\" & strFile _
                                  & ".adp", strConnection
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenAccessProject CurrentProject.Path & "\" & strFile & ".adp" _
                              , True
    Debug.Print "---- Save current references ----"
    procRCSaveReferences
    Debug.Print "---- Save current objects ----"
    procRCSaveAllAsText
    Debug.Print "---- Load objects into new database ----"
    procRCLoadAllFromText objAccess
    Debug.Print "---- Load references into new database ----"
    procRCLoadReferences objAccess
    Debug.Print "---- Set options in the new database like in the current ----"
    procRCSetOptions objAccess
    objAccess.CloseCurrentDatabase
    Set objAccess = Nothing
End Sub

Private Sub procRCSaveAllAsText()
    Dim obj As Object
    Dim strModuleLine As String
    Dim strPath As String
   
    strPath = CurrentProject.Path & "\"
    On Error Resume Next
    Kill strPath & "Forms"
    Kill strPath & "Modules"
    Kill strPath & "Macros"
    Kill strPath & "Reports"
    RmDir strPath & "Forms"
    RmDir strPath & "Modules"
    RmDir strPath & "Macros"
    RmDir strPath & "Reports"
    MkDir strPath & "Forms"
    MkDir strPath & "Modules"
    MkDir strPath & "Macros"
    MkDir strPath & "Reports"
    On Error GoTo 0
    Debug.Print "Forms...";
    For Each obj In CurrentProject.AllForms
        Application.SaveAsText acForm, obj.Name _
                             , CurrentProject.Path & "\Forms\" & obj.Name _
                             & ".frm"
        Debug.Print ".";
    Next obj
    Debug.Print

    Debug.Print "Macros...";
    For Each obj In CurrentProject.AllMacros
        Application.SaveAsText acMacro, obj.Name _
                             , CurrentProject.Path & "\Macros\" & obj.Name _
                             & ".mac"
        Debug.Print ".";
    Next obj
    Debug.Print
    Debug.Print "Modules...";
    For Each obj In CurrentProject.AllModules
        Application.SaveAsText acModule, obj.Name _
                             , CurrentProject.Path & "\Modules\" & obj.Name _
                             & ".mod"
        Open CurrentProject.Path & "\Modules\" & obj.Name & ".mod" For _
                                                                   Input As #1
        Open CurrentProject.Path & "\Modules\" & obj.Name & ".bas" For _
                                                                  Output As #2
        ' Bei "SaveAsText" wird die folgende Zeile nicht mit gespeichert,
        ' diese wird aber für den Import benötigt, damit Access automatisch
        ' den richtigen Modulnamen verwendet. Daher wird die Zeile hier
        ' ergänzt.
        Print #2, "Attribute VB_Name = """ & obj.Name & """"
        Do
            Line Input #1, strModuleLine
            Print #2, strModuleLine
        Loop Until EOF(1)
        Close #1
        Close #2
        Kill CurrentProject.Path & "\Modules\" & obj.Name & ".mod"
        Debug.Print ".";
    Next obj
    Debug.Print
    Debug.Print "Reports...";
    For Each obj In CurrentProject.AllReports
        Application.SaveAsText acReport, obj.Name _
                             , CurrentProject.Path & "\Reports\" & obj.Name _
                             & ".rpt"
        Debug.Print ".";
    Next obj
    Debug.Print
End Sub

Private Sub procRCLoadAllFromText(ByRef objAccess As Access.Application)
    Dim strFile As String

    Debug.Print "Forms...";
    strFile = Dir(CurrentProject.Path & "\Forms\*.*")
    Do While strFile <> ""
        objAccess.Application.LoadFromText acForm _
                                         , Left(strFile, Len(strFile) - 4) _
                                         , CurrentProject.Path & "\Forms\" _
                                         & strFile
        strFile = Dir()
        Debug.Print ".";
    Loop
    Debug.Print
    Debug.Print "Macros...";
    strFile = Dir(CurrentProject.Path & "\Macros\*.*")
    Do While strFile <> ""
        objAccess.Application.LoadFromText acMacro _
                                         , Left(strFile, Len(strFile) - 4) _
                                         , CurrentProject.Path & "\Macros\" _
                                         & strFile
        strFile = Dir()
        Debug.Print ".";
    Loop
    Debug.Print
    Debug.Print "Modules...";
    strFile = Dir(CurrentProject.Path & "\Modules\*.*")
    Do While strFile <> ""
        objAccess.Application.LoadFromText acModule _
                                         , Left(strFile, Len(strFile) - 4) _
                                         , CurrentProject.Path & "\Modules\" _
                                         & strFile
        strFile = Dir()
        Debug.Print ".";
    Loop
    Debug.Print
    Debug.Print "Reports...";
    strFile = Dir(CurrentProject.Path & "\Reports\*.*")
    Do While strFile <> ""
        objAccess.Application.LoadFromText acReport _
                                         , Left(strFile, Len(strFile) - 4) _
                                         , CurrentProject.Path & "\Reports\" _
                                         & strFile
        strFile = Dir()
        Debug.Print ".";
    Loop
    Debug.Print
End Sub

Private Sub procRCSaveReferences(Optional strRefFileName As Variant)
On Error GoTo SaveReferences_Error
    Dim objRef As Access.Reference
    Dim f As Integer
 
    If IsMissing(strRefFileName) Then
        strRefFileName = CurrentProject.Path & "\References_" _
                       & Left(CurrentProject.Name _
                            , Len(CurrentProject.Name) - 4) & ".txt"
    End If
    f = FreeFile
    Open strRefFileName For Output As #f
    For Each objRef In Access.References
        'If Not objRef.IsBroken Then Print #f, objRef.Name & ":" & vbTab _
                                             & vbTab & objRef.FullPath
        If Not objRef.IsBroken Then Print #f, objRef.FullPath
    Next objRef
    Close #f
SaveReferences_Exit:
    Exit Sub
SaveReferences_Error:
    MsgBox Err.Description
    Resume SaveReferences_Exit
End Sub

Private Sub procRCLoadReferences(ByRef objAccess As Access.Application)
    Dim f As Integer
    Dim strLine As String
   
    With objAccess
        ' Access trägt standardmäßig ADODB 2.5 ein, daher erst Remove
        .Application.References.Remove .Application.References.Item("ADODB")
        f = FreeFile
        Open CurrentProject.Path & "\References_" _
           & Left(CurrentProject.Name, Len(CurrentProject.Name) - 4) _
           & ".txt" For Input As #f
        On Error Resume Next
        Do While Not EOF(f)
            Line Input #f, strLine
            .Application.References.AddFromFile strLine
        Loop
        Close #f
    End With
End Sub

Private Sub procRCSetOptions(ByRef objAccess As Access.Application)
    Dim strOptions(1 To 14) As String
    Dim varValue As Variant
    Dim objProperty As AccessObjectProperty
    Dim strPropertyName As String
    Dim i As Long
   
    strOptions(1) = "Auto Compact"
    strOptions(2) = "Remove Personal Information"
    strOptions(3) = "Themed Form Controls"
    strOptions(4) = "DesignWithData"
    strOptions(5) = "CheckTruncatedNumFields"
    strOptions(6) = "Picture Property Storage Format"
    strOptions(7) = "Track Name AutoCorrect Info"
    strOptions(8) = "Perform Name AutoCorrect"
    strOptions(9) = "Log Name AutoCorrect Changes"
    strOptions(10) = "Show Values in Indexed"
    strOptions(11) = "Show Values in Non-Indexed"
    strOptions(12) = "Show Values in Remote"
    strOptions(13) = "Show Values in Snapshot"
    strOptions(14) = "Show Values in Server"
    ' Die Option läßt sich nicht setzen, wenn der Wert außerhalb des
    ' Integer-Bereichs liegt.
    ' Die GUI läßt hier einen größeren Wert zu.
    'strOptions(15) = "Show Values Limit"
    With objAccess
        For i = LBound(strOptions) To UBound(strOptions)
            varValue = Application.GetOption(strOptions(i))
            .SetOption strOptions(i), varValue
        Next i
        On Error Resume Next
        For Each objProperty In CurrentProject.Properties
            strPropertyName = ""
            strPropertyName _
                           = .CurrentProject.Properties(objProperty.Name).Name
            If strPropertyName <> objProperty.Name Then
                .CurrentProject.Properties.Add objProperty.Name _
                                             , objProperty.Value
              Else
                .CurrentProject.Properties(objProperty.Name) _
                                                           = objProperty.Value
            End If
        Next objProperty
    End With
End Sub

Open in new window


Copy that into a new standard module into your database, then you can start it with "procRecreate". You should do that in A2007.
It will ask you for the name of the new database which it creates in the same folder and for the connection to be used in the new database, then it creates a bunch of folders for the different kind of objects in the same folder. Next all objects will be exported as pure text files and then reimported into the new database. All properties and references of the old database will also be restored so the new one should be working exactly like before.

I use that on a regular basis for my ADPs to free them from any internal "binary trash" which regularly makes the file a lot smaller than before. Worked in all cases for me without any issue (except when i.e. a form has an internal error, then you must reimport that on your own or even better create it as new). The original database will not be changed, anything is done on the new one.
After completing you can open the new one and then you must compile it once (which makes the file a little bit larger) as this is not be done by the code.

Hope that helps to clean up your database.

Cheers,

Christian
0
 
LVL 1

Author Comment

by:kwebster7327
ID: 39812056
Christian,

Thank you.

This looks like it might work. I've got a few forms which won't come over, but I can fix them one by one.

Biggest problem remaining is getting a custom menubar in the old ADP transferred over. Any ideas on that?

Thanks again!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39812354
Hi,

unfortunately not, that will be transferred automatically, you'll find that in the ribbons under Add-Ins.

It is highly recommended to create a ribbon instead and move the commandbar functions to the ribbon - looks very much more professional and you get rid of these old menus. But there is no automatic process for that, you must do that on your own (there are tools like RibbonCreator or you can do that by creating a XML file and load that on start from the SQL Server from a table).

Cheers,

Christian
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:kwebster7327
ID: 39817959
I've think I've gotten everything ported except those menu bars.

I'd like to switch to the ribbon, but the old menu bars are pretty extensive and have a lot of code driving them. This thing is too close to the end of it's life to do that kind of rewrite.

After spending a day on it, I have code to get them created, but can't get them to actually fire the events they are supposed to. I suspect it is because the index and ID are read-only properties of the command button, so they don't line up with the code.

After Googling extensively, I'm surprised nobody else has posted anything about this.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39818176
Hi,

command bars are a little bit tricky and it is possible, depending on your code, that you've created the command bars more than once so the wrong ones are tied to the events. It is a good idea to first clean up the command bars by removing all of them (if they are created by code) at the application start.

If the command bars are static they should be already in the Add-In menu with the same functionality than before (but my tool above doesn't import command bars so that's only the case in the original file or if you recreate them manually).

I use command bars very rarely so I'm afraid I cannot help you very much with this issue.

Cheers,

Christian
0
 
LVL 1

Author Closing Comment

by:kwebster7327
ID: 39818683
Christian,

Fixed it (so far) with the help of coworkers who weren't as wrapped up in the typical "can't see the forest for the trees" view I was having.

1) Add your code to the existing ADP.
2) Create new ADP with the your code.
3) Open new ADP and use "Import External Data" to pull in ONLY the forms that failed. At the same time, be sure to check the "Import Menus and Toolbars" checkbox under the advanced options.
4) Compile new ADP into ADE.

In my case, the output files are much smaller, indicating the amount of legacy garbage we were hauling around.

Thanks again for your help.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39819340
Hi,

maybe a good idea to do that again, it's possible that the manually imported forms now work for a new import using the code (but the command bars must be imported manually).

You can use this as often as you want, it's a good clean-up from time to time.

Glad if I could help you.

Cheers,

Christian
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now