ADO Help 2

[bdavis96]

The following is what I am trying to accomplish:

I am using ADO to connect to a Query, but I only need a subset of the Query for the Recordset. My understanding of how to do that is:

rst.Open "Select * From Query WHERE Query.Field = Data;, conn ...

I have another recordset that will be providing the data:

rst.Open "Select * From Query WHERE Query.Field = rstOther!Field;, conn ...

I keep getting the error of too few arguements, so I am lost in how to do this.

Any help would be much appreciated.

 

[Sheco]

The key is to put the value from your PC_FMAPRS into the text string that is used to make the PC_FMIMPR.

If you mention the PC_FMAPRS recordset to the database it won't know what you are talking about.

Therefore, you have to take the value from your PC_FMAPRS recordset and put it into the string that the database will use to make PC_FMIMPR.

See how I did it with the "[red]&[/red]" string concat character to put the value of the field onto the end of the hardcoded SQL string.

Like this:
PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = " [red]&[/red] PC_FMAPRS![APORA#], PC, adOpenDynamic, adLockOptimistic

 

[Sheco]

OK that is a new problem and it is probably what Harleyquinn was just talking about above.

If APORA# is a text field then it needs to be in single quotes for the database to understand.

so like this:
PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = [red]'[/red]" & PC_FMAPRS![APORA#] & "[red]'[/red] ", PC, adOpenDynamic, adLockOptimistic

 

[HarleyQuinn]

Hi,

I'm going to be honest, I don't know what the part in the square brackets is for.

But if it is like normal SQL you could try single quotes...

PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = [red]'[/red]" & PC_FMAPRS![APORA#] & [red]"'"[/red], PC, adOpenDynamic, adLockOptimistic

Might work for you????

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Sheco,

Sorry for double posting, but yes that was exactly what i was refering to.

Cheers for explainig that to bdavis96

Harleyquinn

---------------------------------
For tsunami relief donations

 

[Sheco]

The square brackets with the exclamation point are old school.

Microsoft used to completely change the way it did data access like every 6 months and this syntax was carried over from the pre DAO days...

I guess they kept support for this syntax to minimize the amount of rework you have to do when they change data access technologies.

I don't think any of the ADO documentation uses the brackets with exclamation syntax.

 

[bdavis96]

Perfect, Thank you. I am new at SQL clauses, most of my coding usually can get away with "Select * From Query/Table". I didn't realize I needed the ' ' even though it was a variable value. Everything I found showing the ' ' always had a constant value = '5', etc. I really appreciate the help.

 

[HarleyQuinn]

Cheers for that explaination Sheco, guess I'm pretty much new school SQL.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[Sheco]

Oh and be careful because if it is a NUMERIC field then you don't need the single quotes.

Suppose a value is 5

How you put it in the SQL string will depend on the Data Type of the field.

If you mean it as a number then it doesn't need the quotes... if you mean it as a letter then it does.

I hope that makes sense the way I wrote it.

 

[bdavis96]

What is the newest way of writing the statement? I tend to find something that works and keep with it. The reason I use [APORA#] is that I believe VB has a problem with the pound sign (#) if it isn't in brackets, or at least that was a problem I ran into before and found the brackets to work.

 

[bdavis96]

I understand perfectly. In this case, it is a char field in the database.

 

[Sheco]

The thing with the pound sign is not that VB has a problem with it but rather that your database doesn't like it, probably because it can use # as a delimitter for date values. ie: #02/07/2005#

This is very much like the situation if you have a field named "id" but your database also uses "id" as a reserved word... In that case you need to refer to the field with brackets [id] ... so that the database doesn't get confused.

"What is the newest way of writing the statement?"

ADO recordset objects have a method named .Fields that returns a collection object.

It behaves like a normal collection object:

oRS.Fields.Count is the number of fields.
oRS.Fields.Add(Item) to add a field.
oRS.Fields.Remove(Item) to remove a field
Set myItem = oRS.Fields.Item(Index) to get an individual field object. The index can be either the field name or the zero-based ordinal value.

Suppose you have an ADO recordset object named oRS and it has two fields FName and LName and the values are "John" and "Doe"

oRS.Fields.Count will give you 2
oRS.Fields.Item(0).Value will give you "John"
oRS.Fields.Item("FName").Value also gives "John"
oRS.Fields.Item(1).Value will give you "Doe"
oRS.Fields.Item("LName").Value also gives "Doe"

OK, so now you are probably thinking that is a LOT more work than just using brackets and the exclamation point.

BUT ....

.Value is the default property of an ADO Field object so:
oRS.Fields.Item("LName") will still give you "Doe"

ALSO...

.Item is the default method of the ADO Fields collection so:
oRS.Fields("LName") will still give you "Doe"

FINALLY...

.Fields is the default method of the ADO Recordset object so:
oRS("LName") is all you really need to get "Doe"