March 24, 2008 at 10:14 am
Does any body know how to return a result set back to their MS Access session? Here is a simple sample:
declare @strVariable varchar(20)
set @strVariable = 'This was a success!'
select @ddd as NEW_FIELD
Now, when I execute this in my 'SQL Mgmt Studio' session, it returns the value and the new column name "NEW_FIELD" successfully.
But some other programmer told me that since the TSQL code is not based on any physical table on the SQL Server, the returning data is not formatted as a "recordset". So here is my code in my MS Access session:
///////BEGIN CODE////////
Dim strSQL As String, rstResult As New ADODB.Recordset,g_cnnExternal As ADODB.Connection
Dim strCaptureResult as String
g_cnnExternal.Open "SQLOLEDB;Description=MyRole;DRIVER=SQL Server;SERVER=myserver;DATABASE=D*****;Trusted_Connection=Yes"
strSQL = "declare @strVariable varchar(20)" & vbCrLf
strSQL = strSQL & "set @strVariable = 'This was a success!'" & vbCrLf
strSQL = strSQL & "select @ddd as NEW_FIELD"
rstResult.Open strSQL, g_cnnExternal, adOpenKeyset, adLockOptimistic
strCaptureResult = rstResult!NEW_FIELD
///////END CODE////////
When I display my recordset "rstResult" in the watch window, Access does not even open the recordset! Somehow I need this value "NEW_FIELD" returned back from the SQL Server to my local variable!
Am I going about this wrong? Please Help!
March 24, 2008 at 1:04 pm
I'm not sure what the developer you spoke to said, but whether or not ADO can create a recordset has nothing to do with the underlying query being based on a physical table.
I would guess your problems come from your command or cursor settings. You cannot have a an optomistic lock without a resource and your query does not use any tables. Your cursor should be read-only. I would expect ADO to handle this, so the next thing is to specify your command type as text - but I think this is the default.
Finally, your command is a multi-step process and ADO sometimes has trouble with these, so make sure rstResult.NextRecordset does not contain anything.
What you are doing will work. I assume it has a bit more complicated query behind it and this is just a sample, otherwise it looks a bit pointless. From access you can also create a passthrough query and just reference the local query if that makes this easier for you.
March 24, 2008 at 1:10 pm
I wouldn't expect this to return anything, since it looks to me that you'd get an error before you even can get past the SQL parser. You declare one variable, and use an entirely different one (which according to your example has neither been declared nor initialized).
Not sure exactly what this is supposed to do - but it ain't doing much....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 24, 2008 at 2:33 pm
I'm sorry... the last statement should read
"select @strVariable as NEW_FIELD"....and the parcer in mgmt studio said it was a good block of code.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply