Access 2007/2010 question

[DBinPhilly]

Sometimes when making just a minor change in an Access 2007 or 2010 program, the system will freeze for minutes at a time - up to 5 to 10 minutes with the "Microsoft Access Not Responding" message

What could be causing this problem? My FE database in this case is about 40Mb. I sometimes end up bailing and restarting the program. This can happen when I'm merely looking at a Query and trying to save a minor change.

Ideas or suggestions are gratefully appreciated.

[BrianMH]

Is the BE on a shared drive? There are some tips on the linke below.

http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

I'm not sure why but the best performance increase I ever achieve is by using short names for linked tables. The code to convert your linked tables to short names is below. I actually downgraded to access 2003 as I had the same problem that you are having and it caused a few database corruptions. That said this should still help.

Declare Function GetShortPathName Lib "KERNEL32" _
Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long

Function RefreshLinks()
On Error GoTo errorhandler

'Define the ADOX Catalog object.
Dim objCat As New ADOX.Catalog
'Define the ADOX Table object.
Dim objTbl As ADOX.Table

'Database name of the linked table.
Dim strFilename As String
'Path and database name of the linked table.
Dim strFullName As String

Dim blnIsMapi As Boolean
Dim blnIsImex As Boolean
Dim blnIsTemp As Boolean
Dim blnLongFileName As Boolean
Dim blnFailedLink As Boolean
Const srtImex = "IMEX"
Const strMapi = "MAPILEVEL="

'Open the catalog.
objCat.ActiveConnection = CurrentProject.Connection

'Loop through the table collection and update the linked tables.
For Each objTbl In objCat.Tables
'Verify that the table is a linked table.
If objTbl.Type = "LINK" = True Then
blnIsTemp = objTbl.Properties("Temporary Table") Or Left(objTbl.Name, 1) = "~"
blnIsImex = (InStr(1, objTbl.Properties("Jet OLEDB:Link Provider String"), srtImex, vbTextCompare) > 0)
blnIsMapi = (InStr(1, objTbl.Properties("Jet OLEDB:Link Provider String"), strMapi, vbTextCompare) > 0)

If Not blnIsTemp And Not blnIsImex And Not blnIsMapi Then
'Verify that the table is a Jet table.
strFullName = objTbl.Properties("Jet OLEDB:Link Datasource")
strFilename = Mid(strFullName, InStrRev(strFullName, "\", _
Len(strFullName)) + 1, Len(strFullName))
'Determine whether the database exists.
If DoesFileExist(strFullName) = True Then
objTbl.Properties("Jet OLEDB:Link Datasource") = GetShortName(strFullName)
'Update the link by using the short path name.
Else
MsgBox "Cannot update: '" & objTbl.Name & "'" & String(2, vbCrLf) & "File not found: " & vbCrLf & strFullName
blnFailedLink = True
End If
If InStr(strFilename, ".") > 9 Then blnLongFileName = True
End If
End If
Next

If blnFailedLink = False Then
If blnLongFileName = True Then
MsgBox "The table links were successfully updated, but the name of the backend database file does not follow 8.3" & _
vbCrLf & "Please rename the file, relink the tables, and then run the procedure again.", vbExclamation
Else
MsgBox "The links were successfully updated!!! ", vbInformation
End If
Else
MsgBox "The links were not successfully updated." & vbCrLf & "Please verify you table links.", vbExclamation
End If

ExitHandler:
Exit Function

errorhandler:
MsgBox Err.Description & " " & Err.Number
Resume ExitHandler

End Function
Function GetShortName(ByVal sLongFileName As String) As String
Dim lRetVal As Long, sShortPathName As String, iLen As Integer
'Set up a buffer area for the API function call return.
sShortPathName = Space(255)
iLen = Len(sShortPathName)

'Call the function.
lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
'Remove unwanted characters.
GetShortName = Left(sShortPathName, lRetVal)
End Function

Function DoesFileExist(strFileSpec As String) As Boolean
'Return True if the file that is specified in the
'strFilespec argument exists.
'Return False if strFileSpec is not a valid
'file or if strFileSpec is a directory.
Const INVALID_ARGUMENT As Long = 53
On Error GoTo DoesfileExist_Err
If (GetAttr(strFileSpec) And vbDirectory) <> vbDirectory Then
DoesFileExist = CBool(Len(Dir(strFileSpec)) > 0)
Else
DoesFileExist = False
End If
DoesfileExist_End:
Exit Function
DoesfileExist_Err:
DoesFileExist = False
Resume DoesfileExist_End
End Function

[HiTechCoach]

Sometimes when making just a minor change in an Access 2007 or 2010 program, the system will freeze for minutes at a time - up to 5 to 10 minutes with the "Microsoft Access Not Responding" message

What could be causing this problem? My FE database in this case is about 40Mb. I sometimes end up bailing and restarting the program. This can happen when I'm merely looking at a Query and trying to save a minor change.

Ideas or suggestions are gratefully appreciated.

I have seen this with staring with JET 4 in Access 2000 and later.

Where is your front end? Is it on your local drive?

Are you linked to a development back end or the production back end that is opened by others?

I find that the number of objects plays a role not just file file.

A common cause is having Auto Correct turned on. It must look at all objects to see if any updates are needed. I keep it turned off.

Also see: http://www.granite.ab.ca/access/performancefaq.htm