TheSwamp
Code Red => VB(A) => Topic started by: David Hall on October 12, 2015, 04:15:49 PM
-
OK, so I have been out of the loop for a while, and just got this thrown in my lap. I am looking for opinions (be nice, i know I opened the door) on the best way to use excel as a database to generate a BOM. We have assembly drawing tabs with the list of parts on each tab. The user inputs how many of those units they need, and the final tab creates the BOM based on the prev. tabs. The excel file is 22mb, so I know this is not the most efficient way to do the task, but I cant seem to figure out what is. I was thinking of a CSV file that gets read in, but then i wasn't sure how to best manage that file either. I thought about XML, but once again, managing that file isnt easy.
any ideas?
-
Is this with Inventor, AutoCAD*, or Revit?
What other programs are available, eg; Navision?
-
its all done in excel. I will try to get some screen shots
-
Here are 3 shots, the first, quanity is nothing, the second i put in a quanity of 1, and the third, shows the BOM based on previous tab
-
I think that if you have Excel then there's a good chance you have Access and that's what I'd use. (Actually I'd create a web app with MySql but that's another story)
We have done this very thing with a similar use case, i.e. assemblies with parts and reports for material lists and other things.
While you can do this in Excel it can become very fragile very quickly. Not only because of all the macros firing off but a user can easily corrupt it with a single key stroke.
What we do is export the data as a 'flat' .csv file and import it into access, from there we process this data into tables for each type of assembly and calculate some other data as needed.
From here we have our data tables and can create any reports we want and this is where Access is king.
If you can find your way around access you won't look back, the hardest thing is creating the queries and things like sub reports but once you pick that up you can do anything.
Our database creates the following:
assembly schedules (for workshop assembling)
packing slips (palletising)
cutting lists per part and assembly
delivery dockets
stickers for part id's
checking reports
and so on...
Trying to do all that reliably in Excel is almost futile. It's a lot more work at the start but adding reports when required is a snap.
-
Thanks Mick, I will see what I can pull together. I have used Access before, so it will be pretty easy i hope.
-
Actual database is most certainly the way to go, but here are some things to keep in mind with Access.
The good:
- It's self contained. By this I mean that the storage engine, reporting engine, and user interface (forms) are all stored in one executable file (though it doesn't have to be).
- ODBC - It's easy to connect to the database in VB. It's up to you if you want to use DAO or ADO. Personally, I use ADO, but it's just my personal preference. Both will get the job done.
The bad:
- Access likes to get corrupted. It happens so much that Microsoft added a feature inside of Access to repair it when it happens (Compact and Repair Database). If you end up using Access, I'd recommend using this feature regularly. Now, its a very rare occasion when I have an Access database that gets corrupted beyond repair (but it has happened to me), so you most likely won't be in a situation where it gets corrupted beyond repair. I would simply look at is as an annoyance/maintenance issue.
- Access is NOT good with large tables (at least not with the Access storage engine). What's large? It depends. It's generally good up to 100,000 to 500,000 records in a table. Nothing is stopping you from creating and using larger tables (I have access tables with over 1 million records in them), but it certainly slows down. I have also noticed that the databases with the larger tables in them seem to get corrupted more.
- The VB that you use with Access forms is a little different than the VB you use with any other VB form. This stems from the fact that the Forms in Access are different than VB forms. If you are used to making and coding forms from VB6 or VBA from any other Office product, it will probably take some getting used to.
Other notes:
Development: Because everything is stored in a single exe file, development can get tricky. If someone is using the database, it puts the exe into a state where changes can not be saved. To help mitigate this, you generally "split" the database into (the back end, and the front end). Let me explain. One of the really nice things about Access is that, while it does have a storage engine built into it, you do not have to use it. You can link to database tables from other databases (Access, or otherwise (like MySQL for example)). So, one development strategy that works pretty well is creating an Access database with only tables in it (the back end). Then, create a different Access database that links to the tables from the first Access database. This second database (the front end), will contain all the forms, reports, etc. The reason why you do this is that everyone no longer has to work from the same file. You can have many different copies of the front end. This allows you to create a development copy for yourself that you can work on while everyone else is still using the database. You can then send out newer versions of the front end to everyone as required. Or, you can create different versions of the front end for different people, depending upon the functions that they require. Or, you could simply have one front end file that everyone accesses and do it that way. If you have an Access database already developed, Access provides a tool that will split it into a front end and back end automatically.
Data sources: Access is the only thing that I know of that allows you to create queries that accesses data from different databases. Because Access allows you to link to tables from any database that has an ODBC connector, you can create queries between tables from completely different databases (for example, you can create a query that has a table from Access, a table from MSSQL, and a table from MySQL). Access also allows you to export tables to different databases as well. So, for example, if you start off using the Access storage engine, you can export that table to another ODBC database (like MySQL). You could then delete the local table, and link to the external table. If you give the table the same name as what the local table was, everything will work just like before. So it is easy to migrate away from using the Access storage engine.
Forms: If you want to use the standard VB forms in Access, it is possible. Microsoft doesn't seem to want to let you do it, as they have disabled the button that adds them, but you can drag and drop a form from another VBA project into Access VBA. I think this is an oversight from Microsoft, but I do it all the time.
Those are just my initial thoughts about using Access. Its a double edged sword. There are some really nice things about it, but it ain't all roses.
-
Great post mmelone!
You covered everything we ran into/were aware of as well and is why I'd make it a web app with MySql (if I had the choice). I even think the learning curve may be easier if you haven't developed with access before, getting the most out of Access requires a deep knowledge of how Access works to create anything more than simple queries and reports (IMO).
To get around the issues you mention, we decided to just have a master template DB that the user copies into each project. This isolates errors from the other jobs and a copy get saved in the archived project folder and allows development to continue without problem.
If we need to do an update for a bug we can either export the tables or do a quick copy/paste into a new version or export the fixed code module into the existing DB.
Cheers,
Mick
-
In terms of VBA code development, it depends upon what I'm working in. In Access, I would keep all VBA as part of the Access front end exe file. But, with any other program (Excel, Autocad, etc), I set up a standard module that has a function that loads in vba modules when the program opens. That way, whenever the program runs, they are using the most up to date version of the code, without having to distribute changes every time I make an update. All updates are distributed automatically simply by updating a file.
For example, I created many Excel UDF's. So, I created an Excel Add-in that is basically equivalent to bootstrapping code (same idea as computer BIOS). The code in the Add-in never changes.
In the ThisWorkbook section:
Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
'Register the Macros
Debug.Print "Registering Macros"
Call RegisterMacros
End Sub
Private Sub Workbook_Open()
On Error Resume Next
'Remove Functions
Set mf = Application.VBE.VBProjects("FunctionsProject").VBComponents.Item("Functions")
Application.VBE.VBProjects("FunctionsProject").VBComponents.Item("Functions").Name = "FunctionsRemove"
Application.VBE.VBProjects("FunctionsProject").VBComponents.Remove mf
'Add Functions from network
Application.VBE.VBProjects("FunctionsProject").VBComponents.Import "\\m-storage\shared\ExcelFunctions\Functions.bas"
End Sub
That code imports the Functions.bas (Module) everytime Excel is opened, ensuring that everyone is using the most up to date code. And keeping the bootstrapping code in an Add-in makes it much easier for the end user to add to Excel.
-
Do either of you guys have a basic example youcan share? I have never started from scratch. I tried to import from excel, and that didnt go well, so I will have to input everything from scratch. Also, what would a front end look like? I have always edited an existing DB, so I am not sure what to begin with.
thanks
-
I generally start with creating the tables that will store the data. The core idea behind all databases is to only store any piece of information only once. This concept leads to database normalization(https://en.wikipedia.org/wiki/Database_normalization (https://en.wikipedia.org/wiki/Database_normalization)). This is a fundamental concept in database design, so take a few minutes to go over it and really understand it. The basic concept behind it is the same basic concept behind XREF's in Autocad. You only want to ever have to update any piece of information in only one place. For example, it looks like you are recording part numbers along with descriptions. Using the image you sent, your first inclination might be to create a table with 4 columns in it (Tag No., Qty., Passport Catalog ID, Desc.). But, if you did it this way, if there was ever a change in the description of a part number, you would have to change it for every record in that table. The description information is recorded in more then one place. A better option would be to create a Part Number table that records all of the properties for that part number (Part Number, Description, Weight, etc...). You would then create another table to record that other information (is it a BOM?). It would be something like this, assuming Tag No is a part number (Tag No., Qty, Passport Catalog ID). You would then create a query that links the two tables together to get the final list of all the information you need. I'm probably not explaining myself very well. Basically, I would get familiar with the idea of normalization, and primary keys. Once you have that squared away, you can create all of the tables you'd need to store all of the information you require. Once the information can be stored, you can create a "front end" to show that information however you want. This can be something as simple as a query. Or you can create a form to view the data, or you could create a report. The sky is the limit when it comes to front end design. Whatever you think would be the quickest/easiest/best way to display the data.
-
As programmers, we do see too often those "smart professionals", engineers, land surveyors, designers... over-use (if not abuse!) Excel to store their mission-critical data and hard to sell them better and safer alternatives. Sometimes a programmer can decide, sometimes cannot.
However, as CAD programmers, whether we have a say or not about where/how data is stored, we should focus on making our solution structured correctly first, which I have not seen being mentioned in the discussion of this thread.
By structured correctly, I mean that the typical of solution of this kind would consist of 3 parts: code to manipulate objects in AutoCAD/Drawing, code that passing data between AutoCAD and data store, and code that actually organize the data store (especially, if it is your own custom data store, such as Excel sheet, CSV, XML...).
The key part is the middle one: data access layer. It should be coded against an Interface so that the from part (AutoCAD side code) would have no knowledge on what the back end is, be it Excel sheet, or some kind of database. That way, if the back end data store is changed, simply implement the interface against the new data store, and the CAD side code (reading/writing data back and forth) will not be affected and not need to re-build/re-compile.
With that said, however, if you use VBA, I am not sure if you can use Interface. Back to when I was using VB5/6, yes, one can define Interface and implement it. By why still sticking with VBA?
-
Fee-fi-fo-fum - I recognize substation materials...
We have begun to utilize Inventor 2015 with an add-in for substation design - http://substationdesignsuite.com/
Right now we simply output the materials to a spreadsheet from Inventor - but eventually want to work up an add-in for Inventor that ties the materials to an external DB.
We do use Access for the materials when working up Transmission line projects - but right now it's a confusing mess using the output from another program (PLS-CADD).
Joe Celko's book - Trees and Hierarchies in SQL for Smarties - is an interesting read. Way over my head, but we all have to start somewhere.
The Access Solutions book is an excellent resource.
Anyway, keep us updated as to which path you proceed.
-
Yes, its sub material all right. Sadly, I am using the excel version for right now. I want to move to a DB, most likely Access, but I just dont have the time to set it up right now. I needed something to show as a proof of concept sooner than later, and I was able to do that with excell. That being said, I intend to try and convert to Access in the next month or so. Ultimately, it would be nice to tie the cad to the BOM, but that is anther challenge all by it self.