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()
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
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()
On Error GoTo Err_cbTestSP_Click
Dim strConnect As String
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
strConnect = "ODBC;DSN=MyConnection;DATABASE=MyDatabase;"
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect
strSQL = "exec dbo.RefreshData"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
dbs.QueryTimeout = 200
qdf.ODBCTimeout = 150
qdf.Execute
DoCmd.Hourglass False
Exit_cbTestSP_Click:Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
strConnect = "ODBC;DSN=MyConnection;DATABASE=MyDatabase;"
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect
strSQL = "exec dbo.RefreshData"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
dbs.QueryTimeout = 200
qdf.ODBCTimeout = 150
qdf.Execute
DoCmd.Hourglass False
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