Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

Easy ADODB Recordsets and Commands in Access

When it comes to interacting with SQL Server procedures and data, I use ADODB exclusively in Access. The technology was developed originally to interface with any data source, not just SQL.

When I first started using ADODB recordsets my code looked like this:

Dim rs as ADODB.Recordset
Dim con as ADODB.Connection

Set con = New ADODB.Connection
With con
.ConnectionString = “Provider=SQLOLEDB;Data Source=ServerSQLExpress,1433;Initial   _ Catalog=MyDB;User ID=johndoe;Password=password”
.Open
If .State = adStateClosed Then
MsgBox “Can’t open connection.”, vbInformation, “Error:”
End If
End With

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = con
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open “Select * from tblClients”
End With

Now imagine the above lines repeated over and over again in my code and you can imagine, it was a chore. To be honest you don’t have to use the code block, but can also accomplish the same thing by just using the open statement, but I believed there was another way.

What I came up with was a central location to open my recordsets and for executing SQL commands using two function calls: OpenMyRecordset and ExecuteMyCommand.

Advantages to my approach

  • By using one connection object for all calls to the server, be that Access or SQL, I’m keeping the connections down to a minimum. When I open multiple recordsets I’m just using one connection, which stays open while my application is open or when the server disconnects me.  Doing so keeps the number of connections down to a minimum and allows for easy modifications going forward.
  • Easy cut over to SQL: I’ll use OpenMyRecordset and ExecuteMyCommand regardless if I’m using Access or SQL Server. Sometimes I’ll start a project using an Access backend, with the intent of moving it to a SQL Server later. If that’s the case I only have to change the conConnection procedure from the Access connection, (CurrentProject.Connection), to a connection string stored in tblProgramOptions. It makes switching from Access to SQL Server a breeze.
  • Fast Connection to SQL: By using ADODB objects and SQLOLEDB connection strings I bypass the ODBC layer altogether and connect straight to SQL, making my app that much more quicker. I’ll be writing an article on SQLOLDB next.

Free Code
To use the code, first open it here, then copy and past it into a new module.

OpenMyRecordset
When I need to open a ADODB recordset in my code I use this procedure to connect, get my data, and then disconnect. Here is how I would use it:

Private Function TestADODB()

Dim rs as ADODB.Recordset
Dim strSQL as String

strSQL = “Select ClientID from tblClients Where State = ‘IL'”
OpenMyRecordset rs, strSQL
With rs
If .RecordCount = 0 Then
MsgBox “No records returned”
Else
‘Do something here
End If

End With

Set rs = Nothing

End Function

Notice the use of the record count property instead of EOF and BOF, you can only use this property if you specify a client side cursor, which OpenMyRecordset does by default.

If you need to open a full recordset that will allow you to add or edit data then you would use:

OpenMyRecordset rs, strSQL,  rrOpenDynamic,  rrLockOptimistic,True

ExecuteMyCommand

Use this function when you don’t need to return a recordset, when you’re doing an action query or executing a stored procedure:

ExecuteMyCommand “Update tblClients Set State = ‘IL’ Where ClientID = 100”

I hope this article prompts you to use ADODB with SQL Server instead of DAO, it’s much faster and can provide you with more flexibility.

If you love this article than consider subscribing to my blog here. I’m also available for speaking engagements and consulting on Access with SQL Server projects, you can contact me here.

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help, SQL Server

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores