Link to home
Start Free TrialLog in
Avatar of Jason Steward
Jason Steward

asked on

Access Error and Crashing When New Records Are Added to SQL Backend

I have MS Access 365 with SQL Server backend using ODBC links.  I have the latest ODBC driver (version 17).  I am connecting to a corporate resource that assigns SQL view using a Denodo drivers.  The link and data flow works great until a new record is added.  If new records are added to the main SQL tables, Access returns an error: "ODBC--call failed.  Communication error while getting a tuple; Error fetching next row (#26).  This pops up if I have the linked table open in Access and Access does an automatic record refresh.  Takes usually about 2 minutes for this to pop up.  I've noticed that each time I check the main database after receiving this error, there were new records being added.  I went into File, Client Settings, Advanced, and set Refresh interval (sec): to 0.  I also set ODBC refresh interval (sec): to 0.  I also tried these at 2000, but I still seem to get the error.  If I have a form open that's linked to the SQL backend, instead of getting an error, Access just crashes.    IT Dept. can't see any errors in the Denodo drivers on the backend.  Any thoughts on how I can troubleshoot or stop it from erroring-out when new records are getting added?
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

If your Application works fine with the normal SQL BE then the issue is on the Denodo side...just make sure for start that everything works as it should....if its OK then the Denodo driver has issues.
I saw this yesterday but didn't comment because I have no experience with the Denodo drivers.

  With that said, I will say that I would not be messing with them for going against a SQL database.  Microsoft has enough issues as is with Access going against SQL with their own drivers.

  For example, in many cases you need Row Versioning turned on in the SQL Table (and you might want to try that here), have to avoid certain field types (bits, Floats, extended date/time types) in order for things to function properly.

 I suppose this is a mandate from IT though, so your probably stuck with them.  From the error message, it sounds like your getting disconnected from the datasource and I would explore that.

  Create a DSN for the same data source, then work with another tool, such as Excel or WinSQL to run SQL against it.  See if you have the same issues when things are added.  If you do, then it's the driver and/or setup of the virtualized datasource.

  If not, then it's the way Access works with ODBC, and you might have to switch to OLEDB as a workaround.

  and if none of that bears fruit, then I would post in the Denodo community forms.  This seems to be a very specific problem with the driver, and I'd think you'd find the answer there before you would here.  

  I've never seen a question about Denodo here until this one.

FWIW,
Jim.
Avatar of Jason Steward
Jason Steward

ASKER

Thanks so much for your comments, Jim.  Throughout our company there are many types of databases.  Denodo allows creation of views that bridge between every potential data source.  I'm working with the group responsible for bridging those gaps and they can't seem to find an issue on their end.  That Denodo/SQL view is the only source for the data I need to update users of my application.

I connected to the problem view using Excel and so far haven't gotten an error or an application crash like it does with Access.  I also noticed something today...   Access does crash or error as mentioned previously even without new records being added.  It's like the connection just drops off.  I haven't timed it, but it seems cyclical about every 2 minutes.  I definitely seem to be getting disconnected from the data source.  I don't know much about OLEDB, but I'll look into it.  I'll also look into Row Versioning if you believe that could lead to intermittent  disconnects.
@Jason,

You are not running this from a laptop with a WiFi connection, are you?
Nah, it's on a wired corporate secure network.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial