Hello All,
Sorry for the length of this. I have seen a few posts concerning this topic and thought I would try. This seems like something a number of people would like to have.
First the basics, I am using:
Windows 7 Ultimate, 64 bit.
Autocad Electrical 2012 and as far as I know there are no service packs installed.
Microsoft Access 2002 + SP3
Microsoft Excel 2007
Simply put, I would like to have an excel file that contains the cost of the different items we use. I think this is the better route since I can do the math (i.e. factor any cost multiplies, etc) on the item costs.
From there, I would like to have the costs reflected in the Acade Schematic --> Reports --> Bill of Material. I was thinking of putting the costs in the "User2" column.
From the site http://office.microsoft.com/en-us/access-help/import-export-and-link-data-between-access-and-excel-H..., I was able to find some information about how to use the Access "Link Tables" function.
I have created an Excel worksheet called "LS" and created this table based on the columns from the "LS" table.
In the Excel worksheet "LS" I added a few columns after the column "WDBLKNAM" and added my prices.
I also changed the format of the "User2" column from "General" to "Currency".
In the default_cat.mdb, I renamed the current "LS" table to "LSorg".
Using the information on creating a Access "Link Tables", I was able to create the new table "LS" in default_cat.mdb.
So far so good. Using Access, I can open the linked "LS" table in default_cat.mdb and this looks ok. Looking in the "LS" table in default_cat.mdb with Access, I can see my pricing in the "User2" column. I can change the pricing in the excel file and the changes are updated in the "LS" table in default_cat.mdb. This is great.
Now for the issue. If I insert a component that I know is in the "LS" catalog and try the Catalog data --> Lookup button, I get a message from Acade that indicates that the "User2" column must be a Character field. Ok, I went back to the excel file and changed the format of the "User2" column back to "General". Try to insert a component again and the Catalog data --> Lookup button does not show the message, however the "LS" catalog is not displayed. If I click on the drop down "table", "LS" is not there but my original copy "LSorg" is shown.
On the above referenced website I found the note :
- You cannot link Excel data to an existing table in the database. When you create a link, Access creates a new table, often referred to as a linked table. The table shows the data in the source worksheet or named range, but it doesn't actually store the data in the database.
I can only guess that the above is the issue.
I went as far as exporting my "LSorg" table to excel and then trying the access "Link Tables" function to create the "LS" table. Same issue.
Anyone else trying or needing this "feature"?
Any solutions?
![Autodesk Design & Make Report](https://forums.autodesk.com/t5/image/serverpage/image-id/1350519iCB55845EBFC43016/image-size/large?v=v2&px=999" "Autodesk Design & Make Report")
