ACCESS Question Using a Table from another Database

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,720
Were i work there is database that has several tables. But pretty much everything is based on one main table in that database. Most of the other tables are for Combo/listboxes sources data in the main table named QUOTES MASTER FILE.
The Owner will not allow anyone to touch the data, especially the QUOTES MASTER FILE table. This database was built years ago and by someone with very basic access development experience. The databases main purpose is to track information about Quotes. So the QUOTES MASTER FILE has a field called/titled “Quote Number”. There is a bunch of other fields in the table but that are all fields that have information about the “Quote Number”. New Quote Numbers, and information about the quote are entered into the data base daily.

My problem is I need to also track data about quotes. But I cannot add new fields to QUOTES MASTER FILE table.

How do I create a new table that will have the same list of Quote Numbers – even new ones when they are added?

Thanks
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

YesConsulting

New Member
Joined
Jan 7, 2019
Messages
11
Office Version
365
Platform
Windows, MacOS
There are many clarifications required before this can be responded to. A few that come to mind:
> You can attach the database, and have a link to the table in your Access database?

>Do you have control over the App that adds records into the Quote table?

> When you say "will not allow to touch the data", does that mean just this table, or the entire Access.mdb. I presume the data is split off from the UI elements?
> Is there a primary key with an Index?

> At what point to you collect the new data about the quotes? Is this done in the original application?

Assuming the best/easiest. Seems to me to add a table linked by Primary Key... with all your added data fields. It breaks relational form, but who cares.

Seems to me the real question is an application design one. What access to the app do you have at Quote.record add time?
If not but can attach the table in a separate app then you have a batch operation. Testing the Quote table for new records and filling in your new data into the related table accordingly.
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,720
I can’t relay do anything in the other Database with respect to design. I do not have admin rights. I can link the table into a separate database. And no the quote master file table does not have a primary key.

I don’t know when new quote numbers are added.


I am a limited in my Access skills so can you please elaborate on “batch operations”? And how you would go about testing the data and then adding the new data to my table in my separate database?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,627
Office Version
2013
Platform
Windows
It's always possible to load one table from another. Simplest is to simply empty the destination table and then insert all the records from the source table (but that can be time consuming if the table is large - might take seconds or minutes rather than milliseconds). That would also be an example of a batch operation.


...I can link the table into a separate database..
Note that if you can link to the table then you just link and you are done - all the records are available for you.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,480
Messages
5,444,733
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top