salmonella said:
did not get a response on first post so will try a different angle. I need
to
be able to modify the Form/table link to accept a file name with a period
at
the beginning (UNIX hidden file), e.g. (.myfile.mdb). I cannot do it
through
the link table wizard since it rejects a file name starting with a period.
Is
there a way put the file name in the connection string for all the forms
in
my FE (they all are accessing the same BE).
Here is the routine I use to assign links to a file. I've never tried a
filename with a leading period, but the code should work if Jet accepts that
filename.
Public Function pjsRefreshLinks(strDataFileName As String) As Boolean
'Purpose: Try to refresh the links for this code MDB's attached tables,
using the strDataFileName argument
On Error GoTo ErrorHandler
'Update connection information in attached tables.
Const NONEXISTENT_TABLE As Long = 3011
Const FILE_NOT_FOUND As Long = 3024
Const ACCESS_DENIED As Long = 3051
Const READ_ONLY_DATABASE As Long = 3027
Dim myTable As DAO.TableDef, tdfs As DAO.TableDefs, _
rstTemp As DAO.Recordset
Dim lngTableCount As Long, strAttach As String, _
strSQL As String, strTableName As String, _
strFirstField As String
'Initialize
Set tdfs = CurrentDb.TableDefs
SysCmd acSysCmdInitMeter, "Attaching tables", tdfs.Count - 1
strAttach = ";DATABASE=" & strDataFileName
'Loop through all tables, reattaching ALL attached Access tables
lngTableCount = 0 ' Initialize lngTableCount for status meter.
For Each myTable In tdfs
If (myTable.Attributes And dbAttachedTable) = dbAttachedTable Then
If Left(myTable.Connect, 10) = ";DATABASE=" Then 'MS Access
linked table
myTable.Connect = strAttach
myTable.RefreshLink
'Open a single recordset to keep a connection to the backend
db (so code runs faster)
If rstTemp Is Nothing Then
strTableName = myTable.Name
strFirstField = myTable.Fields(0).Name
strSQL = "Select Top 1 " & strFirstField _
& " From " & strTableName _
& " Order By " & strFirstField
Set rstTemp = CurrentDb.OpenRecordset(strSQL,
dbOpenDynaset, dbReadOnly)
End If
End If
End If
lngTableCount = lngTableCount + 1
SysCmd acSysCmdUpdateMeter, lngTableCount
Next myTable
pjsRefreshLinks = True
ExitHandler:
On Error Resume Next
Set myTable = Nothing
Set tdfs = Nothing
rstTemp.Close
Set rstTemp = Nothing
SysCmd acSysCmdRemoveMeter
Exit Function
ErrorHandler:
Select Case Err.Number
Case NONEXISTENT_TABLE
Err.Description = Err.Description & vbCrLf & _
"File '" & strDataFileName & "' does not contain required table '"
& myTable.SourceTableName & "'"
Case FILE_NOT_FOUND
Err.Description = Err.Description & vbCrLf & _
"You can't run this program until you locate " & strDataFileName
Case ACCESS_DENIED
Err.Description = Err.Description & vbCrLf & _
"Couldn't open " & strDataFileName & " because it is read-only or
it is located on a read-only share."
Case READ_ONLY_DATABASE
Err.Description = Err.Description & vbCrLf & _
"Can't reattach tables because " & strDataFileName & " is
read-only or is located on a read-only share."
End Select
'Insert your error handling here. I use a custom error object.
pjsRefreshLinks = False
Resume ExitHandler
Resume
End Function
