Auto Relinking Tables that are Part of a Relationship

[tmort]

I have a front end/back end database that is set to automatically refresh its links.

I had a couple of tables intially that did not need a defined relationship and the auto relink worked. Since that time I have decided to set a relationship between these two tables.

Now when it gets to the auto relink I get a message saying that I can't delete the table because it is participating in a relationship. The debugger points to the code:

objRelink.RelinkAllTables (sDBPath & "testdb_DATA.mdb")

Set objRelink = Nothing

Thanks

 

[Keith P]

Try RefreshLink mthod rather than relinking

 

[tmort]

Thanks, I'll try that.

Do you have any examples of how to use this code. Although I dive in to VB I really don't know the syntax or what I'm doing.

Thanks

 

[Keith P]

If you search for 'relink tables' on this forum or on the Microsoft KB you will find some good examples. If you really get stuck let me know.

 

[tmort]

Auto Refreshing

I'm using the example code from the "solutions"/"northwind" dbs and have some questions on how to adapt it. I'm not very familiar with VB and I thought I would post this to see if I am on the right track and if there are any syntax issues

I like the feature where it asks you to locate the database. I'm assuming that the database it is looking for is the backend.

Also, the following code is confusing to me. I'm not follwing what it is doing. Unlike the "solutions" database my database will not be in the same directory as Access.

' Get name of directory where MSAccess.exe is located.
strAccDir = SysCmd(acSysCmdAccessDir)

' Get the default sample database path.
If Dir(strAccDir & "Samples\.") = "" Then
strSearchPath = strAccDir
Else
strSearchPath = strAccDir & "Samples\"
End If

' Look for the Northwind database.
If (Dir(strSearchPath & "Northwind.mdb") <> "") Then
strFileName = strSearchPath & "Northwind.mdb"
Else
' Can't find Northwind, so display the Open dialog box.
MsgBox "Can't find linked tables in the Northwind database. " _
& "You must make sure the Northwind sample database is " _
& "installed. If Northwind is installed, you must locate " _
& "Northwind in order to use " _
& conAppTitle & ".", vbExclamation
strFileName = FindNorthwind(strSearchPath)
If strFileName = "" Then
strError = "Sorry, you must locate Northwind to open " & conAppTitle & "."
GoTo Exit_Failed
End If
End If

I'm thinking that is should be changed to something like the following where I just state the path that I would install it in.

' Get name of directory where MSAccess.exe is located.


' Get the default sample database path.

strSearchPath = "c:\mydbdirectory\mydatabasefrontend.mdb?"
[or whatever path]

' Look for the Northwind database.
If (Dir(strSearchPath & "Northwind.mdb") <> "") Then
strFileName = strSearchPath & "Northwind.mdb"
Else
' Can't find Northwind, so display the Open dialog box.
MsgBox "Can't find linked tables in the Northwind database. " _
& "You must make sure the Northwind sample database is " _
& "installed. If Northwind is installed, you must locate " _
& "Northwind in order to use " _
& conAppTitle & ".", vbExclamation
strFileName = FindNorthwind(strSearchPath)
If strFileName = "" Then
strError = "Sorry, you must locate Northwind to open " & conAppTitle & "."
GoTo Exit_Failed
End If
End If

 

[Keith P]

The approach I use is to test for the presence of the last table on application start e.g.start splash screen. The reason for using the last table is if a previous re-link failed earlier tables may be linked but not latter tables. If the table isn’t present prompt the user to locate your b/e db. I use the CommonDialog control which I believe comes with Access Developer versions. There is an API alternative, search forum if needed. When the path is located refresh the links.

Public Function CheckLinks() As Boolean
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
On Error Resume Next
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("LastTable", dbOpenDynaset)
If Err <> 0 Then ‘think error code (3204)
CheckLinks = False
Else
CheckLinks = True
End if
End Function

Public Function RefreshLinks(strFileName As String)
Dim indDb As DAO.Database
Dim tdf As TableDef
Set indDb = CurrentDb
For Each tdf In indDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
tdf.RefreshLink
End If
Next
End Function

 

[tmort]

Thanks,

Also, the database will likely be installed to different paths on different systems and I'm interested in getting the path of the backend from a procdure or function in the code rather than hardcoding the path.

I've been told that I can use the getsetting/savesetting to get the installed path and save any new path.

Do you know anything about this?

Thanks again

 

[Keith P]

currentdb.name gives you the full installation path. If you want just the folder then manipulate the string using instr, instrrev, mid etc.

The following function from the KB will give you the backend.
Public Function Get_Linked_DbName(tableName As String) As String

Dim myDb As DAO.Database, ret, dbName
On Error GoTo aErr
Set myDb = CurrentDb()
ret = myDb.TableDefs(tableName).Connect
Get_Linked_DbName = Right(ret, Len(ret) - (InStr(1, ret, "Database=") + 8))
aErrExit:
Exit Function
aErr:
Get_Linked_DbName = 0
Resume aErrExit

End Function

 

[tmort]

I've found what should be an easier way than parsing a string to get the path but I'm having trouble with it.

I found that there is a function called currentproject.path that returns the path of the current db.

However, when I inser the code I still find that it is not finding the backend and am prompted to find it.

The code as I am using it is below:

' Get name of directory where MSAccess.exe is located.
Rem strAccDir = SysCmd(acSysCmdAccessDir)

' Get the default IWCD database path.
Rem If Dir(strAccDir & "IWCDv6\.") = "" Then
Rem strSearchPath = strAccDir

Rem Else
Rem strSearchPath = strAccDir & "Samples\"

Rem New Code

Rem strSearchPath = "c:\IWCD\"

strSearchPath = "CurrentProject.Path"


Rem End If

It worked when I used strSearchPath = "c:\IWCD\" (when this was the installed directory of both), however when I use strSearchPath = "CurrentProject.Path" (with and without the quotes) it doesn't find it and I am prompted to locate the backend.

Any ideas? Thanks

 

[Keith P]

CurrentProject.Path will return the application folder, i.e. the front end location.

 

[tmort]

It's my intention to have the front end and backend in the same directory, although what this directory is will vary from installation to installation.

However, in the example I gave, if I specified this path it works but when I invoke the function it doesn't.

Any idea what I am doing wrong?

Thanks

 

[Keith P]

to find the b\e append the b\e name to the CurrentProject.Path e.g.


strSearchPath = CurrentProject.Path & "\myDb.mdb"

 

[Keith P]

Nothing wrong with having the f\e and b\e in the same folder unless at a later date you want to make it multi user

 

[tmort]

Thanks for all the help, but I'm still having difficulty,

I'm trying:

' Get name of directory where MSAccess.exe is located.
Rem strAccDir = SysCmd(acSysCmdAccessDir)

' Get the default IWCD database path.
Rem If Dir(strAccDir & "IWCDv6\.") = "" Then
Rem strSearchPath = strAccDir

Rem Else
Rem strSearchPath = strAccDir & "Samples\"

Rem New Code

Rem strSearchPath = "c:\IWCD\"

strSearchPath = strSearchPath = CurrentProject.Path & "\IWCDv6_DATA.mdb"


Rem End If

And it is not finding it, I'm still prompted to find it. I've also tried

CurrentProject.Path & "\IWCDv6.mdb"
CurrentProject.Path & "IWCDv6_DATA\"

???

 

[Keith P]

If you post the code you are using I'll have a look.