Best way to retrieve a value from a table

  • Thread starter Thread starter Diego Lotti
  • Start date Start date
D

Diego Lotti

Hello,

I want to migrate all my db into an adp project connected to SQL server
2000.

I'm wondering what is the best way to retrieve values from a table and use
them in my vba code.

I'm looking for the equivalent of the dlookup function.
I know I can use dlookup but I read that it produces "weird" results. Is
this true?

For example:
I want to get the value of the "Checked" field of a table based on the ID.

in normal mde I would use:
blChecked=NZ(Dlookup("Checked","Table1","ID=xxxx"),False)

How could I get that value now? It's normal to use a recordset even for 1
field?

Thank you very much
 
You should use ADO Recordsets for this, or Stored procedures. DLookups are
slow in this environment.

HTH,
J. Clay
 
Thank you.

But don't you think that open a recordset based on a SP is too expensive for
retrieving a single value ?
Is there any other solution? Opening, binding, reading and closing a
recordset does not waste time?

You told me to use Stored procedure. It's ok, but how can I pass the result
of a SP to my Vba code? I have always to open a recordset. Do I miss
something?

Bye
 
1) I use recordsets and/or stored procedures to get one value quite often.
It is very fast as you use the current connection. No mater how you get it,
the underlying activitiy will involve a recordset of some sort since that is
how Access is going to retreive a value.

2) I would consider an SP with an output parameter as it will run at the
server VERY fast. Here is a VBA function I use to get the current SQL user
by calling an SP.

Public Function GetUser() As String
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "GetCurrentUser"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("strCurrentUser", adVarChar,
adParamOutput, 20)
cmd.Parameters.Append prm
cmd.Execute

GetUser = cmd.Parameters("strCurrentUser")
Set prm = Nothing
Set cmd = Nothing
End Function


HTH,
Jim
 
Thank you very much!

I have no experience of adp and I feared that opening a recordset for
retrieve e single value was slow.
Now the point is clear: since I use the current project connection, the
execution of a SP or the binding of a recordset are very fast operations!

Thank you also for the output parameter info, I will use this approach when
I need a single value.

Bye
 
Back
Top