storing SQL result into a VB variable?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's my question, I am using a button to peform the following event code,
basically I am trying to store the result of a quiery into a VB variable for
later use... how can this be done?? any help is thanked in advance again!

Dim MyVariable As String

MyVariable = DoCmd.RunSQL "SELECT Name FROM createNeweDLPIRecord WHERE
id=1434;"
 
Dim rst As ADODB.Recordset
Dim MyVariable As String

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open "SELECT YourField FROM YourTable WHERE YourCriteria"
MyVariable = .Fields("YourField")
.Close
End With
Set rst = Nothing

You can, of course, achieve the same goal using a DAO recordset instead of
an ADODB recordset. The choice is yours.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thanks for the Help Brenda,

I get the following error from "Compile Error: user-defined type not
defined" for Dim rst As ADODB.Recordset
Do I need to add a library? which one? how? thanks again...
 
Well, as I said earlier you can do this using either ADO or DAO. If you want
to use ADO, you'll need a reference to the Microsoft ActiveX Data Objects
<version number> library, and if you want to use DAO you'll need a reference
to the Microsoft Data Access Objects 3.6 library, if you don't already have
it. Most MDBs will have a reference to at least one of these libraries,
because Access prior to Access 2000 added the DAO reference by default when
you created a new MDB, Access 2000 and Access 2002 add the ADO reference by
default, and Access 2003 adds both. To add a reference, in the VBA editor,
choose References from the Tools menu. The equivalent DAO code would look
something like ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MyVariable As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT YourField FROM YourTable WHERE
YourCriteria")
MyVariable = rst.Fields("YourField")
rst.Close
set rst = Nothing
set db = Nothing

Oh, and who is this 'Brenda' person, and how come I answer the questions and
she gets the thanks! :-)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I have to store the result of a select wuery into a VB variable and I tried
to use the same code as sammymelbourne did. Isn't there a simple function
that allows to copy the (string) result of a sql-select statement into a
string variable?

Thanks,

Marce
 
If the query's been saved, you could use DLookup against it. If all you have
is a SQL statement, then the approach Brendan outlined is the only option.
 
Back
Top