By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,863 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,863 IT Pros & Developers. It's quick & easy.

ACCESS97, try to relink reference to another MDB

P: n/a
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.mdb uses
I created a reference to configurator.mdbm 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.mdb 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.References.Count To 1 Step -1
Set ref = Application.References(lngRef)
If (Left(ref.FullPath, Len(devQuotrak)) = devQuotrak) Then
If (applicationPath <> devQuotrak) Then
strPath = applicationPath & Mid(ref.FullPath,
Len(devQuotrak) + 1, 99)
Application.References.Remove ref
Application.References.AddFromFile 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.References.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 ?

Mar 24 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
On 24 Mar 2006 09:01:54 -0800, le*********@natpro.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.mdb uses
I created a reference to configurator.mdbm 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.mdb 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.References.Count To 1 Step -1
Set ref = Application.References(lngRef)
If (Left(ref.FullPath, Len(devQuotrak)) = devQuotrak) Then
If (applicationPath <> devQuotrak) Then
strPath = applicationPath & Mid(ref.FullPath,
Len(devQuotrak) + 1, 99)
Application.References.Remove ref
Application.References.AddFromFile 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.References.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 OldConnectString$, NewConnectString$
'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
OldConnectString = tdef.Connect
If Len("" & OldConnectString) > 0 And InStr(1, OldConnectString, ";DATABASE=") Then
temp = InStr(OldConnectString, path1)
If temp > 0 Then
NewConnectString = str1tostr2once(OldConnectString$, path1, path2)
tdef.Connect = NewConnectString
tdef.RefreshLink
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
Mar 24 '06 #2

P: n/a
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.mdb, 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*********@natpro.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.mdb uses
I created a reference to configurator.mdbm 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.mdb 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.References.Count To 1 Step -1
Set ref = Application.References(lngRef)
If (Left(ref.FullPath, Len(devQuotrak)) = devQuotrak) Then
If (applicationPath <> devQuotrak) Then
strPath = applicationPath & Mid(ref.FullPath,
Len(devQuotrak) + 1, 99)
Application.References.Remove ref
Application.References.AddFromFile 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.References.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 OldConnectString$, NewConnectString$
'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
OldConnectString = tdef.Connect
If Len("" & OldConnectString) > 0 And InStr(1, OldConnectString, ";DATABASE=") Then
temp = InStr(OldConnectString, path1)
If temp > 0 Then
NewConnectString = str1tostr2once(OldConnectString$, path1, path2)
tdef.Connect = NewConnectString
tdef.RefreshLink
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


Mar 24 '06 #3

P: n/a
On 24 Mar 2006 15:25:17 -0800, le*********@natpro.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.mdb, 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*********@natpro.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.mdb uses
> I created a reference to configurator.mdbm 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.mdb 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.References.Count To 1 Step -1
> Set ref = Application.References(lngRef)
> If (Left(ref.FullPath, Len(devQuotrak)) = devQuotrak) Then
> If (applicationPath <> devQuotrak) Then
> strPath = applicationPath & Mid(ref.FullPath,
>Len(devQuotrak) + 1, 99)
> Application.References.Remove ref
> Application.References.AddFromFile 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.References.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 OldConnectString$, NewConnectString$
'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
OldConnectString = tdef.Connect
If Len("" & OldConnectString) > 0 And InStr(1, OldConnectString, ";DATABASE=") Then
temp = InStr(OldConnectString, path1)
If temp > 0 Then
NewConnectString = str1tostr2once(OldConnectString$, path1, path2)
tdef.Connect = NewConnectString
tdef.RefreshLink
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.

Mar 25 '06 #4

P: n/a
In article <11**********************@v46g2000cwv.googlegroups .com>,
le*********@natpro.com Fri, 24 Mar 2006 09:01:54 writes
I'd like to change all the configurator.mdb 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.Connect <> "" _
And Left(DocName, 4) <> "MSys" _
And TableName.Connect <> ";DATABASE=" & DBFile _
Then
TableName.Connect = ";DATABASE=" & DBFile
Err = 0
TableName.RefreshLink
' 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(DocName).UserName = "Group_01"
ct.Documents(DocName).Permissions = SecVal
End If
TableCount = TableCount + 1
ReturnValue = SysCmd(acSysCmdUpdateMeter, TableCount)
If D < 20 Then D = D + 1 Else DoEvents: D = 0
Next I
'------------------------------------------------------------------------
------------

--
Les Desser
(The Reply-to address IS valid)
Mar 27 '06 #5

P: n/a
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

Mar 27 '06 #6

P: n/a
In article <11********************@j33g2000cwa.googlegroups.c om>,
le*********@natpro.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)
Mar 27 '06 #7

P: n/a
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********************@j33g2000cwa.googlegroups.c om>,
le*********@natpro.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)


Mar 28 '06 #8

P: n/a
In article <11*********************@z34g2000cwc.googlegroups. com>,
le*********@natpro.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)
Mar 28 '06 #9

P: n/a
On 28 Mar 2006 08:53:17 -0800, le*********@natpro.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
Mar 29 '06 #10

P: n/a
yup... that's what I want to do
using an ini file is great idea

how do you deal with a contacts form, that is based on a query (says
qryContacts) that exists in contacts.mdb ?
when application.mdb opens the contacts form via the sub, it won't see
the qryContacts query, it looks to me like all recordsources for
contacts.mdb must exist in application.mdb, no ?

Mar 29 '06 #11

P: n/a
In article <11**********************@g10g2000cwb.googlegroups .com>,
le*********@natpro.com Wed, 29 Mar 2006 01:54:47 writes
yup... that's what I want to do
using an ini file is great idea

how do you deal with a contacts form, that is based on a query (says
qryContacts) that exists in contacts.mdb ?
when application.mdb opens the contacts form via the sub, it won't see
the qryContacts query, it looks to me like all recordsources for
contacts.mdb must exist in application.mdb, no ?

Our Contacts db (plus the back end) is totally self contained and can be
run on its own.

So all forms, reports, etc for the application are there.

From Application all I do is call a sub that resides in the referenced
Contacts mdb and that sub loads the local main menu (from within
Contacts) and from that point on the user is working within Contacts -
no need for anything in Application.

We do actually link to some of the tables in ContactD from Application
but not to all of them. For example, as name/address/phone details are
in ContactD but we need them in Application forms/reports, there are
links from Application to ContactD for those tables.

All the linking is done using the values in the ini file. To determine
whether to link to ApplicationD or ContactD we use a table naming
convention that identifies links in Application that belong in ContactD
--
Les Desser
(The Reply-to address IS valid)
Mar 29 '06 #12

P: n/a
so to use your example below,

my application.mdb calls a public function called customerExists which
resides within the referenced contacts.mdb

Public Function customerExists(lngCustomerId As Long) As Boolean
Dim intCount As Integer

intCount = DCount("customerId", "tblCustomer", "customerId = " &
lngCustomerId)
quoteExists = (intCount > 0)
End Function
the call within application.mdb looks like this
customerExists = contacts.customerExists(lngCustomerId)

and I get an error on the "DCount.." line indicating that it cannot
find the tblCustomer table, which exists in contacts.mdb, but not
within application.mdb

so if I want contacts.mdb to be self contained, it looks like I need to
open it with another ms-access session using

CreateObject("Access.Application")

or is there another way ?
Les Desser wrote:
In article <11**********************@g10g2000cwb.googlegroups .com>,
le*********@natpro.com Wed, 29 Mar 2006 01:54:47 writes
yup... that's what I want to do
using an ini file is great idea

how do you deal with a contacts form, that is based on a query (says
qryContacts) that exists in contacts.mdb ?
when application.mdb opens the contacts form via the sub, it won't see
the qryContacts query, it looks to me like all recordsources for
contacts.mdb must exist in application.mdb, no ?

Our Contacts db (plus the back end) is totally self contained and can be
run on its own.

So all forms, reports, etc for the application are there.

From Application all I do is call a sub that resides in the referenced
Contacts mdb and that sub loads the local main menu (from within
Contacts) and from that point on the user is working within Contacts -
no need for anything in Application.

We do actually link to some of the tables in ContactD from Application
but not to all of them. For example, as name/address/phone details are
in ContactD but we need them in Application forms/reports, there are
links from Application to ContactD for those tables.

All the linking is done using the values in the ini file. To determine
whether to link to ApplicationD or ContactD we use a table naming
convention that identifies links in Application that belong in ContactD
--
Les Desser
(The Reply-to address IS valid)


Apr 4 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.