How to return a value from a stored procedures in a large global CORP SQL Server 13.xxx from MS Access 2007-2016 with VBA

Question

I have a DLL created in C# in Visual Studio that can return the good product output from our company production SQL Server.

I have a MS Access database witch I have ADMIN privilege to where I in the frontend have VBA coded a DLL object call with this parameters see "@ in bold below" from a stored procedure Biop_ProductionCountersInUTC in a Report reader database called POINTDBReport.

I'll guess that it is a clone database of a real database for read only purpose.

These are the parameter settings in Biop_ProductionCountersInUTC stored procedure:

• @Equipment (varchar(255), input, No default) aka '62' in the query below
• @Counter (varchar(255), input, No default) aka ''OutputGood' in the query below
• @Start_time (datetime, input, No default) aka @FROM in the query below
• @End_time (datetime, input, No default) aka @TO in the query below

The stored procedure returns an integer.

The DLL function that is call from VBA in the Access database is functioning very well, but I would like to change the call to the SQL Server from the DLL to a VBA code inside my Access database instead. I'm kind a stock in this VBA code after several hours of Google / Youtube search.

I have no privileges in this SQL Server database to e.g. create a new stored procedures that I can call from a QueryDef method or something alike, only a query-call like the SQL Server Query that is listed below or some type of query pass-though maybe.

Then I execute this query that is copy&paste from the DLL source inside SQL Server Management Studio v18.7.1 as a new query I'll get the 'Output' value as a sum as expected. so this SQL Server query works just fine.

This SQL Server query, and it is the same as in the DLL.

DECLARE @FROM AS DATETIME2 
DECLARE @TO AS DATETIME2 

SET @FROM = '2021-01-22 05:00:00' 
SET @TO = '2021-01-22 13:15:00' 

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

INSERT INTO @dt 
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

SELECT 
SUM([Value]) AS 'OUTPUT' 
FROM @dt

but then I run this VBA code below in debug mode I'll get an error code 3078 message listed below the code. The error occurs after the SQL connection, when the code hit the "Set recordset", the connection to SQL Server seems to work just fine so fare

This is the VBA code so far:

Function GetGoodOutoutFromPoint()
On Error GoTo Errhandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim conn As String
Dim lsql As String

lsql = "DECLARE @FROM AS DATETIME2 " & vbNewLine & _
    "DECLARE @TO AS DATETIME2" & _
     vbNewLine & _
    "SET @FROM = '2021-01-22 05:00:00'" & vbNewLine & _
    vbNewLine & _
    vbNewLine & _
    "SET @TO = '2021-01-22 13:15:00'" & vbNewLine & _
    vbNewLine & _
    "DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID]
 NVARCHAR(255))" & vbNewLine & _
    vbNewLine & _
    "INSERT INTO @dt" & vbNewLine & _
    "EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO" & vbNewLine & _
    vbNewLine & _
    "SELECT" & vbNewLine & _
    "SUM([Value]) AS 'OUTPUT'" & vbNewLine & _
    "FROM @dt"

conn = "Driver={SQL Server Native Client 11.0};Server=xxx;Database=POINTDBReport;Uid=xx;Pwd=xxx"
Set db = OpenDatabase("", False, False, conn)
Set rs = db.OpenRecordset(lsql, dbOpenDynaset, dbSeeChanges) ' Here occurs the error 3078

'Get the good output data an due something with it.
Set rs = Nothing
Set db = Nothing

Errhandler:
If Err.Number <> 0 Then
    MsgBox "Error retrieving data from Point SQL Server" & vbNewLine & vbNewLine & "error code " & 
Err.Number & " - " & "error decriptipon " & Err.Description _
    & vbNewLine & vbNewLine & "Contact Admin", vbCritical, "Error"
    
 If Err.Number = 3078 Then Set db = Nothing
  End If
End Function

'************************************************************************************

This is the error I get:

Error code 3078 - error description
The Microsoft Access database engine cannot find the input table or query
'DECLARE @FROM AS DATETIME2
DECLARE @TO AS DATETIME2
SET @FROM = '2021-01-22 05:00:00'
SET @TO = '2021-01-22 13:15:00'

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255))

INSERT INTO @dt
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO

SELECT
SUM([Value]) AS 'OUTPUT'
FROM @dt'.

Make sure it exists and that its name is spelled correctly.

Is there someone in the community that can figure this out, maybe tweak my VBA code a bit

Answer 1

  Dim strFrom       As String
  Dim strTo         As String
  Dim SumResult     As Currency
  
  strFrom = "'2021-01-22 05:00:00'"
  strTo = "'2021-01-22 13:15:00'"
  
  With CurrentDb.QueryDefs("qryPT")
     .SQL = "EXEC MyGetSum" & strFrom & "," & strTo
     SumResult = .OpenRecordset()(0)
  End With

Now of course the above assumes that you have a stored procedure saved as MyGetSum.

It would look like this:

CREATE PROCEDURE MyGetSum
   @FROM DATETIME2,
   @TO DATETIME2
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

   INSERT INTO @dt 
   EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

   SELECT 
   SUM([Value]) AS 'OUTPUT' 
   FROM @dt
END

Now, of course it is VERY possible that you do NOT have a saved stored procedure server side, and for some reason (like world poverty), you have to have all of the code, lines that you have as per above?

Ok, then I would create Two pt queries in Access. The first one is our base t-sql + sql code. You grab that, set the values, and then run it in the 2nd pt query. We again do this, since writing all that in-line t-sql in VBA is a real mess (trying to save world poverty here).

So, I would create the first PT query in Access. You can (have to) type in free form text into the query (but in MS-ACCESS SQL view mode). So you have this:

DECLARE @FROM AS DATETIME2 
DECLARE @TO AS DATETIME2 

SET @FROM = '@@FROM'
SET @TO = '@@TO'

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

INSERT INTO @dt 
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

SELECT 
SUM([Value]) AS 'OUTPUT' 
FROM @dt

So we place the above in our PT query. We can "think of" this query as our source query.

So, it will look like this in MS-access:

Ok, we save the above as ptSumS (we tag a "s" on the end for source).

In fact the above does not really have to be a PT query, but that will prevent the access query editor from messing with the sql text as we have above.

Now, create the 2nd PT query (that is the query we will actually run).

So, now our code looks like this:

  Dim strFrom       As String
  Dim strTo         As String
  
  Dim SumResult     As Currency
  Dim strSQL        As String
  
  strSQL = CurrentDb.QueryDefs("ptSumS").SQL
  
  Debug.Print strSQL
  
  
  strFrom = "2021-01-22 05:00:00"
  strTo = "2021-01-22 13:15:00"
  
  strSQL = Replace(strSQL, "@@FROM", strFrom)
  strSQL = Replace(strSQL, "@@TO", strTo)
  
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     SumResult = .OpenRecordset()(0)
  End With

So, either way, using a PT query is the way to go.

The first example - calls a existing stored procedure (so if you have the ability to write + create + save that server side stored procedure, then you can call + use + consume it with the first example.

However, if you don't have the ability to save + create a stored procedure, then we use the 2nd approach. I created the T-SQL as a pt query, since trying to write all that garbage in the VBA editor is too much pain - just type in the t-sql you have into that "handy dandy" pt query. We never run that first PT query - but are using it as a place holder for the t-sql code. It also means we can modify that t-sql - and not have to change the VBA code.

So, we replace the two date parameters , and then run the 2nd example. And by using pt-queries, then we eliminate all the need for connection strings in code (again - really messy in code if try and spew connection string code all over the place).

And while I shoved the results into the currency VBA type, we could for example do this:

  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
  End With

Now RIGHT after above - since we setup the t-sql, we can now run a report, or even a simple access query that is BASED on the above pt "qrySum". From Access point of view, that query will work just like ANY other plane jane query. So right after above, we could launch a report based on qrySum eg:

docmd.OpenReport "rptMyReport",acViewPreview.

Or we could shove the results into a record set like this:

  Dim rst        As DAO.Recordset
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     Set rst = .OpenRecordset()
  End With

  debug.print "sum result = " & rst(0)

So you could build up the strSQL in code, and still use the above final code snip, but might as well just type in the whole sql mess into a query, execute a replace on the two values and then shove the resulting sql into that 2nd PT query. We don't modify the first one, since if we "replace" the two date parameters, then after that, how can we know what the last values were - and thus can't use the source query over and over.

However, REGARDLESS, you MUST ADOPT the native 17 or later drivers, since WHEN sql server is using datetime2 (as opposed to datetime), then the standard legacy odbc drivers return datetime2 values as strings - and that is a mess of epic proportions. Same goes for reports and just general use of the data in Access - you REALLY have to ensure that you using the newer native 17 odbc drivers. Or I suppose you can modify the server side table(s) and change the datetime2 columns types to datetime (but that in near all cases is not a practical suggestion as it would mess with existing server side code and queries).

Edit: Setting up the connection from/inside of Access.

So we have two pass-though queries in Access. The FIRST query as noted is just a place holder for the t-ssql code. We don't need (nor even want) a conneciton string. To create the first PT query:

It will now prompt for a table - close that dialog.

So, now click on this:

Now change the view to SQL view - with this:

Ok, so now we can paste in our first (source t-sql). That will give us the original screen shot I have in above.

so we have this:

Save the above - we calling it qrySumS

Now, close that, and create our 2nd query.

Again, PT query, but this time do this:

So we display the property sheet. This is WHERE we will setup the conneciton string. (as noted, we could do/have/set the connection string in code - but hey, the UI is less work.

So in the property sheet, click on the connection builder:

This will launch the ODBC connector dialogs - and that is WHERE you can setup + enter the same previous information you shared.

Select new, and then select the ODBC driver.

AS WARNED - try and select a native 17 driver.

You CAN try using "SQL Server" from that list (if you don't have native 17 installed). But that can cause issues with datetime2. "sql server" as a odbc driver is built into ALL copies of windows. If you choose native 17, then you have to install the native 17 driver on all workstations that use the access application.

Ok, so work though the dialogs.

In your case, the server name + sql instance looks to be:

SQLDKBA185

and then user + password

And DO NOT skip the database selection - often so many do!!!

This one:

When all is said and done, you wind up back to the original starting point, but with the name of the connection you typed in

And along the way, there is a "test connection" - do try it.

so, you COULD type in, or even cut+paste in the connection string, but the above connection builder (gee, I use it 10 times a day), should result in this:

Now, this 2nd query can be left blank, since our VBA code fills it out (copies from the first query - replaces the params. We save and called this qrySum in our example code.

So you can type/paste in the connection string. and you can of course use VBA code, but it makes the most sense to just use the connection builder. Note the syntax is VERY similar to the .net connection - but is often a wee bit different - so that's why I suggested to use the connection builder. (we using ODBC as opposed to .net sql provider - but you can often look at one, and translate - re-type to the format required by Access (ODBC format).

Edit #2 If you can determine that the datetime columns are NOT datetime2, then change the t-sql code to use datetime, and you CAN USE the "sql driver" and thus not have to adopt + install native 17 (or later) odbc drivers on each workstation that is to use this application. So, if you use "sql server", then this should/could work on all workstations WITHOUT having to install the later native 17 drivers. This however IS REQUIRED if the table(s) in question on the server ARE using datetime2 as opposed to datetime columns. Do NOT ignore this advice here - much pain and suffering will result if you don't address the datetime2 vs datetime issue.