Best Method to Edit ACADE Databases (SQL vs. MS Access)

[Anonymous]

Our company has decided to implement ACADE 2017, and I have been instructed to find a way to access and edit the database files without using a database program (MS Access).  We have an both an ERP system and SQL server up and running now. The problem isn't the cost of the database software, there is a desire to limit the number of standalone databases on our system.  I can push back but the battle will be uphill, so I need some more information.  

The ACADE database will be linked to the ERP system through a few fields (ERP Part Numbers/Make/Model/Descriptions, etc.) which are present in ERP. Data flow will be from ERP to ACADE; changes will not be pushed back to ERP by ACADE, and unlinked data fields (e.g., ratings, pinlists, etc.) will be ignored.  

I'm concerned with the myriad of other databases which exist (footprint_lookup, schematic_lookup & wd_picklist to name a few), the number of tables which are included in some of these databases, and the ease of editing the data all of these database files without a database program.  I know SQL has considerable flexibility, I just don't know if it can deliver "all this & more." 

My question is:  When considering all of the effort (which may be) required to set up the ERP to SQL to ACADE interface is the better choice when compared to the relative simplicity of installing MS Access and editing the database files directly?

Thanks,

Michael

[dougmcalexander]

The safest and most reliable way to edit the databases and maintain data integrity is via the actual database editors that are built into AutoCAD Electrical.  If you wish to directly edit the database tables, MS Access is more straightforward.  Be absolutely certain that anyone who will edit the database knows what they are doing.  The names of the tables, the format of the tables, the names of the fields, and even the order of the fields in the tables all matter to AutoCAD Electrical.  I have had to repair many databases.  Sometimes the customers didn't even realize the damage they had done until they tried to migrate the database forward for use with a newer release of AutoCAD Electrical.

As far as SQL is concerned, much has been written about it already on this forum.  But again, it is important to be sure that everything functions correctly in Access format prior to attempting the SQL migration.

[darren.stults]

Doug,

We've been using the ACADe editor for making changes to our catalogs and have recently run into problems with our security software. Specifically, AutoCAD seems to write temporary files with random extensions when moving data between its interface and MS Access. When one of those extensions matches one in the list of ransomware extensions in the security suite the user gets locked out of the server. This has happened about 5 times now to two different users resulting in complete shutdown of productivity until IT can get the user unlocked. I'm assuming that since it is a recent issue (last 3-4 months) it is probably related to an update or change in our security software.

Have you run into this situation? If so, do you think migrating to SQL Server would solve the random filename issue? We don't have the problem when I use MS Access to edit the MDB files instead of the ACADe interface. Unfortunately that means I would have to do all the catalog adds/edits since I'm the only one with an Access license or the know-how to use it. IT says they cannot do much to prevent this because of the random nature of the filenames.

I posted something on this awhile back and received an answer from another user which satisfied the "why" and "what" but so far I have no real solution. I can't be the only one experiencing this error and if anybody would know how this can be fixed it would be you.

I very much appreciate any help you can provide.

Darren Stults

[dougmcalexander]

Darren, I haven't seen this issue before.  And the only file name that I know of that gets generated temporarily while editing the parts catalog database (default_cat.mdb) is the default_cat.ldb file (lock file).  If you add something to the Task List an update database is created but it goes away after the task list is executed.  But that isn't even related to the catalog database anyway.  So this seems strange that you are seeing random files.  I would be curious what their file names are.

There were reported issues with the catalog database when two or more users were editing the database simultaneously with the Catalog Browser.  That issue was corrected in release 2018.

As for SQL, I have been testing it for a while now and it seems fine.  I didn't see much of a speed increase but it does keep the database from being accidentally damaged by someone using MS Access.  If you don't know just how tables need to be named and formatted you can create problems when editing directly with MS Access.  The database might even appear to function okay but when you try to migrate it into the next release of the software you get errors.  

I wish I never needed to edit the database directly with MS Access but there are a few times when I need to edit the database directly.  For example, when I need to add the same part multiple times with a different ASSY LIST value.  The Catalog Browser doesn't allow this and will produce an error popup when you try to save changes.  I have a request on the Idea Station to allow us to enter the same part multiple times with a different ASSY LIST value, just the same as we can enter the same part number multiple times with a different ASSY CODE value.