To Access or not to Access. That is the “Question”

[DocAElstein]

Hi.
. WARNING:- This is a bit rambling!!. It’s aimed only at long experienced Access and Excel users who have the time for a bit of a Philosophical Question!!
. This Thread could have gone in Forums Excel, Access or Other. I think I shouldn’t duplicate Threads so I thought… To be an Access expert you probably first become an Excel (and maybe also VBA) expert, so putting it here is sort of 2 out of 3.
. It is not a specific question. I’m just stuck at a bit of a crossroads, not knowing whether to continue playing with big text and Excel files, manipulating them with VBA, or whether to take the extra time and expense to get into Access
. My background is very unusual, giving me a very untypical and unique view of thing. For 25 years I used in research the best computers there were (which usually costed more than a big House). Then I “opted out” before breaking my “Computer Koma” a couple of years back for a private project and started again with my first experience with PCs and Windows, especially Excel (from 2007 versions), and more recently VBA. I’ve always had the habit of handling and looking at ridiculous large amounts of data and enormous Data tables all at the same time. Speed and size limitations made my life very hard!!. Since “waking up” to computers I’m still amazed how cheaply one can obtain blitz speed and a ridiculous almost endless amount of memory
. Recently I came across Access, and have had some advice to get into that. I’ve put some effort into understanding the whole idea behind Relational Data bases etc. I understand the points of efficiency, avoiding duplicate entries, allowing for “quick” well ordered searches ( so called “Queries”) etc. etc. But my opinion is that Access for me 25 years ago, or for Excel and Computers up until about 10- 25 years ago was a very good idea. But at about Excel 2007 and computers from the last couple of years, (where I “jumped back in”), many of the arguments for Access seem no longer relevant, at least when, like me, one is not interested in using small “Computers” (Smart phones and the like), and I think the only people that can profit from the possibility of calculations at the speed of light are Money speculators!!!!
. There are some files here. Maybe a quick look would help to at least to show the scale of my problem!!. They are a bit messy, and big, so I would only look at them if you have a bit of time!……

. The first examples are based on my earlier ideas for a provisional working files…that was before I knew anything about VBA

FileSnack | Easy file sharing

FileSnack | Easy file sharing

…… these ideas I tried then to expand to a massive all inclusive one:

FileSnack | Easy file sharing


…. I gave up with that last (third) file / workbook as it was too complicated – That was however, before I got into VBA, and was typing everything in by hand. – I had no big data files or data banks.. I was simply copying numbers in that I got from various places

… All Files look daunting.. But I view on a large Television screen and I’m getting good at easily manipulating them and data easily with VBA. I think with a combination of Formats (I like colors for example!!) and VBA, I can see, sort, and get at everything I want. And “efficiency” with current Computers capabilities is a bit academic.

…..I started with a new provisional working file, using a bit of VBA a couple of weeks ago:

FileSnack | Easy file sharing

….. and I just started as a parallel project to make a massive Data File to support the working file (That is to say a file from which I can use VBA to take the data from there and put in my working file):

FileSnack | Easy file sharing

. I’ve decided to take a bit of time out, and think whether or not to continue along these lines or whether to get into Access
. Sorry for rambling on a bit. I’m just looking for quick yes or no from experienced excel VBA and Access users… … That is to say should I bother to learn Access at my age.. My opinion is that it makes things even less “visible to the naked eye!!”


Thanks,
Alan Elston.

(Incidentally the bit of data I have in that last file, was obtained partly with the help from “MrExcell” Threads where Access and Relational Files were torn apart with VBA to produce a “normal” table with rows and columns (or is it “Relations”, “Records / tuples “ and “Attributes / Fields” in Today’s Language). Thanks again to Alan and Mike for that help ( Threads…. http://www.mrexcel.com/forum/excel-questions/780686-re-import-open-access-ascii-file-excel.html And http://www.mrexcel.com/forum/micros...scii-file-text-excel-file-without-access.html )

 

[TerryHogarth21]

I took a look at a subset of your final data. That's a lot of information to say the least

If anything it may make it easier once the data is normalized in Access to manage vs Excel.

In any case this is always worth a read for Access vs. Excel
Using Access or Excel to manage your data - Excel

I normally always use Excel to present or show data - ie Pivot Tables/Charts etc and Access to create a relational database and up keeping the back-end. What is great is once you start to learn and use both they work well together for pushing or pulling data to and from.

Of course there are limitations but it's always nice to go into this forum and see some of the amazing things that people can do with either tools.

 

[DocAElstein]

Hi,
Thanks for the Reply and info.
Gives me a (slight "uneasy" ) feeling that I should start to learn Access.. - I say “uneasy” because I was hoping not to have to learn even more, mainlly ‘cos I’m “getting on” in age a bit now. (And buying Access is expensive for me , working privately!!). But I’ll try to keep an open mind, maybe carry on Tidying up the Big Excel Tables for now. But, yeah, it is nice going through this forum, giving and getting new ideas. I'm often dead impressed with what people can do with VBA
Thanks again
Alan

 

[TerryHogarth21]

Cleaning up the Excel file is worth a shot or even reviewing some of the information to see if you really need all those columns of data.

The Excel forum is a great place to start to see if you could even write or get some more VBA ideas to automate some of your data.

I like Access but it could be overwhelming at first - the learning for me was just out of necessity due to the size limitation in Excel. Even Access has the 2GB limitation and then you have to move onto SQL Server. Then you have to learn SQL it is never ending.

 

[DocAElstein]

Hi Terry
Yeah, I agree, there’s loads there I’ll probably never need, and I would never have done it for 25 years (And I think I’d even have to be careful with Access- (I read somewhere of a 255 column ( Field) limitation I think?)
.. But space seems irrelevant (in excel and text files) these days. And automating the data with VBA is exactly wot I’m getting into just now, and yeah I’ve already had some great help on that front from this Access forum and the excel forum (and the “other languages” forum)
.. The code (I wrote quite quickly) in that FileSnack | Easy file sharing file was something of a “Home-made” Query I think, without any Table (relation) or size restrictions. It picks out stuff based on a heading (out of that file FileSnack | Easy file sharing ) but is more flexible as you can allow for different spellings etc.
… so I was hoping to do everything that I can with Access with similar code.. It probably isn’t efficient or as quick as Access… but as I said “who cares” these days for most applications with the amazing speed and memory available


But Thanks for the reply..it all helps in making my decision on whether to get into Access.
Alan

 

[xenou]

I’ve always had the habit of handling and looking at ridiculous large amounts of data and enormous Data tables all at the same time. Speed and size limitations made my life very hard!!. Since “waking up” to computers I’m still amazed how cheaply one can obtain blitz speed and a ridiculous almost endless amount of memory

Access is not good for ridiculously large amounts of data. It is only good for large amounts of data.
ξ

 

[DocAElstein]

Access is not good for ridiculously large amounts of data. It is only good for large amounts of data.
ξ

.............................Yeah! I’m beginning to wonder if the “question” should be … Excel/text Files or….Give up!! –it is a bit overwhelming and” ridiculous”. But it’s an important project, so I’ll keep at it for a while..(or till I drop!!)
Alan ( Dr. AElstein)