Dlookup Failure

[NotAnExpert]

Hi Wayne and others, thank you all for the kind replies.

I have had a look and Access is only using 'linked' tables, which are from sql table views. These SQL Views can be made from multiple tables to make it easier for Access to have everything in one place.

I've opened up the database today and watched in horror as all the office users faces dropped thinking they'd lost all their work and Sage 200 hung on every machine again.

Really must figure this out!

 

[Minty]

If you open one of those linked tables "views" can you edit any of the fields ? (obviously be very careful if this is your live sage data)

If you can then the views are linked in a way that may impose record locking, very strange that the whole table is locking rather than one record though.

What ODBC driver is being used?

 

[NotAnExpert]

Hi Minty, thanks for the response, I will see if the data can be edited from with the Access Linked Table.

Oddly, the SQL ODBC Driver is the one found in the , having looked at the version I used to create the System DSN, it is:

version: 6.01.7601.17514, SQLSRV32.DLL, 21/11/2010

EDIT: Just checked, seems there is a SQL Server version 13 available, would the version provide more options?

Apologies, my profile name should be a clue, I have no idea what can be done by what when it comes to these database connections.

Kindest regards

Craig

 

[Minty]

I think that the original one is probably the correct one for the server, I have seen weird things when using an older / newer one on the wrong flavour of SQL server though.

Do you know which version of SQL your sage app runs on?

 

[NotAnExpert]

Hi Minty, it's the newest version of Sage 200c, running on Windows Server 2016, on SQL Server 2016

Is it possible that the database is seeing this ODBC connection as an extra connection and thereby locking others for that reason?

 

[Minty]

Okay version 13.1 appears to be the latest flavour to be able to take advantage of the features availability in SQL Server 2016.

https://www.microsoft.com/en-us/download/details.aspx?id=53339

I would try it on a test environment.

 

[WayneRyan]

Not,

I see that your initial post had a DLookuo on dbo_jobview.

If this view joins multiple tables there's a chance that it might not be updatable at all. Especially if it has any domain functions (min, max, ...)

More importantly if that view is comprised of the joins OF OTHER views, then you can open up a whole world of performance problems. Don't let them do that !!!

Building views based on other views takes away all of the Server's strong points like PKs, indexes and statistics. I've seen these perform 10,000 times slower than views based on regular tables. Even with minimal data this can hurt. How big are your tables.

Still need more info ...

Wayne

 

[NotAnExpert]

Hi Wayne, no, the views I am currently creating only look at the original tables, not views of views.

The tables themselves are brand new and only have a few thousand rows. However our 'stock' table is now about 150k rows and growing. This shouldn't be causing me an issue though :/