Using Objects from another database

[Josetta]

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

[Tom van Stiphout]

On 24 Apr 2007 17:40:45 -0700, Josetta <Jo*****@smartdot.netwrote:

Indeed the term is "add-in". That's where you can put the common
functionality. Both apps would set a reference to this module, and are
then able to use it.

Personally I would rather create a single app, and use security to
fence off certain functionality the user may not need.

-Tom.

>I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

[engles]

On Apr 24, 5:40 pm, Josetta <[e-mail]:

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

I do this particular thing all the time. I don't use add-ins for this
particular functionality. Basically, keep the same front-end for all
back-ends and let the user switch backend databases (how you select
the new one is up to you. I use the common file dialog myself
usually), then cycle thru the tabledefs to define the new path to each
linked table. Here is a code snippet (this assumes an Access back-end
database):

For Each tdf In db.TableDefs
' If connect is blank, its not an Linked table
If Len(tdf.Connect) 0 Then
tdf.Connect = ";DATABASE=" & varFileName

' The RefreshLink might fail if the new path isn't OK. So
trap errors inline.
On Error Resume Next
tdf.RefreshLink

I don't remember off-hand if I wrote that code from scratch or adapted
it from somewhere else (like Getz, Litwin, et. al.). You will
probably need to adapt to your needs, but this should show you the
basic idea.
-- Larry Engles
Access developer since day 1 of Access 1.0

[Josetta]

Thanks for the suggestion. The boss would really like to keep them
separate and I am just trying to save myself some work down the road,
since he comes up with these crazy ideas continually. I am going to
look into this add-in business. It looks like this may be the way to
go in the future. For now, I just need to get it done.

Josetta

On Apr 24, 11:35 pm, Tom van Stiphout <[e-mail]:

On 24 Apr 2007 17:40:45 -0700, Josetta <[e-mail]:

Indeed the term is "add-in". That's where you can put the common
functionality. Both apps would set a reference to this module, and are
then able to use it.

Personally I would rather create a single app, and use security to
fence off certain functionality the user may not need.

-Tom.

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta- Hide quoted text -

- Show quoted text -

[Josetta]

Thanks for your reply. Hmm...this works fine with tables and I am
using this to make sure all my tables are linked properly. What I
need is to have this functionality with forms, reports and queries.

On Apr 26, 12:37 am, [e-mail] wrote:

On Apr 24, 5:40 pm, Josetta <[e-mail]:

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

I do this particular thing all the time. I don't use add-ins for this
particular functionality. Basically, keep the same front-end for all
back-ends and let the user switch backend databases (how you select
the new one is up to you. I use the common file dialog myself
usually), then cycle thru the tabledefs to define the new path to each
linked table. Here is a code snippet (this assumes an Access back-end
database):

For Each tdf In db.TableDefs
' If connect is blank, its not an Linked table
If Len(tdf.Connect) 0 Then
tdf.Connect = ";DATABASE=" & varFileName

' The RefreshLink might fail if the new path isn't OK. So
trap errors inline.
On Error Resume Next
tdf.RefreshLink

I don't remember off-hand if I wrote that code from scratch or adapted
it from somewhere else (like Getz, Litwin, et. al.). You will
probably need to adapt to your needs, but this should show you the
basic idea.

-- Larry Engles
Access developer since day 1 of Access 1.0- Hide quoted text -

- Show quoted text -

[Bruce]

On Apr 26, 11:20 am, Josetta <[e-mail]:

Thanks for your reply. Hmm...this works fine with tables and I am
using this to make sure all my tables are linked properly. What I
need is to have this functionality with forms, reports and queries.

On Apr 26, 12:37 am, [e-mail] wrote:

On Apr 24, 5:40 pm, Josetta <[e-mail]:

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

The approach Tom described earlier is the best approach to take, i.e.,
create a single front end database that houses all of the forms,
reports, queries, etc. for both applications and let the end user
either choose which 'application' they want to use (or force them into
one or the other based on their user id or other criteria). Once that
choice is made only make available those forms, reports, queries, etc.
that apply to the chosen application. You can easily share data
between different mdb files, and even code modules, but forms,
queries, and reports are another matter. It's best to keep those in
the same file unless you want the maintenance nightmare you've already
described.

Bruce

[engles]

On Apr 26, 9:20 am, Josetta <[e-mail]:

Thanks for your reply. Hmm...this works fine with tables and I am
using this to make sure all my tables are linked properly. What I
need is to have this functionality with forms, reports and queries.

On Apr 26, 12:37 am, [e-mail] wrote:

On Apr 24, 5:40 pm, Josetta <[e-mail]:

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

I do this particular thing all the time. I don't use add-ins for this
particular functionality. Basically, keep the same front-end for all
back-ends and let the user switch backend databases (how you select
the new one is up to you. I use the common file dialog myself
usually), then cycle thru the tabledefs to define the new path to each
linked table. Here is a code snippet (this assumes an Access back-end
database):

For Each tdf In db.TableDefs
' If connect is blank, its not an Linked table
If Len(tdf.Connect) 0 Then
tdf.Connect = ";DATABASE=" & varFileName

' The RefreshLink might fail if the new path isn't OK. So
trap errors inline.
On Error Resume Next
tdf.RefreshLink

I don't remember off-hand if I wrote that code from scratch or adapted
it from somewhere else (like Getz, Litwin, et. al.). You will
probably need to adapt to your needs, but this should show you the
basic idea.

-- Larry Engles
Access developer since day 1 of Access 1.0- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I guess I was not detailed enough. The variable "varFileName" is
complete path for a backend database. It could be a different
database than is currently linked to. This then would then link to a
different backend. If you feed it the same path and file as the
existing backend, all you are doing is refreshing the link.

Hope this sheds light on the subject. Since the front-end (the mdb
file with the forms, reports, queries, etc.) does not change, you are
using the exact same front-end to re-link to mulitple backends.
-- Larry Engles
Access developer since day 1 of Access 1.0

[lyle fairfield]

Josetta <Jo*****@smartdot.netwrote in news:1177461645.121679.261130
@b40g2000prd.googlegroups.com:

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right?

It could be done quite easily in Access 97 using the VB Attributes of the
objects. But it became more difficult in Access 2000. I wrote code to
accomplish this long ago but who knows where it might be now?

If you search Google Groups comp.databases.ms-access for ATTRIBUTE
VB_Exposed you should be able to find some interesting suggestions. Terry
Kreft may be the person most knowledgable about this matter.

Also, you might want to remember that old style MS-VB Forms !!!!MAY!!!! be
possible in modern versions of Access, although this is unlikely to help
with reports.

[Tom van Stiphout]

On 26 Apr 2007 09:15:16 -0700, Josetta <Jo*****@smartdot.netwrote:

One other way to simulate two apps with one is to borrow a trick from
the UNIX command line days. They would have utilities that were
several-in-one, and depending on the name they would behave
differently.
You can do a similar thing. Create a shortcut like this:
<path_to>msaccess.exe <path_to>my.mdb /x:AppOne
and another one:
<path_to>msaccess.exe <path_to>my.mdb /x:AppTwo

Then in the macro named AppOne you have:
RunCode Initialize("AppOne")
and in a macro named AppTwo you have:
RunCode Initialize("AppTwo")

The public function Initialize in a standard module would configure
the app. Perhaps:
Public Function Initialize(ByVal strMode as String)
if strMode = "AppOne" then
DoCmd OpenForm "MainMenuForAppOne"
elseif strMode = "AppTwo" then
DoCmd OpenForm "MainMenuForAppTwo"
else
Msgbox "You can only run this app from an approved shortcut"
End
end if

-Tom.

>Thanks for the suggestion. The boss would really like to keep them
separate and I am just trying to save myself some work down the road,
since he comes up with these crazy ideas continually. I am going to
look into this add-in business. It looks like this may be the way to
go in the future. For now, I just need to get it done.

Josetta

On Apr 24, 11:35 pm, Tom van Stiphout <[e-mail]:

>On 24 Apr 2007 17:40:45 -0700, Josetta <[e-mail]:

Indeed the term is "add-in". That's where you can put the common
functionality. Both apps would set a reference to this module, and are
then able to use it.

Personally I would rather create a single app, and use security to
fence off certain functionality the user may not need.

-Tom.

>I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

>Let's say we have two different company functions: Sales and
Operations

>The Operations database is considered the "main" one...we are using
this daily and it works very well.

>I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

>Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

>Maintenance is a nightmare on all this already.

>Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

>This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

>Thanks!

>Josetta- Hide quoted text -

- Show quoted text -

[Josetta]

Yes, I am finding this out. What I wanted to avoid was making changes
in both places... Thanks for your suggestion. I'm going to seriously
think about it.

On Apr 26, 4:19 pm, Bruce <[e-mail]:

On Apr 26, 11:20 am, Josetta <[e-mail]:

Thanks for your reply. Hmm...this works fine with tables and I am
using this to make sure all my tables are linked properly. What I
need is to have this functionality with forms, reports and queries.

On Apr 26, 12:37 am, [e-mail] wrote:

On Apr 24, 5:40 pm, Josetta <[e-mail]:

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta

The approach Tom described earlier is the best approach to take, i.e.,
create a single front end database that houses all of the forms,
reports, queries, etc. for both applications and let the end user
either choose which 'application' they want to use (or force them into
one or the other based on their user id or other criteria). Once that
choice is made only make available those forms, reports, queries, etc.
that apply to the chosen application. You can easily share data
between different mdb files, and even code modules, but forms,
queries, and reports are another matter. It's best to keep those in
the same file unless you want the maintenance nightmare you've already
described.

Bruce- Hide quoted text -

- Show quoted text -

[Josetta]

Thanks for the suggestion.

On Apr 26, 6:58 pm, lyle fairfield <[e-mail]:

Josetta <[e-mail] in news:1177461645.121679.261130
@b40g2000prd.googlegroups.com:

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right?

It could be done quite easily in Access 97 using the VB Attributes of the
objects. But it became more difficult in Access 2000. I wrote code to
accomplish this long ago but who knows where it might be now?

If you search Google Groups comp.databases.ms-access for ATTRIBUTE
VB_Exposed you should be able to find some interesting suggestions. Terry
Kreft may be the person most knowledgable about this matter.

Also, you might want to remember that old style MS-VB Forms !!!!MAY!!!! be
possible in modern versions of Access, although this is unlikely to help
with reports.

[Josetta]

This might work...Thanks for the suggestion.

On Apr 26, 11:54 pm, Tom van Stiphout <[e-mail]:

On 26 Apr 2007 09:15:16 -0700, Josetta <[e-mail]:

One other way to simulate two apps with one is to borrow a trick from
the UNIX command line days. They would have utilities that were
several-in-one, and depending on the name they would behave
differently.
You can do a similar thing. Create a shortcut like this:
<path_to>msaccess.exe <path_to>my.mdb /x:AppOne
and another one:
<path_to>msaccess.exe <path_to>my.mdb /x:AppTwo

Then in the macro named AppOne you have:
RunCode Initialize("AppOne")
and in a macro named AppTwo you have:
RunCode Initialize("AppTwo")

The public function Initialize in a standard module would configure
the app. Perhaps:
Public Function Initialize(ByVal strMode as String)
if strMode = "AppOne" then
DoCmd OpenForm "MainMenuForAppOne"
elseif strMode = "AppTwo" then
DoCmd OpenForm "MainMenuForAppTwo"
else
Msgbox "You can only run this app from an approved shortcut"
End
end if

-Tom.

Thanks for the suggestion. The boss would really like to keep them
separate and I am just trying to save myself some work down the road,
since he comes up with these crazy ideas continually. I am going to
look into this add-in business. It looks like this may be the way to
go in the future. For now, I just need to get it done.

Josetta

On Apr 24, 11:35 pm, Tom van Stiphout <[e-mail]:

On 24 Apr 2007 17:40:45 -0700, Josetta <[e-mail]:

Indeed the term is "add-in". That's where you can put the common
functionality. Both apps would set a reference to this module, and are
then able to use it.

Personally I would rather create a single app, and use security to
fence off certain functionality the user may not need.

-Tom.

I searched around a little bit, but didn't come up with the answer to
this question, but somewhere in the back of my mind, I think this can
be done...

Let's say we have two different company functions: Sales and
Operations

The Operations database is considered the "main" one...we are using
this daily and it works very well.

I've now created a new database, which basically tracks prospects and
manages clients and their ongoing projects, which is now considered a
"sales" function.

Because we're having sales followup with clients as well, I'd like the
forms, reports, and code regarding the projects to be exactly the same
in the new sales db as it is in the old db.

Maintenance is a nightmare on all this already.

Is there a way to NOT import, but have the forms, reports, etc. be
exactly the same in both databases (like a reference, I guess), so
that I only need to maintain one of them. In other words, if I make a
change to the form or report or code in the main database, that change
is reflected in the new database.

This can be done, right? I wanna say you do almost like an Add-in
thing, right? How can I get this to work? Any ideas?

Thanks!

Josetta- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -