ADO SQL Execution

[SimonFinn]

Hi Guys

I have developed this SQL statement using VBA and am trying to execute it and load the results into an ADO recordset:

SELECT tblCandidate.CandID, tblCandidate.[Last name], tblCandidate.[First Name], ConvertID.[CV details] FROM tblCandidate INNER JOIN (SELECT [tblCV].[CV details], CLng([CandID]) AS ID FROM tblCV) AS ConvertID ON tblCandidate.CandID = ConvertID.ID WHERE tblCandidate.CandID IN (SELECT [CandID] FROM tblCandAddress WHERE AddID=1 And (Town Like 'Lon*' OR County Like 'Lon*' OR [Post Code 1] Like 'Lon*'))

When I copy the statement directly into a Query and run it in Access it returns records. When I execute it from VBA it does not. I think it may be how i am executing the statement, i have tried this which is what i use in VB6 (with a slight modification) and usually works fine:

Set cn = CurrentProject.Connection
Set ADOrst = cn.Execute(strSQL)

But it does not return any records..

Any ideas?

Cheers Si

 

[RoyVidar]

Hi!

Don't know nothing about VB6, but this is the syntax I use when opening recordsets in ADO with sql string for browsing.

dim rs as adodb.recordset
dim sSql as string

ssql="Select someting from sometable"
with rs
.activeconnection=currentproject.connection
.locktype=adlockreadonly
.cursortype=adopenforwardonly
.open ssql, options:=adcmdtext
. if not .bof then
'...
.close
end with
set rs=nothing

As you probably are an experienced VB user, you'l know how to find/change the different locktypes/cursortypes (i e dynamic/optimistic for manipulation...).

HTH Roy-Vidar

 

[SimonFinn]

Hi RoyVidar

I have used this code following your instructions:

With ADOrst
.ActiveConnection = CurrentProject.Connection
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open strSQL, Options:=adCmdText
End With

It still returns no records, I printed the SQL statement and ran it through access and it returned the correct records.

Any ideas on why?

Thanks Si

 

[RoyVidar]

Then there might be a difference in how the query executes this and the interpretation and execution of the sql string thru VBA.

Hope someone else might help, cause I'm not fluent enough in SQL to spot what might cause it.

You might consider posting in forum701 or forum705 if you don't get get a solution thru this thread.

Roy-Vidar

 

[SkipVought]

Hi,

How do you have strSQL formatted? Have you copied the SQL text from Access and pasted it directly into a string in VB WITHOUT modification?

I quite often massage the string for readability, and you have to be careful with the end of continuation lines to include a space...

strSQL = "SELECT tblCandidate.CandID, tblCandidate.[Last name], " & _
    "tblCandidate.[First Name], ConvertID.[CV details] " & _
    "FROM tblCandidate INNER JOIN " & _
        "(SELECT [tblCV].[CV details], CLng([CandID]) AS ID FROM tblCV) AS ConvertID " & _
        "ON tblCandidate.CandID = ConvertID.ID " & _
        "WHERE tblCandidate.CandID IN (SELECT [CandID] FROM tblCandAddress " & _
    "WHERE AddID=1 And " & _
    "(Town Like 'Lon*' OR County Like 'Lon*' OR [Post Code 1] Like 'Lon*')) "

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SimonFinn]

Hi Guys

The SQL i posted was the exact SQL that is executed by VBA and access.

I did a Debug.Print strSQL just B4 the execute code and used this in Access.

For this reason i dont think it is an SQL problem and that i do not recieve errors points me towards an execution problem.

Si

 

[taupirho]

Simple answer is ADO just doesn't seem to like a bunch of 'special' characters contained in the SQL string - including '[', '~' and a few others. I had the same problem. Two solutions spring to mind. Rename your column or if feeling particularly brave look into data shaping where '['s are allowed

 

[SimonFinn]

To add to this i have an if statement which runs this statement instead of the one above if the situation is correct:

ADOrst.Open "qryCVandID", CurrentProject.Connection

This runs fine... i have tried to run my SQL Statement in the same mannor and it fails to return records!

Cheers Si

 

[SimonFinn]

Sorry Tom,

I didn't realise that you had posted this when i posted the last one.

Thanks for the info, i thought i was going crazy and couldnt work out why this was happening.

I will save the data to a query through VBA and execute it from there.

Cheers Si