ADO Record Set & SQL Query

[Guest]

How do I create a record set with a limited number of fields from a table
rather than all fields? The following sql populates the recordset but only
when an * is used rather than ...tbl_SealModel_Orings.Position,
tbl_SealModel_Orings.DashNumber ...

Thanks,

Jim

Private Sub Command36_Click()

Dim dbcDatabase As ADODB.Connection
Dim rstWettedEastomer As ADODB.Recordset

Set dbcDatabase = CurrentProject.Connection
Set rstWettedElastomer = New ADODB.Recordset

'tbl_SealModel_Orings.Position, tbl_SealModel_Orings.DashNumber

varSQL = "SELECT tbl_SealModel_Orings.Position,
tbl_SealModel_Orings.DashNumber " & _
"FROM tbl_SealModel_Orings " & _
"WHERE tbl_SealModel_Orings.Wetted = True And " & _
"tbl_SealModel_Orings.Model = '180' " & _
"And tbl_SealModel_Orings.SizeMeasurement = '2' " & _
"And tbl_SealModel_Orings.SizeUnit = 'in'"


rstWettedElastomer.Open varSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

rstWettedElastomer.Close



End Sub

 

[Guest]

You have a typo in your DIM statement:
Dim rstWettedEastomer As ADODB.Recordset
should be
Dim rstWettedElastomer As ADODB.Recordset

But that shouldn't cause the results you describe. In fact, I think the
compiler should reject it with this typo.

TomU

 

[Guest]

TomU,

Good catch on the typo but, it appears the DIM statement does nothing
because I was able to create the recordset rstWettedElastomer and used it. I
fixed the DIM and tried to use the 2 specific fields again in the select
statement and received the same error. Run-time error
'-2147467259(80004005): Method 'Open' of object '_Recordset' failed.

Thanks for your help...

Jim

 

[Guest]

Hi, Jim.

How do I create a record set with a limited number of fields from a table
rather than all fields? The following sql populates the recordset but only
when an * is used rather than ...tbl_SealModel_Orings.Position,
tbl_SealModel_Orings.DashNumber ...

You need to do two things:

1.) In the Declarations section of the VBA code module, place the following
line of code:

Option Explicit

Save the module and then compile the code by selecting the Debug menu ->
Compile <DatabaseName>. Fix any errors, save and compile again until there
are no more syntax errors. You should have this option set for all modules
in the database. For more info on this setting, please see Tom Wickerath's
tip, "VB Editor Option Settings," on the following Web page:

http://www.Access.QBuilt.com/html/gem_tips.html

2.) Either change the field name "Position" to something else (it's a
reserved word in ANSI SQL-92, which Jet 4.0 uses) or place brackets around
the field name whenever using it in a query. For the list of Jet 4.0
Reserved words please see the following Web page:

http://support.microsoft.com/?id=321266

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[Guest]

Gunny,

Thanks for the help and the links...

Thanks,

Jim

 

['69 Camaro]

You're welcome. And thanks for marking my reply as an answer to your
question. It's very much appreciated.

Gunny