Executing SQL Server Stored procedure from VBA (1 Viewer)

Hi

I have an Access frontend (mdb) with a SQL Server database as backend. Database tables are linked using system ODBC datasource. ODBC driver used for datasource is SQL Native Client, and Integrated Windows Authentication is used. Different users may use different datasources, p.e. connected to master database, or to specific database. To have the access to SQL database(s), the user must be member of specific Active Directory Security Group - this group is defined as a login on SQL Server.

I want execute some Stored Procedure(s) from Access VBA. No recodsets are returned, but it would be nice to get some flag when the stored procedure finishes (the procedure(s) read(s) data from 3rd party application, proccess(es) those data, and update(s) all database tables afterwards - all this takes 10 - 30 seconds, or even less, but it may get messy, when user takes some action at same time).

Currently I tried this test code
---
Private Sub cbTestSP_Click()

Dim strConnect As String
​

Exit_cbTestSP_Click:
Exit Sub

Err_cbTestSP_Click:
MsgBox Err.Description
Resume Exit_cbTestSP_Click

End Sub
---

, but I get the error 'ODBC--call failed'. I suspect the problem is with connection string.

(From where can I read the name of datasource, which is used to link tables - as different users may use datasources with different names, I'd like to avoid using datasource name directly in code? I searched in Debug Window for it - I did see tables there, but no connection info.)


Any good advice is appreciated!
Thanks in advance!


Arvi Laanemets

First, when you get a "ODBC-call failed", this is merely a generic error and to find out the specific errors, you need to enumerate all of errors in the DAO.Errors collection.

Second, have a look at Doug Steele's DSN-less connection sample.

Thanks Banana!

I have now ALMOST working solution.
---
Private Sub cbTestSP_Click()
On Error GoTo Err_cbTestSP_Click
Dim strConnect As String
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef

Dim ConnStr As String
Dim DsnStr As String
Dim FoundPos As Integer
Dim Continue As Boolean
Set dbs = CurrentDb

Continue = True
' A table where application defaults are stored, is used to identify current datasource, used for linking database tables
ConnStr = Left(dbs.TableDefs.Item("dbo_appdata").Connect, 250)
Do While Continue
Continue = (Left(Replace(UCase(ConnStr), " ", ""), 4) <> "DSN=")
FoundPos = InStr(ConnStr, ";")
If Continue Then
ConnStr = Mid(ConnStr, Nz(FoundPos, 0) + 1, 250)
Else
DsnStr = Left(ConnStr, FoundPos)
End If
Loop
strConnect = "ODBC;" & _
DsnStr & _
"Trusted_Connection=Yes;" & _
"DATABASE=MyDatabase"

Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect
strSQL = "exec dbo.StoredProcedure_Test"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
dbs.QueryTimeout = 400
qdf.ODBCTimeout = 200
qdf.Execute
DoCmd.Hourglass False

Exit_cbTestSP_Click:
Exit Sub
Err_cbTestSP_Click:
MsgBox Err.Description
Resume Exit_cbTestSP_Click

End Sub
---
It works fine, so long as dbo.StoredProcedure_Test doesn't contain any queries to other databases. But whenever the stored procedure queries other database (Like: INSERT INTO dbo.MyTable SELECT * FROM [OtherDB].dbo.MyTable; where OtherDB is another database on same server, and the current user is the member of Active Directory Security Group, which has login for all databases on this server), an error 'ODBC--call failed' is returned again.

(Btw, I can't read DAO.Errors collection in debug window for some reason - but the value of err.Source = "DAO.QueryDef".)

The problem wasn't with queries to other databases at all - the problem was with TRUNCATE TABLE command, executed before query filled table with new data. So long the only solution is giving ownership to user group with access to database.