ACCESS97, try to relink reference to another MDB

[lesperancer]

I've got two MDBs, that due to size, security, etc, need to be kept
separate
quotation
configurator (there will be more of these in the future)

so within quotation.mdb,
I linked all the tables that configurator.md b uses
I created a reference to configurator.md bm using full path name
I open a 'configurator' form, and away to work I go
now this is in a development world, and when we move the application to
production, I'd like to change all the configurator.md b references in
quotation.mdb to point to the production MDBs using

Public Sub linkReferences( )
Dim ref As Reference
Dim lngRef As Long
Dim strPath As String

On Error GoTo fErr
For lngRef = Application.Ref erences.Count To 1 Step -1
Set ref = Application.Ref erences(lngRef)
If (Left(ref.FullP ath, Len(devQuotrak) ) = devQuotrak) Then
If (applicationPat h <> devQuotrak) Then
strPath = applicationPath & Mid(ref.FullPat h,
Len(devQuotrak) + 1, 99)
Application.Ref erences.Remove ref
Application.Ref erences.AddFrom File strPath
End If
End If

Set ref = Nothing
Next lngRef

fExit:
On Error Resume Next
Set ref = Nothing
Exit Sub

fErr:
errorLog "linkReferences "
Resume fExit
End Sub


but when I hit the line ' Application.Ref erences.Remove ref', I get a
message
cant enter break mode at this time

and the 'remove' and 'add' does not happen

why this error ?
what else can I do ?

[polite person]

On 24 Mar 2006 09:01:54 -0800, le*********@nat pro.com wrote:

I've got two MDBs, that due to size, security, etc, need to be kept
separate
quotation
configurator (there will be more of these in the future)

so within quotation.mdb,
I linked all the tables that configurator.md b uses
I created a reference to configurator.md bm using full path name
I open a 'configurator' form, and away to work I go
now this is in a development world, and when we move the application to
production, I'd like to change all the configurator.md b references in
quotation.md b to point to the production MDBs using

Public Sub linkReferences( )
Dim ref As Reference
Dim lngRef As Long
Dim strPath As String

On Error GoTo fErr
For lngRef = Application.Ref erences.Count To 1 Step -1
Set ref = Application.Ref erences(lngRef)
If (Left(ref.FullP ath, Len(devQuotrak) ) = devQuotrak) Then
If (applicationPat h <> devQuotrak) Then
strPath = applicationPath & Mid(ref.FullPat h,
Len(devQuotrak ) + 1, 99)
Application.Ref erences.Remove ref
Application.Ref erences.AddFrom File strPath
End If
End If

Set ref = Nothing
Next lngRef

fExit:
On Error Resume Next
Set ref = Nothing
Exit Sub

fErr:
errorLog "linkReferences "
Resume fExit
End Sub


but when I hit the line ' Application.Ref erences.Remove ref', I get a
message
cant enter break mode at this time

and the 'remove' and 'add' does not happen

why this error ?
what else can I do ?

If these are linked tables you change the links via the CONNECT property.
For example

Public Function Relink(path1$, path2$)
Dim DB As DATABASE
Dim tdef As TableDef
Dim temp$, resp
Dim OldConnectStrin g$, NewConnectStrin g$
'takes all attachments to file (full) path1 and re-point them to path2
'fails if anything is missing

Set DB = CurrentDb

For Each tdef In DB.TableDefs
OldConnectStrin g = tdef.Connect
If Len("" & OldConnectStrin g) > 0 And InStr(1, OldConnectStrin g, ";DATABASE= ") Then
temp = InStr(OldConnec tString, path1)
If temp > 0 Then
NewConnectStrin g = str1tostr2once( OldConnectStrin g$, path1, path2)
tdef.Connect = NewConnectStrin g
tdef.RefreshLin k
End If
End If
Next tdef

End Function

Public Function str1tostr2once( f, A$, B$)
'because Access 97
Dim temp$, C$, i As Integer, la As Integer, lb As Integer
'in string F, replaces A by B first occurrence only

If IsNull(f) Then
str1tostr2once = ""
Exit Function
Else
temp$ = CStr(f)
la = Len(A$)
lb = Len(B$)
i = InStr(temp$, A$)
If i > 0 Then
temp$ = Left(temp$, i - 1) & B$ & Right(temp$, Len(temp$) - i - la + 1)
End If
str1tostr2once = temp$
End If

End Function

[lesperancer]

thanks for your input, but my function to change the path of the link
tables works fine, thats not the issue

my problem is the same as having outlook97 on one machine, and
outlook2000 on another machine - and not using late-binding when you
use outlook automation

when you move your application to the second machine, the reference to
outlook97 would be MISSING and you'd have to browse to the outlook2000

in my case, the reference is configurator.md b, but in production, it's
in a different folder than in development, causing the MISSING
reference

polite person wrote:

On 24 Mar 2006 09:01:54 -0800, le*********@nat pro.com wrote:

I've got two MDBs, that due to size, security, etc, need to be kept
separate
quotation
configurator (there will be more of these in the future)

so within quotation.mdb,
I linked all the tables that configurator.md b uses
I created a reference to configurator.md bm using full path name
I open a 'configurator' form, and away to work I go
now this is in a development world, and when we move the application to
production, I'd like to change all the configurator.md b references in
quotation.md b to point to the production MDBs using

Public Sub linkReferences( )
Dim ref As Reference
Dim lngRef As Long
Dim strPath As String

On Error GoTo fErr
For lngRef = Application.Ref erences.Count To 1 Step -1
Set ref = Application.Ref erences(lngRef)
If (Left(ref.FullP ath, Len(devQuotrak) ) = devQuotrak) Then
If (applicationPat h <> devQuotrak) Then
strPath = applicationPath & Mid(ref.FullPat h,
Len(devQuotrak ) + 1, 99)
Application.Ref erences.Remove ref
Application.Ref erences.AddFrom File strPath
End If
End If

Set ref = Nothing
Next lngRef

fExit:
On Error Resume Next
Set ref = Nothing
Exit Sub

fErr:
errorLog "linkReferences "
Resume fExit
End Sub


but when I hit the line ' Application.Ref erences.Remove ref', I get a
message
cant enter break mode at this time

and the 'remove' and 'add' does not happen

why this error ?
what else can I do ?

If these are linked tables you change the links via the CONNECT property.
For example

Public Function Relink(path1$, path2$)
Dim DB As DATABASE
Dim tdef As TableDef
Dim temp$, resp
Dim OldConnectStrin g$, NewConnectStrin g$
'takes all attachments to file (full) path1 and re-point them to path2
'fails if anything is missing

Set DB = CurrentDb

For Each tdef In DB.TableDefs
OldConnectStrin g = tdef.Connect
If Len("" & OldConnectStrin g) > 0 And InStr(1, OldConnectStrin g, ";DATABASE= ") Then
temp = InStr(OldConnec tString, path1)
If temp > 0 Then
NewConnectStrin g = str1tostr2once( OldConnectStrin g$, path1, path2)
tdef.Connect = NewConnectStrin g
tdef.RefreshLin k
End If
End If
Next tdef

End Function

Public Function str1tostr2once( f, A$, B$)
'because Access 97
Dim temp$, C$, i As Integer, la As Integer, lb As Integer
'in string F, replaces A by B first occurrence only

If IsNull(f) Then
str1tostr2once = ""
Exit Function
Else
temp$ = CStr(f)
la = Len(A$)
lb = Len(B$)
i = InStr(temp$, A$)
If i > 0 Then
temp$ = Left(temp$, i - 1) & B$ & Right(temp$, Len(temp$) - i - la + 1)
End If
str1tostr2once = temp$
End If

End Function

[polite person]

On 24 Mar 2006 15:25:17 -0800, le*********@nat pro.com wrote:

thanks for your input, but my function to change the path of the link
tables works fine, thats not the issue

my problem is the same as having outlook97 on one machine, and
outlook2000 on another machine - and not using late-binding when you
use outlook automation

when you move your application to the second machine, the reference to
outlook97 would be MISSING and you'd have to browse to the outlook2000

in my case, the reference is configurator.md b, but in production, it's
in a different folder than in development, causing the MISSING
reference

polite person wrote:

On 24 Mar 2006 09:01:54 -0800, le*********@nat pro.com wrote:

>I've got two MDBs, that due to size, security, etc, need to be kept
>separate
> quotation
> configurator (there will be more of these in the future)
>
>so within quotation.mdb,
> I linked all the tables that configurator.md b uses
> I created a reference to configurator.md bm using full path name
> I open a 'configurator' form, and away to work I go
>
>
>now this is in a development world, and when we move the application to
>production, I'd like to change all the configurator.md b references in
>quotation.md b to point to the production MDBs using
>
>Public Sub linkReferences( )
> Dim ref As Reference
> Dim lngRef As Long
> Dim strPath As String
>
> On Error GoTo fErr
> For lngRef = Application.Ref erences.Count To 1 Step -1
> Set ref = Application.Ref erences(lngRef)
> If (Left(ref.FullP ath, Len(devQuotrak) ) = devQuotrak) Then
> If (applicationPat h <> devQuotrak) Then
> strPath = applicationPath & Mid(ref.FullPat h,
>Len(devQuotrak ) + 1, 99)
> Application.Ref erences.Remove ref
> Application.Ref erences.AddFrom File strPath
> End If
> End If
>
> Set ref = Nothing
> Next lngRef
>
>fExit:
> On Error Resume Next
> Set ref = Nothing
> Exit Sub
>
>fErr:
> errorLog "linkReferences "
> Resume fExit
>End Sub
>
>
>
>
>but when I hit the line ' Application.Ref erences.Remove ref', I get a
>message
> cant enter break mode at this time
>
>and the 'remove' and 'add' does not happen
>
>why this error ?
>what else can I do ?

If these are linked tables you change the links via the CONNECT property.
For example

Public Function Relink(path1$, path2$)
Dim DB As DATABASE
Dim tdef As TableDef
Dim temp$, resp
Dim OldConnectStrin g$, NewConnectStrin g$
'takes all attachments to file (full) path1 and re-point them to path2
'fails if anything is missing

Set DB = CurrentDb

For Each tdef In DB.TableDefs
OldConnectStrin g = tdef.Connect
If Len("" & OldConnectStrin g) > 0 And InStr(1, OldConnectStrin g, ";DATABASE= ") Then
temp = InStr(OldConnec tString, path1)
If temp > 0 Then
NewConnectStrin g = str1tostr2once( OldConnectStrin g$, path1, path2)
tdef.Connect = NewConnectStrin g
tdef.RefreshLin k
End If
End If
Next tdef

End Function

Public Function str1tostr2once( f, A$, B$)
'because Access 97
Dim temp$, C$, i As Integer, la As Integer, lb As Integer
'in string F, replaces A by B first occurrence only

If IsNull(f) Then
str1tostr2once = ""
Exit Function
Else
temp$ = CStr(f)
la = Len(A$)
lb = Len(B$)
i = InStr(temp$, A$)
If i > 0 Then
temp$ = Left(temp$, i - 1) & B$ & Right(temp$, Len(temp$) - i - la + 1)
End If
str1tostr2once = temp$
End If

End Function

Well maybe if you didn't use references you wouldn't have the problem, but it's true that I don't
know what you are trying to do.

[Les Desser]

In article <11************ **********@v46g 2000cwv.googleg roups.com>,
le*********@nat pro.com Fri, 24 Mar 2006 09:01:54 writes

I'd like to change all the configurator.md b references in quotation.mdb to
point to the production MDBs using

Below is a part of the code we have used for years to do just what you
want.

In fact it links to two different mdbs depending on how the table name
starts.

'------------------------------------------------------------------------
------------
' Loop through all tables, re-attaching those with nonzero-length
Connect strings.

AT_Attch:
TableCount = 1 'Initialize TableCount for status meter.
For I = 0 To MaxTables
Set TableName = db.TableDefs(I)
DocName = db.TableDefs(I) .Name
If Left$(DocName, 13) <> "File: Contact" Then DBFile =
PMfilename Else DBFile = COfilename
' Only attach if needs attaching
If TableName.Conne ct <> "" _
And Left(DocName, 4) <> "MSys" _
And TableName.Conne ct <> ";DATABASE= " & DBFile _
Then
TableName.Conne ct = ";DATABASE= " & DBFile
Err = 0
TableName.Refre shLink
' Grant Authority to Group_01 - All users must belong to
Group_01
' This is necessary as re-attaching changes ownership of
attached tables to current user
' and existing authorities are lost.
ct.Documents(Do cName).UserName = "Group_01"
ct.Documents(Do cName).Permissi ons = SecVal
End If
TableCount = TableCount + 1
ReturnValue = SysCmd(acSysCmd UpdateMeter, TableCount)
If D < 20 Then D = D + 1 Else DoEvents: D = 0
Next I
'------------------------------------------------------------------------
------------

--
Les Desser
(The Reply-to address IS valid)

[lesperancer]

I'm trying to reference a secondary MDB within a primary MDB, like you
would reference Outlook or Excel within an MDB
so that I can keep forms / queries / links to tables / modules /etc
seperate

and I'd like some vba in the primary MDB to change the path of the
secondary MDB from the development copy to the production copy, the
code above fails

but now, it looks like the table links and queries, that a secondary
MDB form references, need to be in the primary MDB, so all I'm keeping
in the secondary MDB are forms/reports/modules... and I'm not sure it's
worth it

[Les Desser]

In article <11************ ********@j33g20 00cwa.googlegro ups.com>,
le*********@nat pro.com Mon, 27 Mar 2006 09:50:10 writes

I'm trying to reference a secondary MDB within a primary MDB, like you
would reference Outlook or Excel within an MDB
so that I can keep forms / queries / links to tables / modules /etc
seperate

and I'd like some vba in the primary MDB to change the path of the
secondary MDB from the development copy to the production copy, the
code above fails

but now, it looks like the table links and queries, that a secondary
MDB form references, need to be in the primary MDB, so all I'm keeping
in the secondary MDB are forms/reports/modules... and I'm not sure it's
worth it

Not quote sure of exactly your setup. The 'normal' setup is to have a
master DB containing forms and reports and all 'code' type objects -
i.e. those things that a developer may change.

The secondary DB contains the user's data. In this way the primary can
be changed while the user's data remains unchanged.

Thus the location of the secondary data DB is generally variable while
the master DB stays in a fixed directory wherever it may be running.

How does your setup differ, if at all?
--
Les Desser
(The Reply-to address IS valid)

[lesperancer]

I have two applications, both with a front-end and a back-end
I'd like to, if possible keep them seperate, but I also need to
integrate them
I know that from applicationA, I can open another ms-access session and
run applicationB

but I have to pass parameters from applicationA to applicationB and
back, so I thought, what if applicationA references applicationB (like
you would outlook to send emails)

then from applicationA, I can open an applicationB form and all would
work
the form opens, but as thought is was part of applicationA, so I need
to link all the applicationB back end tables into applicationA, not
hard

but now the queries that the applicationB form uses are accessible from
applicationA either, so I need to copy those to applicationA

so by the time I'm done, applicationA's front end has all the forms /
reports /etc
and applicationA's back end has all the tables

too bad, but it looks this won't work
I'm assuming that opening a second ms-access session and implementing a
parameter passing mechanism is too messy & prone to errors

Les Desser wrote:

In article <11************ ********@j33g20 00cwa.googlegro ups.com>,
le*********@nat pro.com Mon, 27 Mar 2006 09:50:10 writes

I'm trying to reference a secondary MDB within a primary MDB, like you
would reference Outlook or Excel within an MDB
so that I can keep forms / queries / links to tables / modules /etc
seperate

and I'd like some vba in the primary MDB to change the path of the
secondary MDB from the development copy to the production copy, the
code above fails

but now, it looks like the table links and queries, that a secondary
MDB form references, need to be in the primary MDB, so all I'm keeping
in the secondary MDB are forms/reports/modules... and I'm not sure it's
worth it

Not quote sure of exactly your setup. The 'normal' setup is to have a
master DB containing forms and reports and all 'code' type objects -
i.e. those things that a developer may change.

The secondary DB contains the user's data. In this way the primary can
be changed while the user's data remains unchanged.

Thus the location of the secondary data DB is generally variable while
the master DB stays in a fixed directory wherever it may be running.

How does your setup differ, if at all?
--
Les Desser
(The Reply-to address IS valid)

[Les Desser]

In article <11************ *********@z34g2 000cwc.googlegr oups.com>,
le*********@nat pro.com Tue, 28 Mar 2006 08:53:17 writes

I have two applications, both with a front-end and a back-end I'd like to,
if possible keep them seperate, but I also need to integrate them I know
that from applicationA, I can open another ms-access session and run
applicationB

but I have to pass parameters from applicationA to applicationB and
back, so I thought, what if applicationA references applicationB (like you
would outlook to send emails)

then from applicationA, I can open an applicationB form and all would
work the form opens, but as thought is was part of applicationA, so I
need to link all the applicationB back end tables into applicationA, not
hard

but now the queries that the applicationB form uses are accessible from
applicationA either, so I need to copy those to applicationA

so by the time I'm done, applicationA's front end has all the forms /
reports /etc
and applicationA's back end has all the tables

too bad, but it looks this won't work
I'm assuming that opening a second ms-access session and implementing
a parameter passing mechanism is too messy & prone to errors

Sorry but I still do not fully understand your requirement. Let me
describe what we have done as it sounds similar to what you seem to
want.

(My description is based on memory of when this was written many years
ago, so I hope I get it right)

We have (simplified somewhat as we actually have more than the two
applications below)

ContactD - Contacts database, data tables only
Contacts - Forms/Reports/Modules and links to tables in ContactD

ApplicationD - Data tables only
Application - Forms/Reports/Modules and links to tables in ApplicationD
Also links to some tables in ContactD as some Application
forms need direct access to the tables.

Application references Contacts so that it has access to modules and
global variables in Contacts. By calling functions and subs defined in
Contacts, we can load forms from Application that are actually in
Contacts (or at least it looks like they are loaded from there. In fact
you press a button in Application which calls a sub in Contacts which
loads a form in Contacts)

Also by using the global declarations in Contacts, Application and
Contacts can communicate parameters.

In our case there is no need for Contacts to directly reference tables
in ApplicationD so there are no links in that direction.

In some instances, Contacts does need to read tables in ApplicationD,
and we do that in code by directly opening tables from the ApplicationD
db.

To make all the above easier, we have an ini file that contains the
paths to ApplicationD and ContactD.

The name of the ini file is passed to Application when it opens via the
/ini command line parameter. Application extracts the paths and stores
them in global variables accessible by both Application and Contacts so
that either can freely open any of the data tables from within code.

The same global parameters are used to do the re-linking from within
Application and Contacts as they start up.
--
Les Desser
(The Reply-to address IS valid)

[Wayne Gillespie]

On 28 Mar 2006 08:53:17 -0800, le*********@nat pro.com wrote:

I have two applications, both with a front-end and a back-end
I'd like to, if possible keep them seperate, but I also need to
integrate them
I know that from applicationA, I can open another ms-access session and
run applicationB

but I have to pass parameters from applicationA to applicationB and
back, so I thought, what if applicationA references applicationB (like
you would outlook to send emails)

then from applicationA, I can open an applicationB form and all would
work
the form opens, but as thought is was part of applicationA, so I need
to link all the applicationB back end tables into applicationA, not
hard

but now the queries that the applicationB form uses are accessible from
applicationA either, so I need to copy those to applicationA

so by the time I'm done, applicationA's front end has all the forms /
reports /etc
and applicationA's back end has all the tables

too bad, but it looks this won't work
I'm assuming that opening a second ms-access session and implementing a
parameter passing mechanism is too messy & prone to errors

Have a look at TSISOON at -
http://www.trigeminal.com/lang/1033/...asp?ItemID=8#8

You may be able to use it to do what you want.
Wayne Gillespie
Gosford NSW Australia