Best way to retrieve a value from a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diego Lotti - 20 Dec 2004 15:14 GMT

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

Reply to this Message

J. Clay - 20 Dec 2004 16:30 GMT

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

HTH,
J. Clay

> Hello,
>
[quoted text clipped - 18 lines]
>
> Thank you very much

Reply to this Message

Diego Lotti - 21 Dec 2004 09:08 GMT

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

> You should use ADO Recordsets for this, or Stored procedures. DLookups are
> slow in this environment.

Reply to this Message

J. Clay - 21 Dec 2004 15:12 GMT

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.
>
[quoted text clipped - 12 lines]
> are
> > slow in this environment.

Reply to this Message

Diego Lotti - 24 Dec 2004 07:36 GMT

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

> 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,
[quoted text clipped - 44 lines]
> > are
> > > slow in this environment.

Reply to this Message