Link backend issues (1 Viewer)

mtn

Registered User.
Local time
Today, 09:20
Joined
Jun 8, 2009
Messages
54
Hello Everyone,

I have this function working perfectly in AC2003 but not in AC2007 format

--------------------------------------------------------
Public Function fGetLinkPath(strTable As String) As String
Dim dbs As DAO.Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))


End If
Set dbs = Nothing
End Function
-------------------------------------------------------------

When you call it from the open action:

------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim CurrPath As String
CurrentDb.TableDefs.Refresh
For Each loTd In CurrentDb.TableDefs
CurrPath = fGetLinkPath(loTd.Name)
Exit For
Next loTd

Set loTd = Nothing
If PathDBExist(CurrPath) = False Then
MsgBox "Path to database is invalid.Please Re-connect to the database", vbCritical + vbOKOnly, "CWORKS CMMS"
DoCmd.OpenForm "DataSource"
Exit Sub
End If
End Sub

-------------------------------------------------------------

Does anyone know what I need to do to make it work? It returns false each time thereby giving the msgbox info above. This shouldn't be because the file actually exist. Like I said, it works perfectly on .mdb but not on .accdb

Any help would be cvery much appreciated. I am not the author but found the code in one of the sample files I downloaded a long time ago form the net.

Thanks.
 

mtn

Registered User.
Local time
Today, 09:20
Joined
Jun 8, 2009
Messages
54
loTd is a TableDef as in:
-------------------------------------
Dim loTd As DAO.TableDef
-------------------------------------

I forgot to include this too that is called in the previous code in my previous post:

-------------------------------
Public Function PathDBExist(MyPath As String) As Boolean
On Error GoTo err

'Set fso = New Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(MyPath) = True Then
PathDBExist = True
Else
PathDBExist = False
End If
Set fso = Nothing

Exit Function
err:
PathDBExist = False
Exit Function
End Function
-----------------------------------------------
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:20
Joined
Sep 12, 2006
Messages
15,613
your code

Code:
Private Sub Form_Open(Cancel As Integer)
Dim CurrPath As String
CurrentDb.TableDefs.Refresh
[COLOR="Red"]For Each loTd In CurrentDb.TableDefs
CurrPath = fGetLinkPath(loTd.Name)
[COLOR="Blue"]msgbox(iotd.name) 'add this[/COLOR]
Exit For
Next loTd[/COLOR]


Set loTd = Nothing
If PathDBExist(CurrPath) = False Then
MsgBox "Path to database is invalid.Please Re-connect to the database", vbCritical + vbOKOnly, "CWORKS CMMS"
DoCmd.OpenForm "DataSource"
Exit Sub
End If
End Sub

for some reason this sets out to iterate all the tabledefs, but exits after the first.

I expect A2003 and A2007 process the tables in different orders - so in A2003 you find a linked table - bit in A2007 you dont.

put iotd.name immediately before the exit for, and see which table you DO have.
 

Users who are viewing this thread

Top Bottom