Dlookup Failure (1 Viewer)

NotAnExpert

Registered User.
Local time
Today, 20:13
Joined
Feb 3, 2017
Messages
43
Good afternoon peeps

A question from someone only just walking into SQL, so please forgive that the question I am asking, might not actually be the right question.

I will explain what I have done previously and am attempting to do now:

Access 2010, ODBC connection to Sage 50, worked very well, queries, dlookup etc all worked perfectly.

Access 2010, ODBC connection to Sage 200c which sits on SQL Server 2016, seems to connect to a linked table (which is actually a SQL View to get around the primary key issue) however dlookup returns no values.

I am positive there is a better way of doing this, but not quite knowing what to ask means I don't know where to look for the answer.

After update event for a textbox (txtJobRef) linked to a field in Access:

Code:
Me.txtNewJobRef = DLookup("CustomerAccountName", "dbo_JobView", "DocumentNo = " & [txtJobRef])

Is there anything you can see off the bat that I might be doing wrong?

As always, any help is very gratefully received.

Kindest regards
 

Minty

AWF VIP
Local time
Today, 20:13
Joined
Jul 26, 2013
Messages
10,355
Is DocumentNo actually a number stored in a text field ?

If it's text you'll need some quotes around the criteria
Code:
Me.txtNewJobRef = DLookup("CustomerAccountName", "dbo_JobView", "DocumentNo = '" & [txtJobRef] & "'")
 

Minty

AWF VIP
Local time
Today, 20:13
Joined
Jul 26, 2013
Messages
10,355
Damn that quick typing Baldy... Curses ;)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:13
Joined
Aug 30, 2003
Messages
36,118
The day is just beginning here, so I'm fresh. :D
 

NotAnExpert

Registered User.
Local time
Today, 20:13
Joined
Feb 3, 2017
Messages
43
Well, damn. That was fast... applied the revised code and the Dlookup worked splendid.

OK, is there a better way of doing a Dlookup if there are several fields populated from the SQL View on the after update of a single box?

Kindest regards
 

Minty

AWF VIP
Local time
Today, 20:13
Joined
Jul 26, 2013
Messages
10,355
Yes - Probably the best way would be to change the txtBox to a comboBox control based on the View, and pull in the other fields you want, and then populate the rest of the fields based on the hidden columns of the combo.

This also means they can only select from valid entries. This may not be particularity practical if you are returning 10,000's of records.
 

NotAnExpert

Registered User.
Local time
Today, 20:13
Joined
Feb 3, 2017
Messages
43
Thank you for your assistance so far, however I have run into another issue.

I have a number of access databases that all look at various aspects of the SQL database behind Sage 200. It has been noted that when one particular database is open and is reading from SQL Views, it is stopping Sage 200 from doing its job and causes the program to hang for several users at once.

I can't figure out why viewing 'views' would do this if the Sage 200 program is looking at it's own dedicated tables?

Any insight is much appreciated, i'm just learning about this and help is gratefully received.

Regards

Craig
 

Minty

AWF VIP
Local time
Today, 20:13
Joined
Jul 26, 2013
Messages
10,355
Views are not necessarily read only, they can be updated if the joins in them permit and permissions are set suitably.

As a result you may be opening them in a state that the server thinks is pending a write operation. Unusual - but possible.
 

isladogs

MVP / VIP
Local time
Today, 20:13
Joined
Jan 14, 2017
Messages
18,186
Following on from Minty's comments about views:

SQL views will be read only in Access if either of the following are true
1. They are read only in SQL Server
2. They have no primary key in Access

The only way to set a PK field is to link the view using ODBC and then set the PK field manually at the final step.

If you link the view using VBA, it seems to be impossible to create a PK field which means the view will ALWAYS be read only
 

WayneRyan

AWF VIP
Local time
Today, 20:13
Joined
Nov 19, 2002
Messages
7,122
Ridders,

I think you can create a unique index and Access will let you update the view.

Wayne
 

isladogs

MVP / VIP
Local time
Today, 20:13
Joined
Jan 14, 2017
Messages
18,186
I would be very happy if you can show me a way of creating an updateable SQL view which has been linked by code. AFAIK, that isn't possible.

I had a lengthy discussion with Albert Kallal at UA and we both concluded the only way to create an updateable view was to use ODBC and assign a PK field when prompted at the last step.

However, if you can show me otherwise, I'd be very grateful.
 

isladogs

MVP / VIP
Local time
Today, 20:13
Joined
Jan 14, 2017
Messages
18,186
Hi

Hooray!
I'm going to retract the previous statement as I've just found an article by Juan Soto explaining how to do this:
https://accessexperts.com/blog/2010/03/03/using-sql-server-views-with-access-index-needed-for-editing-data-2/

Here is the article in full

SQL Server views are one of the best tools a Microsoft Access programmer can use to limit exposure to data and improve Access performance.

Unfortunately, you can’t modify data in a view unless it has a unique index, read on if that is the case for you.
(You can create indexed views in SQL Server, this article applies to non-indexed views)

After you have linked your view to your Access application, create a new query or execute the following SQL statement in your code to create the index:

Create Index ix_name On view_name(fields) With Primary

ix_name: The name you assign to your primary index.
view_name: The name of your view in Access.
fields: One or more fields seperated by commas that will compose your primary key.

That’s all there is to it. We hope you can “Discover the Power of Your Data!” with this tip.

UPDATE: Code Example

Code:
Public Sub CreateIndexonView(strIndexName As String, strViewName As String, strFields As String)

Dim strSQL As String

strSQL = "Create Index " & strIndexName & " On " & strViewName & "(" & strFields & ")"

CurrentDb.Execute strSQL

End Sub

Here’s how you would use it in your code:

CreateIndexonView “IDX_OrderID”, “vw_CustomerExpiredOrders”, “OrderID”

I've tested this & it works!
A read only view has now become editable. :)

In my case, I used a composite index based on 2 fields:
Code:
 CreateIndexonView "IDX_PupilTimetable", "vwPupilTimetable", "LessonID,PupilID"

Many thanks Wayne for prompting me to look at this again

NOTE: the index will need to be re-created if you relink the view at a later date
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:13
Joined
Jul 9, 2003
Messages
16,245
that isn't possible

I NEVER say that!

I used to, but found that I was often pointed to a novel solution, resulting in severe embarrassment!


Sent from my SM-G925F using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 20:13
Joined
Jan 14, 2017
Messages
18,186
I did preface my comment with AFAIK .... :eek:

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

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
 

NotAnExpert

Registered User.
Local time
Today, 20:13
Joined
Feb 3, 2017
Messages
43
Well, that all looks interesting but I have no idea if this helps me or not ;-)
 

isladogs

MVP / VIP
Local time
Today, 20:13
Joined
Jan 14, 2017
Messages
18,186
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
 

WayneRyan

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

WayneRyan

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

Users who are viewing this thread

Top Bottom