By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Excel as the GUI and Access the database

P: 1
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...
Nov 16 '06 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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 straightforwardly though.
If you want to use this you should study the area a bit beforehand.
Nov 16 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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...
Nov 16 '06 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 17 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 17 '06 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 17 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 17 '06 #7

PEB
Expert 100+
P: 1,418
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...
Nov 18 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.