Excel Common List Follow UP 2

[foundryqa]

Skip (or anyone else),

With the holidays coming up, I'm going to have a bit of extra time to finally set up a common workbook that can then be queried by multiple workbooks that need the same "master" data. Reference the previous thread thread68-1376746 . Before I take the plunge and work on the solution that Skip gave me, I have one remaining question. Would those of you that have done this sort of thing in the past create a database in MS Access and query it, or would you put all of the master data in a workbook and use MSQuery to grab it from there? Looking for pros and cons and hoping to learn a bit more in the process.

Thanks in advance,

Fred

 

[SkipVought]

Hi,

Depends how formal you need to be with controls on the lists like multiple user accessibility to the lists for editing. All thing being equal, it makes little difference, as long as the database is accessible.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[foundryqa]

Skip,

Would you say that there really isn't a performance hit going the Access route then?

Thanks,

Fred

 

[SkipVought]

None that I am aware of.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

Generally, querying from Access is quicker than from excel as Access is more optimised for SQL execution but that will only really come into play if you are dealing with relatively large numbers of rows of data. In that eventuality, you may have to go the Access route anyway as excel can only hold 65536 rows of data (for any version < 2007)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[foundryqa]

Thanks guys. I will be working with 300 - 1000 records. I like each method for different reasons. I may end up just flipping a coin

 

[SkipVought]

Just remember that Excel was not designed to be a database, although it can be use like a database with limited functionality. It all depends on your requirements.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

For that number of records, unless you have a lot of relationships between different "tables", you're probably better off using excel as it doesn't require moving "outside" the app...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question