Refresh Table Links Code

[Lowell]

I am trying to make a deployment package. Of course I have a split database and I want to have my tables automatically linked when it gets to the customers computer and is installed.

I have tried copying and pasting the code from here:



http://blogs.office.com/b/microsoft-...s-tables-.aspx

But it tells me that it doesn't work. And it doesn't!! When I test it on a computer without Access I get two messages: This file is a read only file, save this database to make design changes; and when I try to edit data: The file path: C:\%username&\Programs(x86)\recordkeeping_be.accdb is invalid. Make sure linked tables are on the same computer in the right folder.

This is the start of it:

Option Compare Database
Private Sub Form_Open(Cancel As Integer)
'----------------------------------------------------------------------------
' Procedure: RefreshTableLinks
' Purpose: Refresh table links to back-ends in the same folder as front end.
' Note: Linked Tables can be in more than one back-end.
' Return: Returns a zero-length string if all tables are relinked.
' Return: Or returns a string listing tables not relinked and errors.
'----------------------------------------------------------------------------

(MY WORDS HERE: a cursor comes here and a message box pops up that says Compile Error: Expected End Sub?????????????)

Public Function RefreshTableLinks() As String
On Error GoTo ErrHandle

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer

Set db = CurrentDb

'Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
'Verify the table is a linked table.
If Left$(tdf.Connect, 10) = ";DATABASE=" Then
'Get the existing Connection String.
strCon = Nz(tdf.Connect, "")


I never have understood coding. My skull is too thick. Can you clearly explain what I need to do?

[June7]

Take another look at the tutorial. You declare a Sub procedure then don't end it but go right into a Function declaration. Either delete the Function declaration and modify code to run directly in the Sub procedure or call the Function from the Sub:

Private Sub Form_Open(Cancel As Integer)
Dim strMsg As String
'Run the Procedure, getting any error messages.
strMsg = RefreshTableLinks()
'strMsg will be a zero-length string if there is no error message.
If Len(strMsg & "") = 0 Then
   Debug.Print "All Tables were successfully relinked."
Else
   'Notify the user of the errors.
   MsgBox strMsg, vbCritical
End If
End Sub

Public Function RefreshTableLinks() As String
....
End Function

However, I question a split design that has frontend and backend in the same folder. If this is a multi-user db, the frontend should be copied to each user's workstation and the table links should use UNC pathing.

[Lowell]

The only reason for a split is for updates down the road sometime. I am certain that there will be need for that.

It is only a single user program because there are a few organizations of a similar kind but that deal with different personal data because of different localities. The data is only for a the region in which the program user is using it.

The code worked so far.

Thanks a lot. I will now deploy and run another test on the testing computer. I will check back after that. I think there is another problem brewing but first we will deal with this.

Thanks for now.

[Lowell]

Well it worked on my developer computer but not after deployment. It has something to do with not being able to do design changes after making an install package with Access Runtime.

I have attached some screen shots for your information. Maybe you can help me trouble shoot this??

[June7]

I have never deployed for runtime, never converted to an accde. If can't programmatically set links because it is a design modification, then I don't have solution. Try Google search.

[Lowell]

Well I've spent the last 3 hours on Google and I found what I needed, sort of. But of course, the best I've found out is to use my own head which seems to be one of the better solutions when all else runs out and everyone can't get it figured out. I got it all myself.

All I did was tell the installer to put it in a different folder than I had been sending it to. I was sending it to Program Files (x86), but instead I sent it to My Documents folder and it works beauitully now. Thanks anyhow for trying. I will mark this as solved.