Using Class Module to connect VBA to SQL

Posted on 2008-10-29
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
  • 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 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 33
DSN-LESS connection to MS Access database 6 28
T-SQL: Do I need CLUSTERED here? 13 38
SQL multicriteria from ONE textbox 32 41
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now