Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 6th, 2005, 03:37 AM
Authorized User
Join Date: Aug 2004
Location: , , Belgium.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Tough one !! xls as frontend with mdb as backend

Hi All,

I've been asked to develop a application were xls is the front (because of the flexibility, calculations, .... reasons) and all the xls data is stored in a access database.

I think the only way this is possible to store the xls sheet as XML file and import the whole thing in a memo field.

The questions I have is,
How can I be 100% sure that the sheet is stored in the correct record?

What I most need are your brains!!, please think together with me how this problem can be solved, which are the necessary steps,....
Possibilities, performance,...


Reply With Quote
  #2 (permalink)  
Old January 6th, 2005, 08:01 AM
Friend of Wrox
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

Excel is very flexible but has its limitation, look up in your version of excel help "Excel specifications and limits" this will give you a good idea on what your front end application can and will not do.

Jaime E. Maccou
Reply With Quote
  #3 (permalink)  
Old January 6th, 2005, 08:36 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Perhaps there is a better way to do this than using Excel as your front end. Why do you need to use Excel to enter and manipulate data? Why would you store the entire file as one field?

At a minimum you could make your forms in datasheet view, which is really not recommended. That looks like Excel.

Do you want your users to see calculations as they enter data? If so, you can do this with a form.

Usually people go the other way and use Access forms for their Excel spreadsheets (you need the Access add-in from Micrsoft, which never works right.)

Here is how I would do this, if I were to do this, and I wouldn't:

Create some Excel spreadsheets, then link them as tables to your Access database. As users make entries to the spreadsheets, their changes show up in the Access database. The spreadsheets would have to be permanent, and it is never a good idea to do data entry directly into your data store. And if they reformatted or entered data in the wrong cells, your screwed. You can have running calculations and other data show up in the Excel spreadsheet that you can duplicate in your database.

Now that I think of it, you could use an Excel template, and then have a start up procedure on your database that pulls data from your excel file and then clears it for the next crap shoot. I do this with a text file in one of my databases, but the data is put there by middleware, not a user who is prone to making mistakes.

I think the short answer is user training to get them to use Access. I am very worried about data validation and formatting, missed cells, etc. And I still can't figure out why you would store all your data in one field.

Reply With Quote
  #4 (permalink)  
Old January 6th, 2005, 10:05 AM
Authorized User
Join Date: Aug 2004
Location: , , Belgium.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts

The reason why I want to put all data in one field is quit simple, with this option the user is allowed to use the complete xls sheet where he can put comments, formulas, calculations, etc,....

If we save the file as "XML for excel", the complete xls sheet is formatted in the correct way, and can be easily re-used again....

from this xls sheet (which only contains raw calculations) I only need approx. 10 fields (figures ) in the database

Please reply your opinion

Reply With Quote
  #5 (permalink)  
Old January 7th, 2005, 05:03 AM
Registered User
Join Date: Jan 2005
Location: rijkevorsel, antwerpen, Belgium.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts



I never made a xls as form but is it not possible to work with ADODB (VBA)?
And work the same way as you make unbound forms in access.

That’s probably the thing I would do.


Reply With Quote
  #6 (permalink)  
Old January 7th, 2005, 11:01 AM
Authorized User
Join Date: May 2004
Location: Bucharest, , Romania.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts

I used ADODB to export data from one row (or more rows) in Excel as new records in Access, and the same way can be used to update data.
And also to import data from one (or more) Access records into Excel sheets.
But only for certain rows (or cells).

But how do you export or import an entire sheet as an OLE field?

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
.mdb to .xls or .csv conversion ramniwas Visual Basic 2005 Basics 5 April 14th, 2008 04:50 PM
Fatal Error - A tough one! gargamel BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 3 April 4th, 2007 04:25 PM
Access MDB with SQL Backend ashg657 Access 2 July 18th, 2006 12:54 PM
Compacting the Frontend Scripts82 Access VBA 0 February 18th, 2006 11:31 PM
tough code please help... xanderxvr Classic ASP Basics 2 January 27th, 2005 12:23 PM

All times are GMT -4. The time now is 09:21 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.