SQL connection via linked table in Access

[Anonymous]

We are upgrading from ACADE 2012 to 2016.  The migration utility is great, but we wanted to take it one step further with this implementation and connect the ACADE catalog directly to our part master information in SQL (which is inderectly linked to our ERP system).  The best way I could think of to do this was to create a view in SQL and then create a linked table to that view.  The original MISC_CAT table is then renamed, and the newly created linked table renamed to MISC_CAT.  After some trial and error with the linked table creation, it worked.  The problem is, now it doesn't and I have no idea why.

Using the catalog browser interface, causes ACADE to crash when I enter a search term and click the magnifying glass button.  I get a dialog with the message: "FATAL ERROR: Unhandled e0434352h Exception at fd74965dh".  I have reported the bug a couple times, but have not had a response.

If I use the edit component dialog (as opposed to the catalog browser) and the lookup function to query the catalog, I get a different result.  After entering a search term and searching, edit component dialog just closes on its own.  However, the following message and stack trace are recorded in the command line:

Extracting item numbers in use . . .System.ArgumentException: An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at Autodesk.Electrical.Database.AccessDbCatalogStore.getManufacturers(String catName, Dictionary`2& manufacturers)
   at Autodesk.Electrical.Database.CataLogSearch.getManufacturers()
   at Autodesk.Electrical.Database.CataLogSearch.buildQueryLocal(String& queryStr, List`1 partials, List`1 phrases, List`1 orOperands, String blockName, Int32[] indexes)
   at Autodesk.Electrical.Database.CataLogSearch.executeSearch(String searchStr, String blockName, Boolean excludeSubassemblies, ExceptionHandler exceptionHandler, Int32[] indexes)
   at Autodesk.Electrical.SharedControl.CatalogGridViewModel.DoSearch(Boolean isFirstTimeLookup, ExceptionHandler exceptionHandler)
   at Autodesk.Electrical.SharedControl.CatalogGrid.DoSearch(Object sender, ExecutedRoutedEventArgs e)
   at System.Windows.Input.CommandBinding.OnExecuted(Object sender, ExecutedRoutedEventArgs e)
   at System.Windows.Input.CommandManager.ExecuteCommandBinding(Object sender, ExecutedRoutedEventArgs e, CommandBinding commandBinding)
   at System.Windows.Input.CommandManager.FindCommandBinding(CommandBindingCollection commandBindings, Object sender, RoutedEventArgs e, ICommand command, Boolean execute)
   at System.Windows.Input.CommandManager.FindCommandBinding(Object sender, RoutedEventArgs e, ICommand command, Boolean execute)
   at System.Windows.Input.CommandManager.OnExecuted(Object sender, ExecutedRoutedEventArgs e)
   at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
   at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
   at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
   at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
   at System.Windows.Input.RoutedCommand.ExecuteImpl(Object parameter, IInputElement target, Boolean userInitiated)
   at MS.Internal.Commands.CommandHelpers.CriticalExecuteCommandSource(ICommandSource commandSource, Boolean userInitiated)
   at System.Windows.Controls.Primitives.ButtonBase.OnClick()
   at System.Windows.Controls.Button.OnClick()
   at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
   at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
   at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
   at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
   at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
   at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)
   at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
   at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
   at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
   at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
   at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
   at System.Windows.Input.InputManager.ProcessStagingArea()
   at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
   at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
   at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
   at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
   at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
   at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
   at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
   at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
   at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
   at System.Windows.Window.ShowHelper(Object booleanBox)
   at System.Windows.Window.ShowDialog()
   at Autodesk.AutoCAD.ApplicationServices.Core.Application.ShowModalWindow(IntPtr owner, Window formToShow, Boolean persistSizeAndPosition)
   at Autodesk.Electrical.AceCatalogBrowser.WindowedHost.ShowDialog()
   at Autodesk.Electrical.AceCatalogBrowser.LookupDlg.CatLkup(ResultBuffer args)
   at Autodesk.AutoCAD.Runtime.CommandClass.InvokeWorker(MethodInfo mi, Object commandObject, Boolean bLispFunction)
   at Autodesk.AutoCAD.Runtime.CommandClass.InvokeWorkerWithExceptionFilter(MethodInfo mi, Object commandObject, Boolean bLispFunction)
   at Autodesk.AutoCAD.Runtime.CommandClass.CommandThunk.InvokeLisp(); error: ADS request error

Clearly the problem is with the linked table, as I can rename the original MISC_CAT table (not linked to SQL) and it works just fine.  I have no clue when this stoppped working (sometime over the last 2 weeks).  I have tried using all available drivers to create the table: the generic SQL Server, SQL Server Native Client 10.0, and SQL Server Native Client 11.0.  I've also tried linking the table with and without primary keys.

Thanks,

Jeremiah

[Anonymous · Accepted solution]

As it turns out my theory of using linked tables in Access to point to our SQL database is moot.  Apparently the good folks at Autodesk have finally added support for SQL as a catalog connection.  So I guess I'll be upgrading to 2017 instead... problem solved (kind of).

[dougmcalexander]

Yes, 2017 supports SQL for both the catalog database and the footprint lookup database.

[galashkina]

What are the benefits received by the user, using the SQL server?

[Anonymous]

For one, there have been rumblings of Microsoft discontinuing Access for years.  It could be easily replaced with a local installation of SQL Server Express Edition.  Comparing Access to SQL (even the express edition) isn't even fair, the flexibility and control of SQL Server far outweigh that of Access so much so that I wouldn't even know where to start.

The biggest advantage though is that for most of us, we work at companies that already have a database in place for purchasing/manufacturing of parts.  That database, though it clearly does not have ACADE specific information (Footprint Lookup, Symbol Info, etc), it does very likely have general information: an internal part number, manufacturer, manufacturer number, part description.  It's also likely that ERP database is in SQL.  Prior to the 2017 release, this would mean that the only way to make that information usable and consistent in ACADE, was to copy that information out of SQL and into Access.  Of course, now the problem becomes maintaining two separate parts databases that have an overlap of information.

I have not tested it yet, but it seems that the 2017 release has fixed this issue by allowing some sort of migration to a SQL database (or, I assume local server if need be).  Now, you still end up with two data bases with overlapping information, but maintaining all of that information and ensuring that it stays accurate is a much easier job with two SQL databases (especially if they are located on the same server) than using Access.

[Anonymous]

Sorry, kind of went on and on there and didn't even answer your question.  There are no benifits to the user.  As a matter of fact, if you do it right, the user will never even know that something has changed.  The benifits are all behind the scenes.  Your CAD Administrator, DB Administrator, IT department, whoever you have that is managing catalog data.

[galashkina]

Thank you so much!
If possible, I would like to understand what "The benifits are all behind the scenes."

[Anonymous]

Take a look at the answer to this post from StackOverflow regarding Access vs SQL Server (*Note: this is a comparison to SQL Server Express Edition, which is a limited version of the full SQL Server, but it is free).

As for what it means to ACADE, it totally depends on your application and what you are trying to do.  If your installment of ACADE uses the standard libraries and catalog data, SQL does nothing for you Access doesn't already do.  Basically, your ACADE installation is running on an island without any interface or intent to interface with any type of ERP (Enterprise Recource Planning) system.

That might be the case for smaller businesses or integrators that use ACADE.  Most larger companies (especially OEMs) have other departments outside of engineering (manufacturing, production planning, purchasing) that also make use of catalog data to do their various functions.  If that's the case, the company has generally invested in some type of ERP (Enterprise Resource Planning) system to aid in those functions.  ERP systems are really just large databases of information with elaborate front-ends to enable users to do whatever function they need to do, but a lot of them use SQL servers to manage the database back end.

All of this is really based on one very simple principle: having the same information exist from two different sources creates problems.  But having two different sources for infomation is sometimes inevidable, so we do things to try to make sure that the information stays consitent from source to source.  For instance, my current application calls for a separate routine that runs on one of our servers that updates information in the ACADE database with information from our ERP system.  This is super clunky and requires a text output from the ERP system with changes be stored in a specific location and there are any number of things that can go wrong and hose up the ACADE database.  Also, the text output is created bi-weekly, so there are lapses where the information in ACADE is not current.

That's what started this thread in the first place.  I was attempting to create the MISC_CAT table as a linked table directly to and SQL table (actually a view of multiple tables) to rid myself of that ugly synch process.  As you can see, I met some resistance with ACADE in doing that.  But all of it was really just a work around for what I really wanted - support for SQL tables.

This is the "behind the scenes" type of operation I was referring to.  But SQL support for ACADE goes further than that.  As administrators, we are no longer tied to the table schemas that have been predefined for us by ACADE.  Those schemas simply become "what ACADE expects to see".  I can create those schemas by creating views from all of my existing ERP tables (sure I'll have to add a few).  But now the information that ACADE shares in common with my ERP system will come from the same source.

Hope that answers your question.  Full disclosure: at this point I have not tried anything regarding SQL and ACADE.  I can't say for sure what the capabilities are from ACADE and won't know for sure until I've tried.  However, the information I've seen from Autodesk regarding ACADE 2017 looks very promising.

[galashkina]

The best phrase is "However, the information I've seen from Autodesk regarding ACADE 2017 looks very promising."   

[galashkina]

Maybe, using SQL, users have more options for changing the reporting format (Bill of Material, From / To, etс) ?
For example, users will be able to combine multiple columns into a single column, to combine multiple lines on a single line, to add to the report headers for sections, and more complex sort records.
SCR will help in this?

[Anonymous]

ACADE is what determines what your reports look like, and this is determined regardless of the back end (database).  ACADE pulls information from the database to populate the reports, but it's ACADE that determines what will be shown on that report (with some input from the user).

To simplify this whole conversation, the advantages of SQL over Access have nothing to do with ACADE and everything to do with managing data.  If you have no interest in managing your catalog data outside of ACADE, SQL has very few advantages to offer you.

[galashkina]

"To simplify this whole conversation, the advantages of SQL over Access have nothing to do with ACADE and everything to do with managing data.  If you have no interest in managing your catalog data outside of ACADE, SQL has very few advantages to offer you."

Thank you!

[dhouck]

ACADE 2021

We utilize the Access linked table to SQL method and the stand alone Access method to link to our catalog data as well...  We (our group and customer techs - simultaneously) support many customers with many different catalogs...in locations that may or may not be connected to the SQL Server.  ...so, we need a way of switching catalogs, when we switch customers and for every customer the catalog needs to be shared among the development group.

Columns of part / customer specific attribute information have been added and are used daily.  We have found that this works with the Access file structure, but does not with the SQL file structure...unless ACADE uses the Access (mdb) tables that are linked to SQL.  ACADE thinks it is using Access, but the table data is linked to SQL...and attribute information can be managed.

We have daily jobs that run against the SQL server to generate stand alone Access files, to support remote disconnected work.  The tech just grabs a copy before they depart for the disconnected location. ...and we'll sync any changes that are made on the disconnected location.  

...the Access link method works, until it doesn't... 

...and it doesn't seem to be related to the Access table links to SQL.  (Connectivity, can be verified by opening mdb with Access locally and externally  DAO / ADO / etc... this would indicate that it is not an accessibility issue.)

It seems to be in the mechanics of how ACADE connects to the default_cat.mdb (or <other>_cat.mdb) file.

How this connection work, on how to make this connection reliable, obtain better verbosity and additional transparency in the errors that occur... ?

Normally, when the error occurs, selections are disabled, there is no data present and only the Exit / Close / Cancel options are available for navigation.

We have several customers that would benefit to a solution.