Stored Procedures and ADO

abombss · Jul 24, 2000

I would like to know if anyone has attempted to use stored queries and ADO w/ Access 97.  I read a KB article on MSDN discussing a bug with ADO and how to use stored procedures with Access 2000.  I have not yet been able try it with Access 97 and I was wondering if anyone has. qb18

jimmythegeek · Jul 27, 2000

Try something like the following: ========== Public Const ADOConnect As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Reporting;Data Source=SERVERNAMEHERE" Private Sub GetStoredProcedure()     Dim rs As ADODB.Recordset     Dim cmd As ADODB.Command     Dim adoCN As ADODB.Connection          Set adoCN = New ADODB.Connection    ' Normally on initial open of application     adoCN.Open (ADOConnect)                     ' Normally on initial open of application     Set cmd = New ADODB.Command          With cmd         Set .ActiveConnection = adoCN         .CommandType = adCmdStoredProc         .CommandText = "sp_SPNameHere"         ' Use the following two lines if you have a parameter to pass         'Set paritem = .CreateParameter("Keyword", adVarChar, adParamInput, 255, Me.txtValue)         '.Parameters.Append paritem     End With          Set rs = New ADODB.Recordset     rs.Open cmd, , adOpenStatic          ' Do whatever you want here .......          Set rs = Nothing     Set cmd = Nothing     Set adoCN = Nothing End Sub ========== I am not positive I have all of the stuff right, but it's something close to this. All of the ADO I have done is in VB, and it should be close to the same. Unfortunately, I do not have SQL Server available at my present job to test this with, so good luck. Jim Lunde <a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a> <a href=

http://www.cgeek.com>CompuGEEKS</a> Custom

Application Development

edge2000 · Jul 28, 2000

ADO from access 97 does work you will need mdac 2.1 or above 2.5 is latest and to goto to preferences and select ms active data objects , The first section makes the connection , change the vars to your info and the fnc call is to run the stored proc. Dim cn As New ADODB.Connection Dim rs As ADODB.Recordset Dim sys4 As Variant, sys5 As Variant Public Function fncADOConnect() 'Use  This To transfer data to SQL Box - Check tblparameters for connection information 'No Parameters ' Connect to SQL server through SQL Server OLE DB Provider. 'Temp Variables Dim conSQL As String, strDate As String Dim conDB As Database Dim conRS As Recordset On Error GoTo Err_Attach_SQL     Set conDB = CurrentDb()          ' Get Paths     conSQL = "select * from tblParameters where fileno=1"     Set conRS = conDB.OpenRecordset(conSQL, DB_OPEN_SNAPSHOT)                    ' Set connection properties.     cn.ConnectionTimeout = 25     'cn.ConnectionTimeout = 600     cn.Provider = "sqloledb"     cn.Properties("Data Source&quot

.Value = conRS!SQLServerAddress     cn.Properties("Initial Catalog&quot

.Value = conRS!SQLDatabaseName     cn.Properties("User ID&quot

.Value = conRS!SQLLogonName     cn.Properties("Password&quot

.Value = conRS!SQLPassword          ' Open the database.     cn.Open          conRS.close          'Truncate Data From All User Tables The user must be a db_owner     'fncExecSQL ("xp_AddRole&quot

fncExecSQL ("xp_TruncateAll&quot

Public Function fncExecSQL(strSQL As String) On Error GoTo Err_Attach_SQL          Set rs = CreateObject("ADODB.Recordset&quot

' Open recordset.     Set rs = cn.Execute(strSQL)                  fncExecSQL = True      Exit_Attach_SQL:     Set rs = Nothing     Exit Function Err_Attach_SQL:     fncExecSQL = False     MsgBox "Encountered error:  " & str$(Err) & Chr(10) & Error$, , "Attach_SQL Error"     Resume Exit_Attach_SQL End Function Dave <a href=mailto:dab@completebs.com>dab@completebs.com</a> <a href=

http://www.completebs.com>Complete

Bsuiness Systems</a> See website for more info but we can develop most things for most people.

katya · Jul 31, 2000

Stored Procedures are not supported by the Jet engine which you would probably use to communicate with your Access database. ADO works very well however, with both Access97 and 2000. I have written extensively for both including ADOX and it all works fine. With the limitations of Jet it is best to pass the sql as adCmdText. If you want to use command procedures then you need to be running Sql Server as your engine. A good book is ADO Programming in VB6 by Holzner which covers everything. Watch out that you always set the cursor mode to client with jet when working with ADODB recordsets as otherwise the cursor content is unavailable. good luck

LonnieJohnson · Aug 30, 2002

JIMMY,

I JUST WANTED TO SAY THAT I JUST USED YOUR CODE AND IT MADE ALL THE DIFFERENCE IN SOMETHING I AM DOING IN A97 AND SQL 2000. THANKS DUDE. prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Stored Procedures and ADO

abombss

Programmer

jimmythegeek

Programmer

edge2000

Programmer

katya

Programmer

LonnieJohnson

Programmer

Similar threads

Part and Inventory Search

Sponsor