What connects symbols to components in the DB?
I'm currently using a database from E3 2009.
My goal is to search through the database (using SQL statements or export to excel & macro) and find all of the symbols (of type 2 or 61) that do not have a component assigned to them. My thought was that the LPNTR value was the link between symboldata and componentdata. If it was, I could take the LPNTR from the symbol and search through all of the components to see if there was a match. Unfortunately, this isn't working. I have a symbol used in a component and they both have different LPNTR values, so this must not be how it's done.
I've done something similar for text types- see how many different levels are used for any particular text type and I was surprised at how messy the DB is- 15 different levels for pin numbers?
Was this Helpful?
-
Official comment
Are you using Models for Panel Layout?
-
the Gate table joins the Component to the Symbols. try this in Access (after you've linked all your component and symbol database).
this will return symbols that are not called out by components. i don't think you need to join the component table but its there.
SELECT SymbolData.Class AS SymbolClass, SymbolData.ENTRY AS SymbolEntry, Gate.CCTNAME AS GateCCTNAME, Gate.COPY AS GateCOPY, ComponentData.ArticleNumber
FROM (SymbolData LEFT JOIN Gate ON SymbolData.ENTRY = Gate.CCTNAME) LEFT JOIN ComponentData ON Gate.ENTRY = ComponentData.LPNTR
WHERE (((Gate.CCTNAME) Is Null));
HTH
bobWas this Helpful?
0 -
Much appreciated, Bob! I have just been importing/exporting with excel for now and writing a macro to do the work from there, since I'm not all that familiar with SQL statements. I'll definitely give that a try sometime since it seems a bit easier than writing a lot of VBA code!
Was this Helpful?
0 -
working with Access is much easier than Excel provided you know what you're doing. make sure you back up your database!!! we have an Oracle database but i use Access to connect to it via ADO to load up the tables i need for my queries.
i'm a little leary of writing to the E3 database. we were told by someone at Zuken to not do it and told us a horror story about some company that effed up their database. i can handle adding/deleting attributes from components and that's about all i'll attempt. our database has a lot of unique 2D views of connectors so its not like we can use macros to create new components.
there's a thread E3.Series forum in LinkedIn where other users are writing to the database (look in the thread about the E3 Com). one user is using MS Info Path to create new components.Was this Helpful?
0 -
I've finally gotten around to using ADO with VS 2008. I've been playing around with some simple programs to help clean up the database and find minor problems (like text types on wrong levels, symbol nodes off of a grid, etc). It's amazing how the right SQL statement can make short work of mass changes!
After viewing a few programs, I've noticed that my DB has a lot of seemingly broken links. To refresh everything I decided to start with a blank DB, edit all symbols in the old database and save them to the new one, then continue on with models and components. During this process, I had some problems with models- I was getting an error in e3 when clicking on certain models: "W - Symbol type not found in database!" After taking time to map out the database table relationships I think I figured out the problem (and a clue to what all the seemingly unused symbols are!) which is that there is no entry in the OutlineSymbols table (connects ModelData to SymbolData). In order to fix it I need to understand what these OutlineSymbols are. In my DB there are tons of them, but their symbol type isn't 61 like they are with the new database- does it matter? What are they used for? How can I manipulate them?
Any ideas on how to fix this?Was this Helpful?
0
Please sign in to leave a comment.
Comments
5 comments