Guidance with Transitioning from Excel to Access

Question

I am contemplating a consolidation of my existing work records methods and would appreciate guidance on whether what I'm contemplating is practicable.

Currently I have two Excel workbooks, one for each of two work streams, that each hold all the details of individual jobs. I have folders for .pdf copies of all documents. I have a domain name and an external email host, with folders for each individual job that have a hierarchical structure identical to the .pdf folder hierarchy. The system is well structured but it means that details for jobs are in three different places.

I have long known that Excel, whilst convenient, was not the ideal tool for the job and that the system needed a database, not a spreadsheet. I am now planning to make that transition. I developed a few databases many years ago and have forgotten much of what I previously knew, so I would much appreciate answers to the following:

• My Excel workbooks have worksheets that simulate external forms; these forms are populated by using VLOOKUP to extract data from the main data sheet. The forms took many, many hours to develop and are critical to my work. Can they be imported or readily duplicated in Access?
• Can Access be used to hold .pdf files or, if they can't be held within Access, can the .pdf files be accessed from an Access dashboard?
• Similarly with emails - can emails be accessed from an Access dashboard? From a quick read of the Access help file, it appears that EmailDatabaseObject may enable me to prepare emails that contain the forms referred to above, which will be a definite benefit.

I don't care how long it takes me to develop the integrated system. It's taken me years to get it to where it currently is and I'm up for the challenge of developing the knowledge needed to make the proposed transition, provided that what I'm aiming at is practicable. Even without the level of integration that I'm hoping for, just converting the two workbooks to a database will provide immense benefits and I'm trusting that transitioning the forms from Excel to Access will prove to be possible, so I'm already defining the various use cases and starting preliminary modelling.

Answer 1

Just grab a good book..write down what you need...start making some small samples and off you go.

Answer 2

I have a domain name and an external email host

Domain name implies you want to create a website. Typically you get an email server when you pay for hosting for a website. That however is a separate topic, as best as I can tell none of the rest of what you are asking is relevant to the website.

My Excel workbooks have worksheets that simulate external forms; these forms are populated by using VLOOKUP to extract data from the main data sheet. The forms took many, many hours to develop and are critical to my work. Can they be imported or readily duplicated in Access?

Somewhat at least. However for the long-term solution it will be better to not use the existing forms. Except they are important for understanding how to make the new forms (the design). I think that once you have converted to a database you will understand that the UI (form) design is the hard part; the database will do most of the work. That will probably be your situation.

Can Access be used to hold .pdf files or, if they can't be held within Access, can the .pdf files be accessed from an Access dashboard?

It depends. Yes you can put PDF files in a database as BLOBs. An alternative would be to put just file names in the database and then the PDF files can be accessed as files. Both alternatives have advantages and disadvantages.

Similarly with emails - can emails be accessed from an Access dashboard? From a quick read of the Access help file, it appears that EmailDatabaseObject may enable me to prepare emails that contain the forms referred to above, which will be a definite benefit.

I don't know what you need to access the emails for and I do not know how you are currently accessing them and I do not know what your email client is.

All these questions are very general and it would be appropriate to ask many separate questions for details. So I will not get into the details.

First let us back up and go up to a higher level. It might be better to not use Access. You can use C# or VB.Net and some other database. SQL Server is a good choice but SQLite is provided with current versions of Windows 10; Windows uses SQLite (for some things at least).

Whether you use Access, SQL Server or SQLite, if you use C# or VB.Net then you can use Entity Framework (also see Overview - EF Core). One little detail about EF that might be confusing is that Microsoft is in transition from an original version of EF (Entity Framework 6) to a newer version (Entity Framework Core). There are new features in EF Core and there are features that are in EF 6 that are not yet in EF Core. You should probably go for EF Core and any books or tutorials about EF will generally apply to both.

I don't care how long it takes me to develop the integrated system. It's taken me years to get it to where it currently is and I'm up for the challenge of developing the knowledge needed to make the proposed transition, provided that what I'm aiming at is practicable.

Good. And the best goal is to use C#/VB.Net with EF as above.

The first thing is to design the database. That is useful regardless of any of the preceding; it is as useful if you use Access as it is for any of the other possibilities. That is where a book might help; just database design is important. I think the Database Answers website can help; there are many sample data models there that might spark your imagination. Also see Database Solutions & Downloads for Microsoft Access | databasedev.co.uk; it is specific to Access but it probably is useful if you choose to use a different database. I do not know what book to recommend.

---

Sam Hobbs
SimpleSamples.Info

Answer 3

• My Excel workbooks have worksheets that simulate external forms; these forms are populated by using VLOOKUP to extract data from the main data sheet. The forms took many, many hours to develop and are critical to my work. Can they be imported or readily duplicated in Access?
  
  They can't be copied over, but they can be recreated in Access and the same behavior replicated.
  
• Can Access be used to hold .pdf files or, if they can't be held within Access, can the .pdf files be accessed from an Access dashboard?
  
  Yes/No, look over http://www.devhut.net/2016/10/03/adding-attachments-to-an-access-database/
  
• Similarly with emails - can emails be accessed from an Access dashboard? From a quick read of the Access help file, it appears that EmailDatabaseObject may enable me to prepare emails that contain the forms referred to above, which will be a definite benefit.
  
  You can use automation to create/send e-mails and other retrieve existing ones.  You may like to look over http://www.devhut.net/2018/02/16/ms-access-how-to-send-an-email/ , http://www.devhut.net/2019/08/20/vba-retrieve-an-outlook-message-item/

Here are a few general information links you may like to review

• http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/
• http://www.devhut.net/2012/12/04/ms-access-where-start-learning-database-tutorials/

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

Advantage of moving from Excel to Access: You’re still in the Microsoft Office environment, so you’re familiar with it. This translates into less work. Even though time is not a consideration, time is always a consideration. Moving data between office apps is formalized by the exposed object models of the apps. You’re not going to stop using Excel.

You’re probably somewhat familiar with VBA.

Microsoft has two versions now, 365 (a subscription) and 2019 (one-time purchase). When I looked at the subscription plan, I decided I would do better with 2019.

If your Forms are filled using VLookup, my guess is you’ll get a cleaner fill by using Queries. Access provides a slick query-writing tool; that’ll postpone the need to learn SQL for a bit.

You’ll need to learn to think in sets.

I haven’t used Access for email.

While Access editions come out every few years, the basics don’t change that much. So whatever version you have or get, older books still apply. The new editions of Access incorporate things in tables like lookups, calculated fields, and attachments, all of which should be avoided. You can probably do well w/ books from Access 2010.

This forum is quite valuable, too.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 5

Yes, time is a consideration and if you, DaviDWF2, convert to Access and then, for some reason realize the benefits of the more powerful and productive features, then much of the time learning Access will be wasted. If you never advance beyond Access then it is a waste of time learning the more complicated technologies. But the complicated technologies can save time after they have been learned.

Note that Microsoft does not support use of Office in websites; see Considerations for server-side Automation of Office. Also SQLite would be inappropriate for websites. If however you use Entity Framework then it will be easier to convert the database, whether you use SQL Server, Access, SQLite or MySQL.

---

Sam Hobbs
SimpleSamples.Info

Answer 6

My grateful thanks for the very helpful guidance, comments and links.

Yes Sam, my queries were very general as at this stage I'm looking at the overall concept. What I have now is workable but is far from ideal and I know that a database will overcome many of the shortcomings; I just want to be sure that what I'm contemplating is realistic and achievable.

There are no current plans for a website; I'm savage on our ISP who recently managed to delete around 8,000 of our emails from their server so I immediately moved to an external email host, hence the domain name. It also enabled us to now have a more meaningful email address.

I'm somewhat inclined to stay with Access as I already have some familiarity with it, albeit dated. I have several texts on Access, all of them referring to Access 95 or Access 2.0. which really dates me! However, as you noted Peter, the basics haven't changed much so I have useful resources immediately at hand. I have progressed a little though - I'm now using Office 2013. That said, Sam I do appreciate your point about other more powerful and productive methods and in fact I've already installed MySQL in order to get a feel for it. I will definitely have a look at Entity Framework. One consideration though is that I'm unlikely to develop any databases beyond this current proposed one, so I do need to weigh up the amount of learning time versus the time to be spent applying that learning.

Yes, I recall from the databases that I developed years ago that designing the input and output forms involved a lot of work, hence my interest in being able to utilize the forms that I've created in Excel. Our work involves extensive use of third-party forms, which my Excel forms simulate. I've just been looking at the various sites that these forms originate from and I see that most, if not all, are now available in either Word or .pdf format, where previously I had to obtain paper copies and from these create the Excel forms I currently use. Assuming that I stay with Access, does this simplify it? Can Access incorporate Word or .pdf forms as a template which can then be populated with objects from Access?

And yes Peter, I am somewhat familiar with VBA, or at least the Excel flavour of VBA; you've previously guided me on specific problems in developing my Excel workbooks.

So - armed with the new resources you've collectively pointed me to, together with my current resources ("Beginning Database Design" being a favourite), I'll continue on with careful development of the use cases, database design and preliminary data modelling, then once I'm satisfied with the design I'll start the construction phase. I've no doubt that I'll appear here regularly in the months ahead.

Again, my thanks to each of you for taking the time to respond; it really is very much appreciated.

David

(PS - I'm not sure whether it's possible to mark each reply as Answer but I'm about to try. If yours doesn't get marked as the Answer then it's because of the system, not because your response was less helpful).

Answer 7

I will definitely have a look at Entity Framework.

You cannot use Entity Framework from VBA. However if you were to use it with VB.Net then you can use the data in the Access database(s) alongside the VBA for Access.

As for email, I am different from many people. I don't like having my email in servers. I download (using POP3; that is, a local email client) all my email. However any company that loses 8,000 emails should not be in business, I assume they lost your business.

---

Sam Hobbs
SimpleSamples.Info

Answer 8

At this stage Sam they've only lost the email. I'm contractually locked into them for now as my ISP but once that's complete I'm gone. Not even an apology from them.

Answer 9

My suggestion of getting a good book and familiarize with Access while less helpful it is the very first step...Others here shared a lot of helpful information but unless you get to certain level they will remain just "helpful information"... As you progress with you learning you will find that Access and Excel share a lot in common but they have a fundamental difference...Access is a database, while Excel is a spreadsheet...at first this might be just an exaggeration... especially when you have created a large table and you have filled it for the 1st time...well it looks like an Excel spreadsheet... isn't it.?

Well forget about the spreadsheet ...enter the world of databases and you will learn several interesting things...the No1 is normalization...design Good tables .. because this will be your foundation...it's like a building..if it has weak problematic foundation ..shortly it will collapse spectacularly..if it has good solid ones..the building will keep growing and growing...

For all your other questions...the simple answer would be that Access can do just about everything..from PDF handling ...email handling.. searching..you name it....as I like to say to everyone that asks : " Can I do it in Access?"....my answer is " Access can do everything but Games" 

Answer 10

Thanks John - that's sage advice.

Don't worry, I'm an avid reader and I well understand what you're saying about establishing the foundation. As I noted, many years ago I developed a few databases and whilst they did what I wanted, they were not as well designed as they should have been.

It will be many weeks before I contemplate actually creating the first tables. Until then I intend to continue soaking up as much knowledge as I can on database design. At the same time, I'm using the existing Excel workbooks and 10 - 12 years of job experience to precisely define the use cases and from there some preliminary data modelling. Once I think I've got it correct then I'll slowly start building.

Yes, once I start creating the tables I know that several of them will look like parts of the current spreadsheets, but the interaction between them is where I expect to gain benefit - the ability to query the database and derive answers that are cumbersome, or impossible, to extract from the spreadsheets.

I'm really looking forward to the months ahead.

David

Answer 11

Thanks John - that's sage advice.

Sage advice + NO points = Nothing....   :)

I keep forgetting that people love stories of flying while they struggle to walk...

Maybe next time...

Answer 12

Yes, once I start creating the tables I know that several of them will look like parts of the current spreadsheets

The data might look the same but the software processing it is different.

---

Sam Hobbs
SimpleSamples.Info

Answer 13

@John - don't do forum for the points.  Just not worth the frustrations.  Just do it to help and let be what will be.  I can't count the number of threads that I've gotten no recognition, and other thread where I'm marked as having answered the question even though it was someone else.  The whole point system is pointless.

@David - You can replicate the forms if you want, but this can also be your opportunity to improve upon things.  The critical thing with any database is to properly breakdown the table structure so your data is properly normalized.  This is the most critical aspect of the entire project.  This is the foundation upon which you build everything else.  Get this wrong and it will plague you in everything else your do (queries, forms, reports, ...).  So take the time to do this well, and ask for help, or a second opinion to review your design when the time comes.  Once the table are setup properly, then the fun begins with developing your forms and adding all sorts of functionalities.

You may like to look at http://www.devhut.net/2017/06/17/great-access-tools-calendar-controls/ and be sure to look over the other useful tools listed in the Other Articles in this Series section at the bottom of the article.

I'd also recommend Mz-Tools which is one of the absolute best add-ins for any VBA development.  RubberDuck is another one worth looking over.  And if you seriously get into query/sql then perhap Access SQL Editor could be of interest. In all cases, I have no affiliation, no financial gains to be made, just my personal opinion and sharing experience/knowledge.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 14

@Daniel ...it was just a simple observation....

Interesting that Access SQL Editor ...i am curious about its stability...maybe i will give it a try.

Answer 15

Ah - now I understand the pique. I was running late for a flight on Sunday and in my rush to wind up the session here I missed tagging the one-liner as "Answer" despite having said I was going to mark all. My error and that's now corrected; it was an act of rushed carelessness, not a failure to observe protocol. Likewise the follow-up didn't get tagged as I mistakenly thought I'd already done it with the initial post.

Daniel, I'm very much on the same page as you. When I'm in town I spend a large part of every week at a number of schools, working with kids with ADHD. It's a voluntary role and I dodge accolades like the plague; my sole motivation is helping these kids improve their self-esteem, their self-confidence and their acceptance of who they are, and my gratification is in seeing them progress. Brownie points are totally un-needed, I get more than enough reward in just knowing that I've improved their lives.

Again, my thanks to all for the follow-up comments. I will be taking all of the guidance and advice on board, I'll be following every suggested link and I'll be endeavouring to soak up knowledge like a sponge, after which I'll hopefully be able to generate a worthwhile system.

David