Share

Access vs. Excel

time*treat's avatar - radar

I know some have used Access to manage their data. I'd like to know from those that have used Excel too, if Access is worth the slowdown period to learn it. I've got a book or two on it, but so far, everything I have needed to do, I have found a way to handle in the spreadsheet program.

lottaloot's avatar - AvatarZ56

I would like to know the answer to that as well. 

I have access, but haven't a clue as to how to use it. 

Todd's avatar - Cylon 200.jpg

Here's the deal: it depends on how you're going to USE the data:

  • If it will be you manipulating the data yourself, and you're going to do lots of different things with the data (you need flexibility), then stick with Excel.
  • If you're only going to have a PROGRAM use the data -- like a Visual Basic program -- then use Access, because it's much harder to get a program to read data from Excel.

For most people, you should stick with Excel.  Sometimes it may be impressive to hear someone say they use Access, but in reality it won't do you any good. 

lottaloot's avatar - AvatarZ56

Thanks for the info Todd.  Wink

sysp34's avatar - Lottery-062.jpg

Here's the deal: it depends on how you're going to USE the data:

  • If it will be you manipulating the data yourself, and you're going to do lots of different things with the data (you need flexibility), then stick with Excel.
  • If you're only going to have a PROGRAM use the data -- like a Visual Basic program -- then use Access, because it's much harder to get a program to read data from Excel.

For most people, you should stick with Excel.  Sometimes it may be impressive to hear someone say they use Access, but in reality it won't do you any good. 

The IT competent  had  spoken I Agree!I Agree!I Agree!
excel had  limitation only  65536 rows and 255 column per worksheet

sysp34


time*treat's avatar - radar

Right now, I'm kicking around a 5/36 numberset. Basically, I am trying to find out if Access will handle the data better, i.e. let me see all records (of really big data sets).

I have, though VBA in Excel, written a bit of code that creates a file with each combination as a record. I retrieve a record with a GET. Record 1 = 1,2,3,4,5 ... record 376,992 = 32,33,34,35,36. The 65536x255 limitation has been worked around as far as working w/ the data. I am able to create static "libraries" in Excel and I use other code to kick the data around. The only thing, so far, is that I can't view the library data directly (have to use the GET) or all at once (due to the Row limit). That is not too big a deal, as far as working with the data, but if someone knows how many records an Access file can hold, I'd like to know too.

Todd's avatar - Cylon 200.jpg

Right now, I'm kicking around a 5/36 numberset. Basically, I am trying to find out if Access will handle the data better, i.e. let me see all records (of really big data sets).

I have, though VBA in Excel, written a bit of code that creates a file with each combination as a record. I retrieve a record with a GET. Record 1 = 1,2,3,4,5 ... record 376,992 = 32,33,34,35,36. The 65536x255 limitation has been worked around as far as working w/ the data. I am able to create static "libraries" in Excel and I use other code to kick the data around. The only thing, so far, is that I can't view the library data directly (have to use the GET) or all at once (due to the Row limit). That is not too big a deal, as far as working with the data, but if someone knows how many records an Access file can hold, I'd like to know too.

That's one of those things where we may differ on how we approach the problem.

For something like your example of a data set (table) with every possible combination, I would almost never build a table.  Since the data is highly predictable, and is a function of mathematics, I would just calculate whatever values I needed using small, reusable user defined functions, rather than the "brute force" approach of creating a table.

By doing so, it actually becomes much more fexible (it is easy to create other data sets just by changing the function call), and it can all work within Excel very easily.

Todd's avatar - Cylon 200.jpg

By the way, I don't want to leave the impression that one way is "right" and the other is "wrong".  In program code, there is no perfect -- or "correct" -- way of doing things.  It's kind of like lottery systems:  nobody has the "one correct solution".  Everyone's ideas have merit.

time*treat's avatar - radar

Well, it looks like I'm on the right track then. I should have stated it as "I wrote code that created the data table." I open the 1 file (which contains all the combos), not create the table, each run. If the system pans out, then I'll get a faster PC, before learning a new app. One thing about a "slow" machine is you learn to write really efficient code.

 

 

TopEnd of thread (1 page)

Welcome Guest

Your last visit: Sat, Oct 31, 2020, 6:38 am

Log In

Log InCancel

Forgot your username?

Forgot your password?