- You are working with a split database, and the backend database file resides in a folder that has a name that is longer than 8 characters. For example, the backend database file resides in a folder that has a name that is similar to the following: BigFolderName
- You are working with a split database, and the name of the backend database file is longer than 8 characters excluding the three-character .mdb file name extension. For example, the backend database file has a name that is similar to the following:BigDatabaseFileName.mdb
- You are working with a database that is not split, and the name of the database file does not follow the 8.3 naming convention. Or the database is stored in a folder that has a long name.
In Windows 2000 and in later versions of Windows, the caching design of the Workstation service has been changed to favor database integrity in a multi-user environment. This design change causes a delay when Jet makes API calls to retrieve information about the long folder name or about the long file name.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
If you are working with a split database, you can resolve this issue by relinking your existing Jet tables. To do this, create code to send a short representation of the long folder name to the Windows API. This resolution requires your backend database to have a file name that follows the 8.3 naming convention. To create this code, follow these steps in the front-end database:
- Set a reference to Microsoft ADO Ext 2.5 (or to a later version of ADO).
- Add the following code to a new module.
Declare Function GetShortPathName Lib "kernel32" _
Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long
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.
MsgBox "Cannot update: '" & objTbl.Name & "'" & String(2, vbCrLf) & "File not found: " & vbCrLf & strFullName
blnFailedLink = True
If InStr(strFilename, ".") > 9 Then blnLongFileName = True
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
MsgBox "The links were successfully updated!!! ", vbInformation
MsgBox "The links were not successfully updated." & vbCrLf & "Please verify you table links.", vbExclamation
MsgBox Err.Description & " " & Err.Number
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)
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)
DoesFileExist = False
DoesFileExist = False
- Run the RefreshLinks function. To do this, follow these steps:
- In Visual Basic Editor, click Immediate Window on the View menu.
- In the Immediate window, type RefreshLinks. Then, press ENTER.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
Note If you use Windows XP, you may improve performance by applying Microsoft Windows XP Service Pack 2 (SP2).
Article ID: 891176 - Last Review: Sep 17, 2011 - Revision: 1