Updating Table Data Automatically

[aquarius]

Hi All,

I have very little experience with access and was hoping the good folks here could give me some direction, guidance, and some good 'ol you-can-do-it! I'm feeling pretty overwhelmed at this point.

I work for a small investment firm and I've been dubbed the technology officer. I'm trying to automate our processes. Traditionally we have used excel for everything (calculating, ranking, sorting, doing quantitative analysis). This has worked okay for a while, but we're finding that our excel spreadsheets can no longer handle the data or workload. We essentially knew we would have to move to access at somepoint, and it appears as this time has come.

Here are some of the issues we're running into:



We use a program that feeds daily data to excel. So for instance we use the =TODAY() function in excel and run the excel file to notify the program that feeds excel to grab some data point of data matching that date (today).

Ideally though, in access we'd like to accumulate the data. So that we can see a trailer starting with today's data going backwards. How do we add data to tables without doing it manually and have it update automatically? How do we add fields so every time it updates a new field is added?

At somepoint I hope to master access, I know it will take a lot of hard work and dedication, at this point I know very little. I greatly appreciate any input and direction, I hope to someday be as knowledgable as some of you.

Thank you again,
aquarius

[ajetrumpet]

aquarius,

To be honest with you, it doesn't sound like Access will be that much more useful than Excel. IMO, Access is absolutely horrible when it comes to its ability to produce statistical data. Access is a 3-dimensional beast that specializes in vertical analysis. In other words, it is perfect for analyzing data that can be related any kind of of record grouping need. I can't count the number of times I've preached this.

But at any rate, what is your source program? It's quite doubtful that VBA has a reference library for it, regardless of what it is. One option that I can personally think of is the linked tables. Access can link to Excel sheets as their own tables. All (recent) versions of Access have a linked table manager that makes the process simple. The downside to linked tables is the fact that the data can only be edited in the source, which is of course Excel in this case.

Sounds like that might work though, especially if the process between Excel and the source is already automated. As the old adage goes, "more links in the chain makes things difficult". But...3 isn't bad! Certainly better than some other situations you could be in!

[aquarius]

Thankyou for your reply and insight ajetrumpet, and I appoligize for posting twice, I assumed this posting did not go through. Although it better explains my current situation.

I imagine, although I could be mistaken, that as long as access can execute mathmatical equations and build upon data from a specified source automatically it will be useful.

The program that we use to source data to excel is called Baseline. I called Baseline to see if they had some sort of 'add-in' for access and they did not, which isn't surprising. Excel will likely remain a key player, but it's function will be used to gather the data from Baseline to import to access where the key calculation will be done (if this is possible, which I really hope it is... because I told my boss it was :| ).

Editing the source data in excel will not be a problem as long as it accumulates in access. It will be a problem if we have to accumulate it in excel (which is what we do now), because the data sets get so large that excel is unable to handle them.

We use macros to update excel with the source data from baseline everyday. This works fine except for the data sets are getting too large that they crash. This is essentially the underlying reason were trying to move to access, but if we cannot do calculations in access it would make the move pointless.

[ajetrumpet]

If Excel is becoming too small for your data accumulation, then I don't think you have any other option but to import it everyday. I said this in a post the other too, but I don't think there is a way to append Excel sheet data to the end of a dataset in Access using code.

Well...there IS, but looping and copying row by row using objects is incredibly slow and inefficient. The only way I know to append new Excel data to the end in an Access table quickly is to use the import wizard.

But of course...if you eventually wanted code and the copy/write method I mentioned was the only possible way, taking the plunge and writing the UDF would only have to be done once. After that, given the fact that the code was written in a flexible manner, it could be called easily everyday.

[aquarius]

You mentioned in the other post that it may be more feasible to create a temporary access table which has the data sourced from excel for that day. Then use the temporary table to append to another table which has a data history or accumulation of the daily data.

Would this be better executed using macros or vba? I'm going to assume vba and is there a vba book or internet source that you would recommend I read? Or maybe both?

Despite knowing very little about access or vba, atleast I know more than I did yesterday. Thankyou for your continued guidance, ajetrumpet.

[ajetrumpet]

No problem. I love discussions like this, and I have a lot to say about it as well.

IMO, books on writing code are absolutely useless. The only reason I say is because 99% of the books out there that are written about programming languages are written in a theoretical fashion. What that means is that it's almost exactly the same as sitting in the lecture room of a computer programming class in college. I'm convinced that this is the case being publishers have a say in it. I have no idea, but I suspect that is the case. Gotta follow the rules, you know? If you do buy a book, even if you absolutely zero about visual basic, don't buy one that has the word "dummy" in the name. IMO, these books don't provide confidence at all. Skill development is attained by desire and confidence if nothing else.

What I would do if you want to learn it is to figure out what the purpose of it is. For example, the purpose of PHP code in general is to compile information and structure before a webpage is offically loaded into a browser window (or something similar to that, I'm not a huge expert). With VBA, the purpose of it being developed was so that users had the ability to integrate different office programs so they could work together in one scenario instead of manually working more than one program with the mouse. It was brilliant by Microsoft to develop that kind of time-saving idea. It's just too bad that they produce a lot of what I call "dummy" software. A lot of competent programmers will tell you that MS products are very good tools for beginners. Of course that's not always true, but at any rate...

Without a doubt, the best way to learn technical skills is to DO it, and you don't have to have a programming job to get good at it. I learned VBA on my own simply because I was interested in it 3 years before I ever got to apply the skills for a business. I also have many different test scenarios set up on my different computers (so many I can't count them all!). For instance, I have a small business template that could at any time be distributed because it's already set up to run a small business. But I use it to test a new idea whenever I have to because it's set up to accommodate almost anything that I expect to run into in the future. I also have databases specifically for running test code on Financial functions, a corruption import db, and even one for radical ideas.

If you really want to delve into being a programmer or even learning how it works, first pick a language. IMO, Visual Basic is about as easy as you can get and still be useful in today's marketplace. I admit that I do have VBA books that I bought and Barnes and Noble, but to tell the truth I've only read probably 50 pages total, out of maybe 3,000 combined. Another thing that is good to know is the fact that forums like this produce answers largely in part to online help articles and e-commerce websites (not necessarily e-commerce, but online sale-type companies). Some of the ones that I've seen mentioned that pertain to Access specifically are:

http://allenbrowne.com/

http://www.functionx.com/

http://www.blueclaw-db.com/

http://www.techonthenet.com/

http://www.fmsinc.com/

http://www.mvps.org/

And then there are KB's out there that make Microsoft's KB look like yesterday's news. One I like is:

http://www.vbaexpress.com/kb/default.php

This is just my opinion about what you asked here. I guess I gave an opinion on a lot of things though. The one good thing about learning Microsoft technology is that you can always be assured that you're marketable. I don't care how much fight that's out there to take Microsoft down. They were the first on the block and they'll always exist even though they do sometimes produce sub-standard software that their PR department has to make look good.

And I guess to answer your question, use VBA to implement my recommendation if you do use it. Macros can't handle it alone. The options just aren't there.

[NoellaG]

Hi aquarius,

if you have a large load of data and have to upgrade today, don't forget the SQL server option. It's more stable, more performant, more secure, great import/export facilities (IIS), ... and of course more costly, I agree. But I always found an access front end to a SQL server back end a winner combination.

grNG