I am designing a split database + I'm moving from one machine to another and
linked tables only support absolute addressing

So I wrote some code in the front end form to link the table in the back end
(one at the mo) which is always in the same directory

Private Sub Form_Load()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If (tbl.Attributes And dbSystemObject) = 0 _
Then tbl.Connect = ";DATABASE=" & CurrentProject.path &
"\JHP_be.mdb": tbl.RefreshLink
Next
..........
..........

I wrote this few years ago and it worked perfectly - I used the same code on
more recent database and it works most of the time but sometimes has trouble
connecting and I cant imagine why?

Anything obviously wrong with this code?

"Chris K" <(E-Mail Removed)> wrote in message
news:Q3Y8o.96437$Y21.42545@hurricane...
>I am designing a split database + I'm moving from one machine to another
>and linked tables only support absolute addressing
>
> So I wrote some code in the front end form to link the table in the back
> end (one at the mo) which is always in the same directory
>
> Private Sub Form_Load()
> Dim tbl As TableDef
> For Each tbl In CurrentDb.TableDefs
> If (tbl.Attributes And dbSystemObject) = 0 _
> Then tbl.Connect = ";DATABASE=" & CurrentProject.path &
> "\JHP_be.mdb": tbl.RefreshLink
> Next
> .........
> .........
>
> I wrote this few years ago and it worked perfectly - I used the same code
> on more recent database and it works most of the time but sometimes has
> trouble connecting and I cant imagine why?
>
> Anything obviously wrong with this code?

VBA sometimes gets a bit twitchy when you use single-line If...Then
statements broken over multiple lines. Try this:

Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If (tbl.Attributes And dbSystemObject) = 0 Then
tbl.Connect = ";DATABASE=" & CurrentProject.path & "\JHP_be.mdb"
tbl.RefreshLink
End If
Next

"Stuart McCall" <(E-Mail Removed)> wrote in message
news:TOY8o.145979$zo4.108868@hurricane...
> "Chris K" <(E-Mail Removed)> wrote in message
> news:Q3Y8o.96437$Y21.42545@hurricane...
>>I am designing a split database + I'm moving from one machine to another
>>and linked tables only support absolute addressing
>>
>> So I wrote some code in the front end form to link the table in the back
>> end (one at the mo) which is always in the same directory
>>
>> Private Sub Form_Load()
>> Dim tbl As TableDef
>> For Each tbl In CurrentDb.TableDefs
>> If (tbl.Attributes And dbSystemObject) = 0 _
>> Then tbl.Connect = ";DATABASE=" & CurrentProject.path &
>> "\JHP_be.mdb": tbl.RefreshLink
>> Next
>> .........
>> .........
>>
>> I wrote this few years ago and it worked perfectly - I used the same code
>> on more recent database and it works most of the time but sometimes has
>> trouble connecting and I cant imagine why?
>>
>> Anything obviously wrong with this code?
>
> VBA sometimes gets a bit twitchy when you use single-line If...Then
> statements broken over multiple lines. Try this:
>
> Dim tbl As TableDef
> For Each tbl In CurrentDb.TableDefs
> If (tbl.Attributes And dbSystemObject) = 0 Then
> tbl.Connect = ";DATABASE=" & CurrentProject.path & "\JHP_be.mdb"
> tbl.RefreshLink
> End If
> Next
>
>

Ill give that a try thanks