PDA

View Full Version : MS Excel as frontend & Access as Backend



Prasad_Joshi
09-19-2006, 03:10 AM
Hi Guys,

I want to use MS Excel as frontend & Access as Backend to keep the data. The data in Access table should be changed,deleted, very easily thru Excel & I should use all the facilities of Excel to generate the reports from the data.

Can anyone help me ?

Prasad Joshi.

ALe
09-19-2006, 03:32 AM
Hi Pradas welcome to vbax.

your request is a global project. have a look to this link as a starter

move data from excel to access
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acmthactTransferSpreadsheet_HV05186520.asp

execute query in access and write result to excel
http://vbaexpress.com/kb/getarticle.php?kb_id=889
http://vbaexpress.com/kb/getarticle.php?kb_id=662

Ken Puls
09-19-2006, 08:32 AM
I love doing this. :)

A couple of other links, from my site:

Retrieve Data From A Database To Excel Using SQL (http://www.excelguru.ca/node/23)
Export A Range Of Excel Data To A Database (http://www.excelguru.ca/node/18)
Populate Multi-Column Listbox With Data From Access (http://www.excelguru.ca/node/20)

and, just in case you need to:
Creating an Access Database (on the fly) Using VBA and SQL (http://www.excelguru.ca/node/60)

My personal preference is to use ADO to work with my access database. There are a couple of other options, such as MS Query and DAO (which is access focussed), but my experience all lies in the ADO area. It's a versatile platform which I understand ports easily to VB.Net should you need to go there.

One other REALLY important thing. I actually need to update the second link I gave you above (export to Access) for a recent discovery I made. The link will get you started, but ADO suports transaction processing. This means that you can send four (or whatever) transactions, and roll them back if they are not all successful in the update. It also comitts the data to the database immediately, so if you need to place a record and retrieve data right away, you don't miss it while the database "thinks" about updating.

I'm going to be updating the article on my site in the next couple of days, but thought I'd throw that out there.

HTH,

Norie
09-19-2006, 09:47 AM
Prasad

Couldn't you just create the reports in Access?

Ken Puls
09-19-2006, 01:42 PM
Norie,

I have built a couple of projects using Excel as a front end to an Access Database.

The first was an Excel 97 front end to an Access 2000 (mdb) database. We had no Access 2000 licenses, so this allowed us to avoid the Access program entirely as there are no license fees for simply using the file structure. In other words, creating reports in Access was not feasible.

I've also recently built an add-in to increase Excel's functionality a bit. (It will be released on my site in a few days.) It also uses an mdb database to store data and calls it back when needed. The purpose of this db is not to create reports, although if it were, I could not guarantee that a user would even have a license.

All I'm trying to say is that I can think of a host of reasons why the OP's question is perfectly valid and creating actual reports in Access would not be appropriate.

Norie
09-19-2006, 02:08 PM
Ken

I'm not saying that it isn't feasible, I'm just wondering why everything can't be done in Access.

Ken Puls
09-19-2006, 02:53 PM
Same reason everything can't be cooked in a microwave, I'm guessing. :)

For my uses, using an Excel front end with and Access database is the only feasible way to complete the task. Using Access alone wasn't. ;)

Aussiebear
09-20-2006, 01:57 AM
Ken, I am constantly trying to use Excel to do that which is mostly Access related. Not so much because work is primarily orientated with Excel but because Excel gives you a much better "Instant Gratification" view of that which you have just completed.

I look forward to the day when there is a "cross breed" program or options between Access and Excel.

I have often wondered since Excel is brilliant as a flat dimensional database, why when I see statements which refer to Excel having depth ( I think they mean multi sheet) why it cannot be used in a more relational database fashion. As in Columns and Rows and Sheets.

If we can have a single sheet with formatted layers, why not a data layer?

Ted

OBP
09-20-2006, 03:29 AM
I think everybody knows my feelings on using Excel for Access applications, as Norie says why not do it all in Access.
Taking Ken's point if Excel is all you have then I can understand this approach.

Norie
09-20-2006, 12:42 PM
Ken

I've no problem with using Excel and Access together, provided the project requires that or that is the best practice.

In fact I learned most of my Excel VBA when I had to use it to automate an Access database to create 40 different worksheets, with 2 charts for each sheet and 4 pivot tables each too.

The data in the Access database was financial data imported via text/csv files created by legacy systems.

And the data changed each month, and wasn't always in the same format.

Boy, was that fun.:)

Access was the best place to store/import the data but the best way to
present the data was using Excel.


Ted

Excel is a spreadsheet not a database.:)

I think there are other packages that will do the 'cross breed' thing, can't recall any names of the top of my head.

OBP

I've seen quite a lot of that in my time.

Best example was the BA (Business Analyst I think, but it might stand for something else) who had an Excel spreadsheet with code that did some sort of sorting/filtering.

The code took all day to run.
:jawdown:
The guy mysteriously :think: disappeared from the company, so I was asked to take a look.

After spending some time trying to decipher the 8 pages of code I just imported the data into Access, create a query, added a few criteria here and there, ran the query and hey presto, the same results in seconds.

Ken Puls
09-20-2006, 02:48 PM
I think everybody knows my feelings on using Excel for Access applications, as Norie says why not do it all in Access.
Taking Ken's point if Excel is all you have then I can understand this approach.

Don't get me wrong, here. There are apps I wouldn't bother doing this way. I have an inventory database here that I developed entirely in Access. (Those users do have licenses.) It works really well for it's purposes.

If I'm pulling report data though, I like the UI and flexibility of Excel. While Access can be used to create nice reports, it's a different skillset, and much more complicated to change for the average user. Excel's flexibility in manipulating data is purely awesome, and by far my preference.

As I think everyone agress, each project should be evaluated on it's on merrits and the appropriate program chosen. It's is important to remember though, as both Norie and I have described, there are reasons where you may want to use this approach, even if you do have access to both apps. :)

Ted, I can't see any reason why you couldn't use an excel sheet to store your database (providing it isn't over 65536 rows at this point). Excel does have some database type functions in it. Having said that, there are several considerations that you'd want to think seriously about. Just some are:
-Separating the program logic (code, reports, etc...) from you data. If you keep them all in the same workbook, how do you upgrade aspects?
-Access is, IMHO, much more efficient at storing large quantities of data. i.e. Excel workbooks tend to slow down when they fill up.
-Multiple user access is virtually impossible if all your data is in the same file as your logic.
So while you could do it, I'd stear clear of it myself.

Cheers,

Aussiebear
09-21-2006, 02:02 AM
Ken, when you have as little experience or knowlege as I have, dreams and reality sort of look the same.....

Aussiebear
09-22-2006, 02:24 AM
I'm told we have some sort of a Whizz Kid with Excel / Access, working in the Head OFfice who imports data from Progress to Access then shifts it across to Excel so he can use pivot tables etc.

He doesn't respond to request for help from "bottom of the food chain" employees like myself.... So I just content myself with the thought I'm the guy that's making him look fantastic....

Ken Puls
09-22-2006, 11:04 AM
LOL!

calmeraj
01-22-2013, 01:51 PM
i am unable to open the link .



i want to add single value for excel to MS access can any one help