Dlookup Failure (1 Viewer)

NotAnExpert

Registered User.
Local time
Today, 06:46
Joined
Feb 3, 2017
Messages
43
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

AWF VIP
Local time
Today, 06:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 06:46
Joined
Feb 3, 2017
Messages
43
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

AWF VIP
Local time
Today, 06:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 06:46
Joined
Feb 3, 2017
Messages
43
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?
 
Last edited:

WayneRyan

AWF VIP
Local time
Today, 06:46
Joined
Nov 19, 2002
Messages
7,122
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

Registered User.
Local time
Today, 06:46
Joined
Feb 3, 2017
Messages
43
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 :/
 

Users who are viewing this thread

Top Bottom