Best way to Update Master Database - via Spreadsheet - sent to sites

[ImraneA]

Hi there

Jest problem :-

1.We have a Supplier database using MS Access.
2.Need to populate data from 4 different sites - different
applications used.
E.G. One site may have MS Access, someone would update spreadsheet we
sent, comes back, we update master database.

3.One off event hopefully.

Data structure at Main Site:-
A.3 Tables (1st table - Supplier details, 2nd table - QA details, 3rd
table - Product details)

Catch :-
1.Each company must use our generic list for products i.e. consists of
Code + description.

CODE NAME
CH00 Chemicals - Undefined
EL01 Electrical. - Cable LV/MV/HV
EL02 Electrical. - Cathodic Prot. System

Idea :-
Use spreadsheet, with 3 sheets
1.1st sheet enter supplier deatils
2.2nd sheet - select supplier from say drop down (based on 1st sheet),
select code from drop down (product code)
3.3rd sheet - select supplier from say drop down (based on 1st sheet),
enter Quality Assurance details.

N.B. Access \SQL Developer - not done Excel VBA within Excel.

Key point :-

3 sheets will have common identifer, e.g. Supplier No, Reference. It
means I can import data in - without any hassle , using scripting,
etc.. into Access db.

Do u follow?

Qn:What do u think of my idea ? - is there a better way? - how do I do
it ?

Thanks in advance.

 

[K Dales]

-----Original Message-----
Hi there

Jest problem :-

1.We have a Supplier database using MS Access.
2.Need to populate data from 4 different sites - different
applications used.
E.G. One site may have MS Access, someone would update spreadsheet we
sent, comes back, we update master database.

3.One off event hopefully.

Data structure at Main Site:-
A.3 Tables (1st table - Supplier details, 2nd table - QA details, 3rd
table - Product details)

Catch :-
1.Each company must use our generic list for products i.e. consists of
Code + description.

CODE NAME
CH00 Chemicals - Undefined
EL01 Electrical. - Cable LV/MV/HV
EL02 Electrical. - Cathodic Prot. System

Idea :-
Use spreadsheet, with 3 sheets
1.1st sheet enter supplier deatils
2.2nd sheet - select supplier from say drop down (based on 1st sheet),
select code from drop down (product code)
3.3rd sheet - select supplier from say drop down (based on 1st sheet),
enter Quality Assurance details.

N.B. Access \SQL Developer - not done Excel VBA within Excel.

Key point :-

3 sheets will have common identifer, e.g. Supplier No, Reference. It
means I can import data in - without any hassle , using scripting,
etc.. into Access db.

Do u follow?

Qn:What do u think of my idea ? - is there a better way? - how do I do
it ?

Thanks in advance.
.

Idea will work - a little cumbersome but not bad if you do
not have Access available at sites where data needs to be
entered.

Better than scripting: use a database query to duplicate
the three Access tables on your Excel sheets (from menus,
Data... Get External Data... New Database Query). Then
have the spreadsheet either update the tables or create a
new table (with input data) and use the Get External
Data... Import option of Access to bring the tables back
in (with new names) - then do update and/or append queries
to get the info integrated into your main Access DB.

Hope this brief summary makes sense - if you have
questions you can try contacting me at kgdcc(NOSPAM)
@westelcom.com

 

[Imrane Akram]

Thanks for comments