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.ConnectionSet 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 WithSet 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 StringstrSQL = “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 IfEnd 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.
Hi,
Love you blog, its very clear. I am new to SQL server and I am trying to use your scripts to access a Stored Procedure on SQL SErver 2008 with MS Access 2003.
I am getting a few syntax errors int he code and am wondering is your code from a later version of MAS Access? IF so what version please?
Hi Gerry,
The code samples are just that, samples that I grab from development meant to convey the general idea of the post and may not work for you without specific changes. If you reply back with more specifics I would be more than happy to respond.
Thanks
Juan
What is ReadGV function in your code?
Hi John,
Thanks for asking, you can learn more about ReadGV and WriteGV here:
http://accessexperts.net/blog/2011/01/12/multi-session-global-variables/
Kind Regards,
Juan
Hi, thanks for this…first class. I have started to replace my lnked tables and its looking good. One question though if you dont mind. How / can you use a dLookup without a linked table?
Hi Alan
The answer is you don’t. DLookup is not a recognized SQL Server function. Instead use a recordset, for example if you need to lookup company name from tblCompanies using CompanyID:
StrSQL = “Select CompanyName From tblCompanies Where ConpanyID = ” & lngCompanyID
OpenMyRecordset rs, strSQL
In the free code section, if you click here to open the code, you can see the code and copy it into a module as you explain.
The code however uses a call “ReadGV” that is not provided and therefore causes the code to fail.
ReadGV is one of my favorite functions. here’s where you can find it:
http://accessexperts.net/blog/2011/01/12/multi-session-global-variables/
How do I connect to ODBC without having to authenticate each time. When the sub procedure is run, it prompts me for the ODBC connection.
You need to supply the user name and password in the connection string. At our firm we use a custom login form to store the credentials for the session and use them to connect with SQL Server.
Thanks
Juan
Please pardon my ignorance, but i don’t seem to see any connection string to sql server in the code or maybe i’m missing something?
In the connection screen you will see:
User ID=johndoe;Password=password
Replace “johndoe” and “password” with you’re own credentials.
Hope that helps
Juan
Hey Juan it works but i need to test it on another system first. thanks for that
..please why does my database that i developed in access 2010 complains of “unrecognise file format” when i try to open it in access 2007 despite the fact that they are both .accdb?
Unfortunately Access 2010 is not compatible with 2007, you will need to have 2007 users install Access 2010 runtime in order to use your software.
Thanks Juan, but i still have a challenge connecting to my server from another system, it’s still complaining of ODBC issue, which i believe should not be.
What about the difference in SQL/T-SQL? You can switch between connections but what if there is a difference in the syntax of a recordset?
Jurgen,
Most likely that will not be the case since the two data sources are compatible, i.e., if you developed with SQL Server you will most likely be able to use the same SQL since their are minor version changes with TSQL among SQL Server versions.
I tried my new ADO code on a windows XP, Vista, Windows 7 computer but it seems not to work reason : http://support.microsoft.com/kb/2517589/en-us . Why is this? Is there a future for ADO applications?
The short version is that they tried to update ADO to be compatible for 64-bit code but in process broke compatibility with older Windows client.
However, there is now a fix for this problem. On your Windows 7 computer, verify that you’re using Service Pack 1. Install the fix as listed here:
http://support.microsoft.com/kb/2640696
Once installed, update your ADO reference on your Windows 7 SP 1 to use ADO 6.1 and recompile your Access application. That will then run on your older Windows without problems.
Hi Juan,
I currently run an access database that connects to SqlServer 2000 using adodb exclusively. I want to upgrade the SqlServer to 2012, the problem is that i am having a problem getting a definitive answer of if my adodb code will still work on sqlserver 2012 as the back end.
Can you shed any light on this? Any help is appreciated…
The answer is that yes, it’ll be compatible. We recently upgraded a client to SQL 2012 and did not need to update our code or connection strings.
Do be aware, though, that SQL Server team has decided to deprecate ADO provider for SQL Server starting with SQL 2012. What it usually means is that on the 3rd version after the announcement of deprecation, it will be removed from SQL Server and they recommend ODBC instead. In case of SQL Azure, you must use ODBC if you want to connect Access with SQL Azure. So this is something you have to keep in mind and plan ahead for next five years.
Juan! Nice piece!
Just what I was looking for and nice and clear.
Thanks Keith!
Dear Juan
I’ve just moved an MS Access 2010 database to SQL 2005. The main form is a continuous form and so needs to be bound to work. Using a name Access query based on the SQL tables is slow when I try and retreive 10,000s of records.
I tried ADO which provided all the speed I needed by when moving away from an edited record the message always appears:
“The data was added to the database but the data won’t be displayed in the form because it doesn’t satisfy the criteria in the underlying recordset”.
I’ve tried various things including an MS hotfix, setting the Me.UniqueTable property and creating a view in SQL on which to base the form. All my tables have unique keys and I can create a working solution if I create a named MS Access query and bind my form to that but then the performance is dreadful.
My connection code looks like this:
Set cn = New ADODB.Connection
With cn
.Provider = “Microsoft.Access.OLEDB.10.0”
.Properties(“Data Provider”).Value = “SQLOLEDB”
.Properties(“Data Source”).Value = “ERNIESQL2005”
.Properties(“User ID”).Value = “sa”
.Properties(“Password”).Value = “fishheads”
.Properties(“Initial Catalog”).Value = “OccultTest”
.Open
End With’
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = strSQL
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set Me.Recordset = rs
How can I get rid of that error message?
yours in desperation
Chris
Dear Juan
an update on my earlier email: I’ve put your connection code above along with your free code in place of mine that I sent in previous positing.
But when I try and edit any field in the form it is blocked with a status bar message:
Field [fieldname] is based on an expression and can’t be edited.
I feel I’m 99% of the way to a fast editable recordset based on multi table query of sql 2005 tables but I’m just missing something!
Chris
Juan
actually I’ve spotted in using your code above that I didn’t use your special line for making the recordset editable but when I use that then I’m back to my original message that:
The data was added but the data wont be displayed in the form because is doesn’t satisfy the criteria in the underlying record source.
The row I’ve just edited then disappears until I reopen the continous form whereapon it shows my edit just fine.
I don’t understand why the underlying recordset isn’t editable when I’ve set the locks as per your line: OpenMyRecordset rs, strSQL, rrOpenDynamic, rrLockOptimistic,True
And if I switch to a name access query it works (just slowly).
So close….
Hi Chris,
Sorry to hear you are having issues with that approach. To be honest I shy away from using the method described because it’s not usable for editing data, (as you have discovered). Go ahead and use either a linked table or a view, NOT an Access query since it will run too slow.
Keep in mind if you do use a view with multiple tables as your form source, (Access thinks it’s a table when you link it), you will need to address these two issues:
— The view will require an Index for editing data, see this post on that: http://accessexperts.net/blog/2010/03/03/using-sql-server-views-with-access-index-needed-for-editing-data-2/
— If you have a multi-table view and you allow delete records you will need to create a delete trigger on the view so that SQL Server knows which table to delete the records from.
Good Luck!
Let me know how it goes.
Juan
I can not get my OpenMyRecordset, rs, strSql…
Hello, can you provide more details?
Juan:
I followed your suggestion on the Easy ADODB and I am very grateful;
I was replicating all of that code in every module that used record sets.
Also discovered the execution of command line for a stored procedure
with parameters which makes calling and passing the parameters
a lot easier than what I was doing before. I was using the Command object
and laboriously creating and setting the values to each parameter with
separate commands. Never realized I could just specify as follows:
Exec QueryName Param1, Param2
Thanks so much for your BLOG and
your LOVE of Access and SQL Server.
Dick Weber
Thanks Dick!
Hi Juan,
I came across this website a few months ago and immediately subscribed to your blog. Here is my question:
I am using an Access 2010 adp with a SQL Express 2008 R2 database and I am new on VBA programming and using ADO.
I have a bound form with a subform and I want to bind the output (recordset) of a stored procedure to some text controls on the main form.
I am using the following code and can’t get rid of the Run-time Error 3704: Operation is not allowed when the object is closed.
The code I use:
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
StrSQL = “EXEC stp_Bereken” & Me.Param1.Value & “,” & Me.Param2.Value & “”
rs.CursorLocation = adUseClient
rs.Open StrSQL, cnn, adOpenStatic, adLockOptimistic
With rs
If .RecordCount = 0 Then
MsgBox “NO records”
Else
MsgBox “records”
End If
End With
The output of ?CurrentProject.Connection:
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=MY-PCSQLEXPRESS;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Provider=SQLOLEDB.1
Can you point me in the right direction?
Many thanks
Hi Black,
Thanks for being a blog reader! I don’t recommend binding a recordset in this fashion, rather dump the stored procedure output to a local table prior to loading the form and then bind the controls to the local table, please refer to my blog post on creating local temp tables from SQL Server:
http://accessexperts.net/blog/2012/01/10/create-temp-tables-in-access-from-sql-server-tables/
Kind Regards,
Juan
there is any sample database which showing this methods please?
Unfortunately no
I try to use OpenMyRecordset with transactions but it seems not to work.
con.BeginTrans
sql=”insert into…;SELECT SCOPE_IDENTITY() as ID”
OpenMyRecordset()
But i have to make OpenRecordset() before con.BeginTrans but then ‘INSERT INTO’ is not part of the transaction. ExecuteMyCommand() can’t use the SELECT SCOPE…
Thx
You should be able to use BeginTrans before opening the recordset based on multiple statement SQL. Looking in our code, it seems that this is best done using ADO methods directly. Here’s snippet from one of our code where do a SCOPE_IDENTITY():
Hope that helps.
Can we have transactions within transactions (other proc) with the same con.Begintrans?
Support for nested transactions is a mixed bag. For example, SQL Server ‘nominally’ supports nested transaction but in actuality, it’s not that simple (e.g. a rollback sets @@TRANCOUNT back to 0 regardless of how many BEGIN TRANSACTION there were previously. Furthermore, nested transaction is not necessarily available across all possible providers/drivers. In general, it’s probably best to avoid nesting the transaction and more especially spanning it across more than one scope. It’s OK to have a stored procedure that might use named transaction & SAVE/ROLLBACK to allow for partial rollback within its body but you shouldn’t want to design something that spans beyond this single stored procedure, else you would get additional errors if transaction is dropped to the floor.
I hope that helps.
Juan,
I have a 2010 Access DB that connects to a network 2005 SQL server via ODBC. From my primary DB i have created an accde database to be used on multiple computers, all with ODBC setup on them to the network SQL.
On my PC both the accdb and accde work fine. On the other computers I do not have full Access installed just the runtime with service pack 1.
On these other computers the 2 queries that are on a form just open up blank. I suspect that you can’t just copy an accde to another computer, set up the ODBC links via admin tools and expect it to work. I’m thinking that the links probably need to be relinked.
Is there a way to do that in an accde?
Ed
Ed,
It seems you may have a security issue on your hands. Make sure users have rights to the tables in question.
Regards
Juan
Juan,
I love what yuou are trying to accomplish however my company (the borg of IT companies) blocks google docs. This means that anything you write which tells me to read some function from google docs becomes black box with missing internals. I was sent to this page from http://accessexperts.net/blog/2012/04/04/part-2-linking-tables-using-a-sql-server-table/. The function OpenMyRecordset is not available to me and therefore the fireworks promised in RelinkAllTablesADOX Procedure never goes off.
Sigh.
It’s always something.
jwc
Hello Juan thanks for your reply to my last request, I have successfully managed to open recordsets and attach them to my forms using your code! Now i am trying to use your ExectuteMyCommand to update a local table with data from a sql server, but am not succeeding.
David,
You can’t do heterogeneous queries (mix local tables with remote tables) when using ADO/SQL Server. Access makes it very easy to do heterogeneous queries so you might want to just use an Access query for this case. If the amount of data is large and running an Access query takes too long, consider alternatives such as marshaling the content of the table as XML document to the SQL Server.
Hi Juan – I am tring to use your code where you call the function “OpenMyRecordset”. I have already defined my connection string as a constant:
Public Const c_strODBCConnection As String = “ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=SQD-FTCL1;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=BDBDiagnosticSamples;”
(I use this constant earlier in creating a pass through query which worked fine:
Call CreatePassThrough(m_SQL, strSbFormSourceQry, c_strODBCConnection, False) ) but now when I use it in my version of your function shown below:
Public Function OpenMyRecordset(rs As ADODB.Recordset, strSQL As String, Optional rrCursor As rrCursorType, Optional rrLock As rrLockType, Optional bolClientSide As Boolean) As ADODB.Recordset
Dim con As New ADODB.Connection
If con.state = adStateClosed Then
con.ConnectionString = c_strODBCConnection
con.Open
End If
the code stops at the con.open line with an error message of:
run-time error’-2147467259 (80004005)’:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
I am at a loss of what to do – can you suggest anything?
I am using Windows 7 Enterprise SP1 and am working in an new accdb in which I am trying to re-create an adp so that we use ODBC in preparation for adp’s no longer working. I have created a dsn which I used to link all my SQL server tables with.
thanks
Your article refering the ReadGV/WriteGV/etc… is, frankly, worthless to many of us where the IT-Departments block all such offsite applications and storage.
Worse yet, Google being a bit backwards, has discontinued their applicaitons for us non-business folks making access to this information just that much harder.
-z
z,
Do you realize that the ReadGV/WriteGV discussed in another article are actually using local table in the front-end? Thus the comment about unable to use those functions, which is for different article is not really relevant here. This would still work for an Access application with a local backend hosted within the LAN.
In fact, OpenMyRecordset and ExecuteMyCommand would still work for a local backend, too without needing it to be offsite.
I’m sorry to hear about your frustrations with Google and IT department. I’m sure that you will be able to find a solution if you look for one.