Why 2 Records after a PassThru + DLookup?

G

Guest

If ReturnRecords = False The
1 record is added but DLookup fails with "Runtime Error 3323 -- The query cannot be used as a row source
I recall seeing that False is really needed for an exec passthru on Googl
ElseIf ReturnRecords = True The
2 records are added and DLookup returns the index of the 2nd record adde
Neither of these 2 records appears in the table until after the DLookup statement -- strange?
End I

CurrentDb.CreateQueryDef ("ZZZZ"
Set q = CurrentDb.QueryDefs("ZZZZ"
q.Connect = "odbc;Driver={SQL Server}; Server=" & ServerName & ";Database=" & DbName & ";uid=;pwd=
q.ReturnsRecords = Tru
q.SQL = "exec spAddResource"
ID = DLookup("ResourceKey", "ZZZZ"

SET QUOTED_IDENTIFIER OFF
G
SET ANSI_NULLS OFF
G
ALTER PROCEDURE dbo.spAddResource AS
SET NOCOUNT O
INSERT INTO tblResources (ProjectID, SourceID) VALUES (7,3)
SELECT @@IDENTITY AS "ResourceKey
G
SET QUOTED_IDENTIFIER OFF
G
SET ANSI_NULLS ON
GO
 
M

Michael Cheng [MSFT]

Hi Sparky,

From your descriptions, I understood that you would like to know why 2
records are returned by DLookup. Have I understood you?

However, I am not sure about your usage of DLookup and I could not
understood your words about "2 records are added and DLookup returns the
index of the 2nd record added" and "until after the DLookup statement".

Based on my knowledge, we can use the DLookup function to get the value of
a particular field from a specified set of records (a domain). Use the
DLookup function in Visual Basic, a macro, a query expression, or a
calculated control on a form or report. I could not fingure out what
'index' is in your descriptions. Would you please show me some sample data
or scenrio that could help me reproduce the issue?

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.


Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
G

Guest

Hi Michael

Thanks so much for responding

The code is used by an Access front-end form which displays tblResources and lets a user edit the fields in the table
or in this case, to add 1 new record using the AddRecord event from a form button. If things worked right
just one record would be added and the IDENTITY value would be returned for the new record

Note that the following pass- through query replaces a Access query which is veeerrrrry slooooow

CurrentDb.CreateQueryDef ("ZZZZ"
Set q = CurrentDb.QueryDefs("ZZZZ"
q.Connect = "odbc;Driver={SQL Server}; Server=" & ServerName & ";Database=" & DbName & ";uid=;pwd=
q.ReturnsRecords = Tru
q.SQL = "exec spAddResource"

Note that q.ReturnRecords is "True" to enable fetching the @@IDENTITY result
If it is false, only one record is added (which is the desired outcome) -- but an erro
is raised when DLookup is executed

This error has been confirmed independently by another expert in my company
So, there appears to be a bug

ALTER PROCEDURE dbo.spAddResource AS
SET NOCOUNT O
INSERT INTO tblResources (ProjectID, SourceID) VALUES (7,3)
SELECT @@IDENTITY AS "ResourceKey
G

Be aware that tblResources has many columns of which 3 relate to the problem - they are

ResourceID int key Identit
ProjectID in
SourceID in

If I directly execute the stored procedure in Query Analyzer, only 1 record is added which is the correct
Sadly, if the proc is called by the querydef, no records are added when the pass-through "exec" statement is
invoked. Rather, the DLookup statement must be executed to create the new record

At that time, DLookup errorneously adds 2 identical records not just one.
And, each new record has a unique value for the ResourceID (i.e. IDENTITY) column

And, DLookup grabs the identity value from the last (the 2nd) identical record added
------------------------------------------------------------------------------------------------------------------
However, I am not sure about your usage of DLookup and I could not
understood your words about "2 records are added and DLookup returns the
index of the 2nd record added" and "until after the DLookup statement".

The purpose of DLookup is to immediately get the value of the identity field of the new record i
tblResources. This identity value is then used to highlight a datasheet showing tblResources
----------------------------------------------------------------------
I could not fingure out what 'index' is in your descriptions.

The index is also known as the IDENTITY or key field which I have named ResourceI

Cheers

Sparky
 
D

Douglas J. Steele

Your DLookup statement refers to the querydef "ZZZZ" that you created.
Calling DLookup means you're invoking the query, and since that query
inserts the row, you're getting the extra rows.

Since the stored procedure is set up to return the id for you, use

CurrentDb.CreateQueryDef ("ZZZZ")
Set q = CurrentDb.QueryDefs("ZZZZ")
q.Connect = "odbc;Driver={SQL Server}; Server=" & ServerName &
";Database=" & DbName & ";uid=;pwd="
q.ReturnsRecords = True
q.SQL = "exec spAddResource"
Set r = q.OpenRecordset
ID = r.Fields(0)

where r is declared as a DAO recordset.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sparky said:
If ReturnRecords = False Then
1 record is added but DLookup fails with "Runtime Error 3323 -- The
Click to expand...
query cannot be used as a row source"
I recall seeing that False is really needed for an exec passthru on Google
ElseIf ReturnRecords = True Then
2 records are added and DLookup returns the index of the 2nd record added
Neither of these 2 records appears in the table until after the DLookup statement -- strange??
End If


CurrentDb.CreateQueryDef ("ZZZZ")
Set q = CurrentDb.QueryDefs("ZZZZ")
q.Connect = "odbc;Driver={SQL Server}; Server=" & ServerName &
Click to expand...
";Database=" & DbName & ";uid=;pwd="
 
G

Guest

I tried to reply 3 times - hope this gets thru.
DLookup is buggy per a Microsoft SRX incident studied overseas today.
They advise MakeTable to ensure the real index not someone elses result is returned
Any links on code patterns for make table??

Thanks for the time, talent and treasures

Sparky
 
B

Billy Yao [MSFT]

Hi Sparky,

Michael is Out of Office and I (Billy) will continue to assist you on this issue.

Looking at the issue, I noticed that you'd like to use the DLookup function for executing a stored procedure.
Based on my experience, DLookup function is used for searching record and not for running the pass-
through query, as it was running an action query.

As you've been suggested, it's better to create a make-table query using the pass-through query. A make-
table query creates a new table from all or part of the data in one or more tables.

1) Creating a table to export to other Microsoft Access databases. For example, you may want to create a
table that contains several fields from your Employees table, and then export that table to a database used
by your personnel department.
2) Making a backup copy of a table automatically by using a macro or code.
3) Creating a history table that contains old records. For example, you could create a table that stores all
your old orders before deleting them from your current Orders table.

To create a make-tabke query, you can refer to the Microsoft Access Help (F1) and search the following
topic in the help document:

"Create a table from another table with a query" (without quatations)


Best regards,

Billy Yao
Microsoft Online Support
 
M

Michael Cheng [MSFT]

Hi Sparky,

I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
Billy's recent information I've posted, please don't hesitate to let me
know directly.

It's my pleasure to be of assistance

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
M

Michael Cheng [MSFT]

Hi Sparky,

Thanks for sharing in the newsgroup!

Mingqing Cheng

Microsoft Online Support
 
Top