Using Class Module to connect VBA to SQL

Posted on 2008-10-29
Medium Priority
Last Modified: 2013-11-25
An Access 2003 front end connects to a SQL server through a LAN.  The system was developed over four years.  It commenced as a Jet dbase and progressed to connecting to a SQL 2000 dbase over a LAN.  The coding is in VBA and has 80 occurrences in the coding to connect to the SQL server.

The database is migrating to a hosted server.  Some users will have a new web based access to a limited part of the system.  However we need to retain the functionality of the backend office to the system.

It is time to clean up the connection to the database.  It has been suggested that it would be a good opportunity to use a class module.  The new object can just be referenced.  

This is a learning curve.  I have included two snippets of code:
1 the class module to initialise, terminate, Execute and create client cursor
2 the current calling procedure

I would appreciate suggestions where the constants should be placed and how to set the object.  What happens when the connection drops out?  How can I check for a connection?
DBCONNSTRING = "Provider=SQLOLEDB.1;UID=imexfx;Password=********;Initial Catalog=dyna***********;Data Source=,1433"
DATAERRORSTR = "Database Not Available"
'---------- Data Class ----------'
Class DataConn
 Private m_objConn
 Private Sub Class_Initialize()
  On Error Resume Next
  If Not isObject(m_objConn) Then
   Set m_objConn = Server.CreateObject("ADODB.Connection")
  End If
  If Err.Number <> 0 Then
  End If
 End Sub
 Private Sub Class_Terminate()
  If isObject(m_objConn) Then
   Set m_objConn = Nothing
  End If
 End Sub
 ' Execute Method
 Public Function Execute(strSQL)   
  Set Execute = m_objConn.Execute(strSQL)
 End Function
 ' Create Static Recordset Method
 Public Function CreateClientCursorRS(strSQL)
  Set m_objRS = Server.CreateObject("ADODB.Recordset")
   m_objRS.CursorLocation = adUseClient
   m_objRS.Open strSQL, m_objConn,, adLockReadOnly, adCmdText
  Set CreateClientCursorRS = m_objRS
 End Function
End Class
Private Sub Test_Click3()
  ' test connection
   Dim rst As New ADODB.Recordset
   Dim cnn As New ADODB.Connection
   Dim strSQL As String
   Dim msg As String
   On Error GoTo ErrHandle
   strSQL = "tblCountry"
   strSQL = strSQL & " WHERE Cntry = 'AUS'"
   Set rst = New ADODB.Recordset
   Set cnn = New ADODB.Connection
   cnn.CursorLocation = adUseClient
   cnn.Open "Provider=SQLOLEDB.1;Password=*********;Persist Security Info=True;User ID=imexfx;Initial Catalog=dyn*********;Data Source="
   rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic, adCmdTable
   If rst.RecordCount = 1 Then
    Debug.Print "Success"
    msg = "success connecting to dynamicwebsites" & vbCrLf & Err.Description
    MsgBox msg, vbExclamation, "Form Main Menu"
    Debug.Print "Failed"
   End If
    Exit Sub
    msg = "Problem Testing connection" & vbCrLf & Err.Description
    MsgBox msg, vbExclamation, "Form Main Menu"
End Sub

Open in new window

Question by:Nigel Keith-Walker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6

Expert Comment

ID: 22829538
I am a bit confused by your question. Are you saying that your DB is already working fine with SQL Server on a local LAN and all you are doing is moving the DB server to a WAN location?

If this is the case, all you need to change is the timeout value in the connection string.

How are your forms & reports connecting to the DB? I am sure they don't call/use your VBA class module?

Also, have you looked at Access Data Projects?

Let us know

Felix Burkhard


Author Comment

by:Nigel Keith-Walker
ID: 22829612
Yes the Access front end works fine with the LAN server.

However I am opening the connection to it in each procedure function, and I am not coding a close connection.  I thought that it was time to make the code more effective. Also I do need to set the cursor to the client side.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1600 total points
ID: 22829636
It would seem to me that all you'd need to do would be to create (a) a global ADODB connection object and (b) a DBConnect function in a Standard Module ... I really don't see the need for a class module in this instance. For example:

public gCON as ADODB.Connection

Function DBConnection() As Boolean

If gCON is Nothing then
  Set gCON = New ADODB.Recordset
  If gCON.State = adStateClosed Then
    gCon.Open "Your connect string"
  End If
End If

'/now test
DBConnect = gCON.State = adStateOpen

End Function

From there just change your 80 code references to use the gCON connection object, and call DBConnect before you run anything ... if DBConnect is False, you could inform the reader, try connecting again, log the failure, etc etc.

This is somewhat crude, and you'd need to test it thoroughly and include error handling also. There are aslo several types of gCON.STate to consider - do a little research on this to figure out the best way to handle these.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Nigel Keith-Walker
ID: 22829791
Thanks LSM

Where does the Global ADODB connection need to be set up? Should the coding be placed in the . Microsoft Office Access Class Objects > Form_Main Menu

There VBA folders are:
. Microsoft Office Access Class Objects
. Modules
. Class Modules


Author Comment

by:Nigel Keith-Walker
ID: 22830016

Currently the forms and reports are connecting to the LAN database using code similar to the test_click procedure.

There is insufficient resourcing to rebuild the system as a project.  The system has developed over four years.  

Author Comment

by:Nigel Keith-Walker
ID: 22854069
I have amended the suggested code and added it to Modules.  The Function DBConnection is now called from the VBA behind one of the forms.  From the immediate window the GCON connection is open in the function, but not available when it returns control to the form VBA.
How can the connection GCON be made available to all forms and reports?

public gCON as ADODB.Connection
public Const DBCONNSTRING = "connection string to SQL server"
Function DBConnection() As Boolean
If gCON is Nothing then
  Set gCON = New ADODB.Connection
  gcon.cursorlocation = aduseclient
  If gCON.State = adStateClosed Then
  End If
End If
'/now test
DBConnect = gCON.State = adStateOpen
End Function

Open in new window


Author Comment

by:Nigel Keith-Walker
ID: 22854438
re last posting

Checking the coding there were two declarations of gcon.  Having removed the public declaration in the VBA behind the Access forms and reports has allowed connection.  I will test further.  

Author Closing Comment

by:Nigel Keith-Walker
ID: 31511089
I posted the coding that I actually used.  I have now replaced 80 occurrrences with a single function.  I have completed the user testingf of the package.  Thanks.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question