473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Excel as the GUI and Access the database

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
7 8487
NeoPa
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
1,418 Expert 1GB
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

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

Similar topics

12
by: jimserac | last post by:
I had previously posted this in an Access forum with negative results so will try here. Although this question specifies an Access database, I also wish to accomplish this with a large MS SQL...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
0
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS...
3
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown....
15
by: sparks | last post by:
We get more and more data done in excel and then they want it imported into access. The data is just stupid....values of 1 to 5 we get a lot of 0's ok that alright but 1-jan ? we get colums...
1
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access...
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
0
NeoPa
by: NeoPa | last post by:
Many of us have noticed that there are some very useful functions available to you when using Excel, but these same functions are not available, as standard, in Access. A particular issue I had...
0
by: Tony Hine | last post by:
Problem for Excel Developers One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets. MS Access is...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.