Set ADOPAR = ADOCMD.CommandText = "SELECT * FROM Products WHERE =? " ConnectionString = "Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb User Id=admin Password= " I'd like to know if it is possible to get the recordcount of an ADO Recordset that was populated by an ADO Command object and if so, how? I'm sure there are 8 ways to do the same thing and everyone thinks theirs is the best/most efficient/professional/etc. This is purely academia and for my own edification. I know I could hard-code the SQL in the open method of the ADO Recordset but I'm looking for a way to populate an ADO Recordset with an existing query in an Access database that requries a parameter. Since it requires a parameter, I have to use the ADO Command object. However, "Invoices2" is a query I created in the database that takes a parameter. How can I get the recordcount of an ADO Recordset, opened with an ADO Command object? Normally I would open the recordset using straight SQL using adOpenStatic. CommandText = "Select * from Invoices2"Ī "-1" is displayed in the debug window. ConnectionString = "User ID=Admin Data Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb Password=""""" Sub getRecordcountFromRecordsetOpenedViaCommandObject().rs.Status says same "Operation not allowed when the object is closed" and rs.state returns 0. When I put there as the second to last row rs.open, no error is given, but the rs remains closed. StProcName = "thenameofmystoredprocedurehere" 'Define name of Stored Procedure to execute.
'One possible error here can be caused if user has just changed the password to company network and hasn't logged off and back in after that! 'Execute stored procedure and return to a recordset PrmUser.Value = "'" & strUser & "'" 'Tried also without quotes, no difference When the Insert SQL and parameter are passed to the function, Excel locks up. I'm trying to expand it to handle an Insert as well.
It's setup to process queries with 0 - 2 parameters. Set prmApplication = adInteger, adParamInput) I've built an ADO function in VBA to handle queries to an Access database. Set prmUser = adVarChar, adParamInput, 7) StProcName = "GetUserAuthForApp" 'Define name of Stored Procedure to execute.Ĭmd.CommandType = adCmdStoredProc 'Define the ADODB commandĬmd.ActiveConnection = oConn 'Set the command connection stringĬmd.CommandText = stProcName 'Define Stored Procedure to run 'Initialize variables for database connections 'Define connection string and open the connection Dim strConn As String 'Connection string to SQL Serverĭim strSQLtoExecute As String 'SQL query string to executeĭim oConn As ADODB.Connection 'Object for connectingĭim rs As ADODB.Recordset 'Object for recordsetĭim stProcName As String 'Stored Procedure nameįunction LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant
0 kommentar(er)
