Excel as the GUI and Access the database

[SwordFish]

Hi All,

Im new here, Ive search thru the website and I cant exactly locate the above mentioned topic.

I need some help on the followings:-
How to have excel as my GUI and then Access acts as my database.
What I have now is a calculation application which is running on excel.
This application will capture numbers and text from user.
I intend to save the numbers & text that have been captured in excel in access.
The moment user key in the numbers in excel, it will be saved in access database. And It will have to support multiple user.

I greatly appreciate your help. Thanks...

[NeoPa]

This is a BIG question.
Check out the 'Data \ Get External Data \ New Database Query' menu item.
You can do all sorts of things...
Create a query and populate the cells with the results.
Optionally save the query to be refreshed at a later date.
This can all be done in VBA code too - not necessarily straightforward ly though.
If you want to use this you should study the area a bit beforehand.

[MMcCarthy]

Further to NeoPa's reply.

As a test to explore the options.

Import the spreadsheet into Access. Normalise the tables (see Links to useful sites thread at top of the Access forum if you don't know how). Create a new query to reflect the data you would like in the spreadsheet.

Now open a new excel spreadsheet and use the merge feature to populate from the Access query.

You now have a system to check out the features of what you want to do.

Hi All,

Im new here, Ive search thru the website and I cant exactly locate the above mentioned topic.

I need some help on the followings:-
How to have excel as my GUI and then Access acts as my database.
What I have now is a calculation application which is running on excel.
This application will capture numbers and text from user.
I intend to save the numbers & text that have been captured in excel in access.
The moment user key in the numbers in excel, it will be saved in access database. And It will have to support multiple user.

I greatly appreciate your help. Thanks...

[NeoPa]

Now open a new excel spreadsheet and use the merge feature to populate from the Access query.

What Merge feature?
Sounds interesting & I couldn't find it in help - Please provide more info.

[MMcCarthy]

What Merge feature?
Sounds interesting & I couldn't find it in help - Please provide more info.

Sorry that's how I refer to it rather than a technical description.

1. Open a new spreadsheet
2. On the menu bar go to Data - Get External Data - New Database Query
3. Select Ms Access Database* and click OK
4. Find the mdb you want to use in the window
5. Query wizard will open allowing you to build your own query based on the tables in the database. Finish the wizard by selecting the return data to excel option.
6. You can edit this query at any time by right clicking on the sheet.

Alternatively you can use any existing DSN data sources here.

Mary

[NeoPa]

Oh right - just a naming issue then.
I'm actually using that a lot more atm - and from VBA as well. Quite fun actually.

It's good to get that facility mentioned in here as well though.
Thanks.

-Adrian.

[MMcCarthy]

Oh right - just a naming issue then.
I'm actually using that a lot more atm - and from VBA as well. Quite fun actually.

It's good to get that facility mentioned in here as well though.
Thanks.

-Adrian.

Sorry Adrian.

Bad habit of mine. We all have our little ecentricities (SP?), sorry I'm too lazy to look it up.

Mary

[PEB]

It seems hard coding work does it worth?

Hi All,

Im new here, Ive search thru the website and I cant exactly locate the above mentioned topic.

I need some help on the followings:-
How to have excel as my GUI and then Access acts as my database.
What I have now is a calculation application which is running on excel.
This application will capture numbers and text from user.
I intend to save the numbers & text that have been captured in excel in access.
The moment user key in the numbers in excel, it will be saved in access database. And It will have to support multiple user.

I greatly appreciate your help. Thanks...