Using Excel instead of Access as a database

I'm thinking about using Excel as a database to hold some project specific
light fixture information that will be used to create a BOM.
The reason for using Excel would be so that other non-ACAD users can edit
the information and then have the CAD user create the BOM in AutoCAD using
tables.

Here's what I'm thinking...
The light fixture has an attribute which would be the light fixture's "ID"
tag, let's say "A1".
There would be a corresponding row in Excel which would have A1 and then a
description, manufacturer, etc.. which would be in different columns.
The BOM program would iterate through all of the blocks in the drawing and
read the ID tags, then it would search through the Excel database. If a
matching ID tag was found in the database, it would enter the information
into the table.

So my questions are...
1) Are there any drawbacks to using Excel vs Access?
2) I know how to write to Excel from AutoCAD, but I'm not sure how I would
go about _reading_ the information from Excel and dumping it into AutoCAD.
I can create the table, no problem.
3) I know how to get AutoCAD to read an Access database (records and tables)
but I'm not sure how that would translate to Excel.

Any pointers, links, info, code snippets anyone can provide will be
appreciated.

Thanks in advance!

 

See comment inline.

It depends. How complicated is the BOM data? You probably know well the
difference between spreadsheet and relational database. The key difference
here maybe that in database, you do not store redundant data and the
database can place constraint on value's uniqueness, on parent-child
relationships, while in spreadsheet, there is no such thing to prevent
redundant or duplicate data. As for allowing user to enter data outside
Acad, again, you have more control if you properly design forms in Access.

On the other hand, if the BOM data structure is not that complicated, Excel
will do. When you put software license into consideration: if all users have
Office standard, Excel is available, while Access only included in Office
Professional. However, if you do not need user to enter data outside Acad,
you can use ADO/DAO access data in *.xls/*.mdb without Access installed.

How do you write to *.xls? Automation or ADO/DAO connection/Recordset (I
recommend this).

If you use ADO/DAO to read data from Access db, you only need do minor
change to the ConnectionString to connect to *.xls file and minor change to
your "SELECT...FROM..." SQL query statement. Search MS knowledge base or
google "ADO & Excel", you can find lot of talks and samples on this.

 

Okay, so if it's pretty much the same as Access, then would a sheet be the
equivalent to a table and the columns equivalent to fields?
If so, how do I perform a query on a sheet?

 

I'm only going to say this one time, NEVER use Excel as a database!

In fact, if at all possible [and it usually is if you think about it], never
use Excel!

 

WHY?

--
I support two teams: The Red Sox and whoever beats the Yankees.

 

Because Excel is not a database, it's not intended to be a database. Excel
is perhaps the most mis-used application in the universe. Go the tables
route, use a real database application, it's easier and more efficient.
Trust me, you won't regret it.

 

I think that can create a decent BOM system using Excel, if as mentioned here, it doesn't required any of the features of a relational database. Make sure to check which version of Excel in installed before trying to connect to it. Good luck.

 

Excel would basically be used as a holding area for the information since
it's an app that a lot of people are familiar with and the information can
be easily modified.

 

Here is one of the link I suggested in previous reply:

http://support.microsoft.com/kb/257819/EN-US/

You can open a worksheet as a table, you also can open a range on the
worksheet as table. Just read through the article above link pointed.

 

Hi,

What a ridiculous comment. It has all the usefulness of a comment like:

"Never drive a Ford"

Why don't you try and justify it if you can?

--


Laurie Comerford
CADApps
www.cadapps.com.au

 

Don't say I didn't warn you!

 

Well, let's see - recordsets? record locking? SQL queries. The insane way
you have to reference a value in an Excel spreadsheet should be more than
adequate. Should I continue?

 

We have a winner ... of the most ridiculous statement of the year!

Terry

 

I don't want to give you a wrong impression. You have to seriously consider how the BOM is going to be used, before deciding for Excel or Access. You have to look forward and think about how is the DB is going to be used and maintained, and who would do it. In addition, what importance it would it have for your company. Sometimes small computer error can cost a lot of money. Based on our experience I can tell you that people never do what they are supposed to do. It doesn't matter how many error traps and checks you put in your code, people always find ways of screwing it. If you really want to create an app. that would worth something at least use Access or MSDE.

 

Hi,

Totally inadequate excuse.

The average user of Excel can use it very happily without ever having heard
of these things.

If you are talking about data management and programming, there may be an
excuse not to use Exel, but that's not what you said in your post, nor was
it necessary relating to the original query, where for most users Access or
SQL Server or whatever are arcane and they would never be abe to manipulate
their quoting outside of Excel.

--

Regards,


Laurie Comerford
www.cadapps.com.au

 

Correct Excel should not be used as a database program. If you read what Matt wishes to achieve though I think he has just used the wrong terminology. A spreadsheet is is fine for transferring simple data to and from AutoCAD.
Excel is just as much a good spreadsheet application as Access is a database application. Therefore each application has its place.
Regards - Nathan

 

Don't worry, I won't.

 

Maybe "database" was the wrong word to use. But that's essentially that's
what it will be... a database, as defined in Webster's dictionary: a usually
large collection of data organized especially for rapid search and
retrieval. I don't need relations between tables/fields; I don't need
queries; I don't need the ability to print out a report. I just need a
place to store information that is easy to edit. If I need to, I'll place
the information in a TXT file, which could also be called a database.

 

Thank you.
It looks _very_ similar to the code I have for reading an Access DB.

Thanks again.

 

Yeah, HJohn makes an excellent point. I have seen tiny little utilities
like this evolve into more complex ones. You build something marginally
useful, and a few of your users or your boss say, "WOW, way cool. Can you
make it do <x> too?". Having chosen a more traditional, scalable tool like
Access, your answer is probably "Sure, no problem.". With Excel, it could
be more like, "Uh, maybe." Next request: Access: "Yeah, sure.", Excel: "Aw
crap, I should have used...".

As Norm pointed out, you don't need an MS-Access license on every seat to
use the .MDB file as a standalone backend via ADO. And you can always push
a SQL query out to a Excel file as an editable report for those that need
it. my2c, from a vet with 20 years of slinging data.
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

consider how the BOM is going to be used, before deciding for Excel or
Access. You have to look forward and think about how is the DB is going to
be used and maintained, and who would do it. In addition, what importance
it would it have for your company. Sometimes small computer error can cost
a lot of money. Based on our experience I can tell you that people never do
what they are supposed to do. It doesn't matter how many error traps and
checks you put in your code, people always find ways of screwing it. If you
really want to create an app. that would worth something at least use Access
or MSDE.