Tuesday, August 07, 2012 12:04 PM
I've been searching all morning, and I cannot find an answer to this. Probably because I'm not searching the right question.
It could also be that I'm doing a whole load of things wrong, so I'll run through what I'm doing and how.
We're running SQL Server 2012 Express and connecting via VBA in MS Access 2010, both of which are 64bit editions.
Firstly, I'll run over what works, here is some of my vba code:
Public Function SQL_Server_Execute(TSQL_String As String) ' create a new connection Dim Server_Connection As New ADODB.Connection ' open the connection Server_Connection.Open (connection string) ' check to see if any errors occurred If Server_Connection.Errors.Count <> 0 Then Err.Raise Server_Connection.Errors(0).Number End If ' run the provided statement string and grab the resulting record set Dim Returned As ADODB.Recordset Set Returned = Server_Connection.Execute(TSQL_String) ' check to see if any errors occurred If Server_Connection.Errors.Count <> 0 Then Err.Raise Server_Connection.Errors(0).Number End If ' first see if the recordset is open If Returned.State = adStateOpen Then ' check for number of records If Returned.EOF = 0 Then MsgBox "No records returned" Else MsgBox "Records returned" End If ' close the recordset Returned.Close Else MsgBox "Returned a closed recordset" End If ' close the connection if it was opened If Server_Connection.State = adStateOpen Then Server_Connection.Close End If Set Server_Connection = Nothing End Function
Note - I've removed the validation and some of the error trapping as I'm still trying to get it all working.
Ok, this works fine for executing stored procedures, I can see in SSMS that the procedures run and do their jobs so as far as I know nothing is wrong with the connection.
The problem is with the returned recordset, it always comes back with an EOF of 0, therefore no records.
I've gone through all the help pages I can find about this and everywhere states that the .execute method returns a recordset containing the result of what was executed, this recordset will be closed if no results were returned.
What's interesting is where the code checks to see if the recordset.state is equal to adStateOpen it is resulting in TRUE, which tells me the recordset is open, but the EOF is 0, so it contains nothing.
I've tested this by passing the string "SELECT GetDate() as Time_Now", which runs without errors but returns no results.
Here're the questions I'd like to ask:
1) Am I retreiving the results in the wrong way? As far as I can see I'm doing it right.
2) Is it something in the T-SQL string I need to do differently?
3) Is it possibly something in the Server settings?
I need this to work since I don't see how a database can work when the FE cannot retreive information from the Server.
Thanks in advance.
Tuesday, August 07, 2012 12:23 PM
Just simplify it:
Set Returned = Server_Connection.Execute(TSQL_String) MsgBox Returned.Fields(0).Value If Not Returned.BOF And Not Returned.EOF Then MsgBox "Has rows" Else MsgBox "No rows" End If
Called with SQL_Server_Execute "SELECT @@VERSION".
- Marked As Answer by Avan_Madisen Tuesday, August 07, 2012 1:09 PM
Tuesday, August 07, 2012 1:09 PM
Would've helped if I'd known beforehand that BOF and EOF were boolean types... (face-palm)
Shows how misunderstanding a tiny little detail can cause huge problems.
Thanks for the help.