mimisdutch
12-03-2008, 02:43 PM
Hi to all,
I am new to this forum and to vba also. I have this question:
I managed to obtain a recordset using QueryDef.OpenRecordSet which is return from a stored procedure on SQL Server back end. The code I use is the following:
Dim strSQL as String
strSQL = "exec test_stored_procedure"
Dim connectionString
connectionString = "ODBC;Driver={SQL Server};Server=(local);Database=MyDatabase;Trusted_Connection=Yes;"
Dim qdf As QueryDef
Dim dbs As Database
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = connectionString
qdf.SQL = strSQL
qdf.ReturnsRecords = True
Dim myrs As DAO.recordset
Set myrs = qdf.OpenRecordset()
After I get the recorset, I use the following code to put it in a subform and show the results.
Set Forms![My_Form_Name]![SubForm_Name].Form.RecordSet = myrs
Forms![My_Form_Name]![SubForm_Name].Requery
Everything shows fine but I need something more. My subform isn't editable like it would be if I used a standard query as a recordsource for this form. Is there a way to make it editable? If there is not, how can I execute stored procedures on the sql server, get the results from them and put them in a form which will be also editable since that is the purpose I used QueryDef? I hope you understood my question. Please let me know if you have any idea since time is pressing.
Thanks in advance.
I am new to this forum and to vba also. I have this question:
I managed to obtain a recordset using QueryDef.OpenRecordSet which is return from a stored procedure on SQL Server back end. The code I use is the following:
Dim strSQL as String
strSQL = "exec test_stored_procedure"
Dim connectionString
connectionString = "ODBC;Driver={SQL Server};Server=(local);Database=MyDatabase;Trusted_Connection=Yes;"
Dim qdf As QueryDef
Dim dbs As Database
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = connectionString
qdf.SQL = strSQL
qdf.ReturnsRecords = True
Dim myrs As DAO.recordset
Set myrs = qdf.OpenRecordset()
After I get the recorset, I use the following code to put it in a subform and show the results.
Set Forms![My_Form_Name]![SubForm_Name].Form.RecordSet = myrs
Forms![My_Form_Name]![SubForm_Name].Requery
Everything shows fine but I need something more. My subform isn't editable like it would be if I used a standard query as a recordsource for this form. Is there a way to make it editable? If there is not, how can I execute stored procedures on the sql server, get the results from them and put them in a form which will be also editable since that is the purpose I used QueryDef? I hope you understood my question. Please let me know if you have any idea since time is pressing.
Thanks in advance.