Using Objects from one database in another database

[Josetta]

I've looked around here to see if this can be done, but haven't found
the answer. Here is what I want to do...

I have a two databases -- One for Sales and one for Operations. These
are used by two groups of people with some obvious overlap (like the
boss, for example). Some of the data is the same, so I'm using only
one backend database file to hold the data. However, I want to have
one piece (projects) from the Operations database to also be used in
the sales database. The projects piece of it consists of several
forms, subforms, reports, queries, code, etc.

To cut down on maintenance (so without actually importing) I want to
be able to use those forms, subforms, etc. in the Sales database,
exactly as they are in the Operations database.

Is there a way to do this so that any changes made in the Operations
database are also reflected in the Sales database? I've never created
Add-ins before, but before I go trying to make this happen, is this
the way I need to go? Would this even work? I guess what I'm saying
is I want to link code, forms, reports, etc. just like I can link
tables.

Any ideas?

 

[Aaron Kempf]

if you used Access Data Projects, then it would be quite easy to share data
in between databases

it would take ZERO code and ZERO effort

you just would need to add database.schema.object


schema is typcially 'dbo' for SQL 2000.. in SQL 2005; it'll break more into
a ROLE type.. like Accounting, HR, Recruiting, Manufacturing, etc


but seriously
make your life easier-- move to Access Data Projects

 

[Josetta]

Sorry, but this is not an option. I'm not even sure what Access Data
Projects is, but the database is far too advanced to make any sort of
switch.

 

[Aaron Kempf]

well I'm so sorry that you chose the wrong database

'its far too advanced'

gag me with a spoon.. I can convert _ANY_ MDB to a well tuned ADP solution
in a week or two

 

[Tony Toews [MVP]]

I have a two databases -- One for Sales and one for Operations. These
are used by two groups of people with some obvious overlap (like the
boss, for example). Some of the data is the same, so I'm using only
one backend database file to hold the data. However, I want to have
one piece (projects) from the Operations database to also be used in
the sales database. The projects piece of it consists of several
forms, subforms, reports, queries, code, etc.

To cut down on maintenance (so without actually importing) I want to
be able to use those forms, subforms, etc. in the Sales database,
exactly as they are in the Operations database.

Is there a way to do this so that any changes made in the Operations
database are also reflected in the Sales database? I've never created
Add-ins before, but before I go trying to make this happen, is this
the way I need to go? Would this even work? I guess what I'm saying
is I want to link code, forms, reports, etc. just like I can link
tables.

Using add-ins would work for your purpose. However they are
troublesome to work with. See my Add-in Tips, Hints and Gotchas
page at http://www.granite.ab.ca/access/addins.htm

Another option would be to merge the two databases. Then use the
network id to determine which set of forms and reports they can see.

API: Get Login name
http://www.mvps.org/access/api/api0008.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews [MVP]]

Sorry, but this is not an option. I'm not even sure what Access Data
Projects is, but the database is far too advanced to make any sort of
switch.

Aaron clearly didn't look at your question.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Aaron Kempf]

wtf are you talknig about kid

Is there a way to do this so that any changes made in the Operations
database are also reflected in the Sales database?


if someone changes the rows in the operations database-- this would be
immediately accessible from the Sales Database-- and it wouldn't run like
_CRAP_ over a network

keep all your tables and queries in one place-- where they belong-- SQL
SERVER

it's a much simpler architecture

 

[Aaron Kempf]

and that API is unnecessary

Select SUSER_SNAME() gives you the name of the current user in ADP

 

[Tony Toews [MVP]]

wtf are you talknig about kid

Reread the original question. It had nothing to do with tables or records.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews [MVP]]

and that API is unnecessary

Select SUSER_SNAME() gives you the name of the current user in ADP

And totally irrelevant in an MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Josetta]

How mature...

well I'm so sorry that you chose the wrong database

'its far too advanced'

gag me with a spoon.. I can convert _ANY_ MDB to a well tuned ADP solution
in a week or two







- Show quoted text -

 

[Josetta]

Tony,

Thanks for your suggestions and for reinterating to Aaron that,
whatever his agenda, he (probably) didn't read and certainly didn't
understand my issue.

Josetta

 

[Tony Toews [MVP]]

Thanks for your suggestions and for reinterating to Aaron that,
whatever his agenda, he (probably) didn't read and certainly didn't
understand my issue.

You are quite welcome.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm