file link specification problem

[SandyR]

I am using Access 2002.

I have a database the uses several text files which are updated frequently.
I want to relink these everytime I open the database. I created a splash
form and put in code to do so. I put the splash form into the startup
parameters.

When I open the database with the shift key, then open the splash form, it
seems to work just fine. When I open the database without the shift key, I
get an error message saying that the file specification doesn't exist.
Could someone help me figure out why this is happening and how to fix it?

Here is my code:

Private Sub Form_Open(Cancel As Integer)
Dim dummy As Variant
dummy = relink_text_files()
End Sub
Public Function relink_text_files()
' this function is to link in the text files
' which should reside in the same directory as the FE database

Dim DB1 As Database
Dim path As String, ctype As String, parametersfromconnect As String
Dim oldconnect As String, newconnect As String
Dim i As Integer, j As Integer
On Error GoTo ERROR_relink_text_files
Set DB1 = CurrentDb

' find the location of the data base - Work backward and look for the back
slash
For i = Len(DB1.Name) To 1 Step -1
If Mid(DB1.Name, i, 1) = Chr(92) Then
path = Mid(DB1.Name, 1, i)
Exit For
End If
Next

' Look for text files and relink them when you find them
For i = 0 To DB1.TableDefs.Count - 1
oldconnect = DB1.TableDefs(i).connect
If DB1.TableDefs(i).connect <> " " Then
If Mid(DB1.TableDefs(i).connect, 1, 4) = "Text" Then
' extract the parameters
parametersfromconnect = Left(oldconnect, InStr(oldconnect,
"DATABASE=") + 8)
DB1.TableDefs(i).connect = parametersfromconnect + path
DB1.TableDefs(i).refreshlink
End If
End If
Next
Exit_relink_text_files:
Exit Function

ERROR_relink_text_files:
MsgBox Err.Description
Resume Exit_relink_text_files

End Function

 

[Ralph]

I tested your code and it works. I got it to fail by deleting the import spec
for the text file. If the error number is 3265 then that is what your problem
is. Also an easier way to get the path of the database is:
path=currentproject.path & "\"

 

[SandyR]

Thanks for your reply. Do you have any idea how the specification might be
getting deleted? This is a test version of the program, accessed by no-one
else but me. If I open the database in bypass mode, the specification is
there and the code works. If I open it as a user would, the code fails.
There is very little other code in this form - just an on-click for the
single button that I have put on it so far.

 

[Ralph]

My apologies for not reading your post more carefully. It appears there is a
problem with the form loading and using the refreshlink. The specs are stored
in a system table named MSysIMEXSpecs and in my testing the spec exists there
yet I get the same error message you get that it does not exist.

 

[Ralph]

By setting the code to run on the Close event of the Splash form rather than
the Load or Activate event, I got it to work.

 

[SandyR]

Thank you! Thank you ! Thank you !
It never occurred to me to try putting it into the on close event.