Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-18-2017, 01:02 AM   #16
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,800
Thanks: 79
Thanked 1,427 Times in 1,332 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Dlookup Failure

I did preface my comment with AFAIK ....

Several years ago I researched this very topic in connection with one of my own databases.
The consensus online was that it wasn't possible.

The UA thread with Albert reinforced that view.
I've emailed him and got an immediate reply

Anyway, I'm just happy as this has meant I can remove a complex and longwinded chunk of code involving a make table query based on a view of 1.4 million records

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 12-18-2017, 02:53 AM   #17
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Dlookup Failure

Well, that all looks interesting but I have no idea if this helps me or not ;-)
NotAnExpert is offline   Reply With Quote
Old 12-18-2017, 09:44 AM   #18
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,800
Thanks: 79
Thanked 1,427 Times in 1,332 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Dlookup Failure

Quote:
Originally Posted by NotAnExpert View Post
Well, that all looks interesting but I have no idea if this helps me or not ;-)
Sorry if I led your thread away from its original question.

In post 1 you described using a SQL view to 'get round the primary key issue'.
I'm not sure what exactly you meant by that but at least two of us thought you were describing a view which was read only.
If you need it to be editable, you now know how to do that.

TBH I'm not sure I understand what post 9 means either.
Perhaps you need to explain the issue once more

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 12-18-2017, 06:37 PM   #19
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,076
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: Dlookup Failure

Notanexpert,

As for post #9, it looks like you have some blocking going on.

When it happens, Exec sp_who2 and look at the "blk" column.
It will show you which processes are in contention ... then all you have
To do is figure out what they're doing

Hth,
Wayne
__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 12-18-2017, 06:48 PM   #20
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,076
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: Dlookup Failure

Upon further review ...

Does your table have an identity column?

If access sees them, it will want to provide a value (can't do it). So it
Can't be in the "add" linked table.

But it must be in the view fir the "update" linked table.

This gets messy real fast.

If so, you're probably gonna need SQL Server stored procedures to manipulate
The table.

Either way, we need more info ...

Wayne
__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 12-20-2017, 06:32 AM   #21
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Dlookup Failure

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!
NotAnExpert is offline   Reply With Quote
Old 12-20-2017, 06:45 AM   #22
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,435
Thanks: 128
Thanked 1,475 Times in 1,447 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Dlookup Failure

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?

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 12-20-2017, 07:17 AM   #23
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Dlookup Failure

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
NotAnExpert is offline   Reply With Quote
Old 12-20-2017, 07:43 AM   #24
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,435
Thanks: 128
Thanked 1,475 Times in 1,447 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Dlookup Failure

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?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 12-20-2017, 07:44 AM   #25
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Dlookup Failure

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 by NotAnExpert; 12-20-2017 at 07:53 AM.
NotAnExpert is offline   Reply With Quote
Old 12-20-2017, 07:56 AM   #26
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,435
Thanks: 128
Thanked 1,475 Times in 1,447 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Dlookup Failure

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/down....aspx?id=53339

I would try it on a test environment.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 12-20-2017, 07:00 PM   #27
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,076
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: Dlookup Failure

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
__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 01-03-2018, 01:34 AM   #28
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Dlookup Failure

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 :/

NotAnExpert is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter Failure Gnasch General 4 07-17-2011 09:56 AM
Method Failure jkfeagle Modules & VBA 7 03-16-2010 12:05 PM
Mean Time Between Failure bfreese1972 Queries 9 09-28-2007 12:24 AM
DLOOKUP Type Conversion Failure Roly Reefer Queries 2 09-03-2005 08:19 PM
Email failure SteveE General 2 06-15-2005 10:10 AM




All times are GMT -8. The time now is 05:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World