Solved

Using Class Module to connect VBA to SQL

Posted on 2008-10-29
8
3,735 Views
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?
'---------
'Constants
'---------
DBCONNSTRING = "Provider=SQLOLEDB.1;UID=imexfx;Password=********;Initial Catalog=dyna***********;Data Source=123.123.123.123,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")
   m_objConn.Open(DBCONNSTRING)
  End If
  
  If Err.Number <> 0 Then
   Response.Write(DATAERRORSTR)
   Response.End()
  End If
  
 End Sub
 
 Private Sub Class_Terminate()
  If isObject(m_objConn) Then
   m_objConn.Close
   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=123.123.123.123"
 
   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"
   Else
    Debug.Print "Failed"
   End If
   
   
Exit_Sub:
    Exit Sub
    
ErrHandle:
    msg = "Problem Testing connection" & vbCrLf & Err.Description
    MsgBox msg, vbExclamation, "Form Main Menu"
End Sub

Open in new window

0
Comment
Question by:Nigel Keith-Walker
[X]
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
8 Comments
 
LVL 9

Expert Comment

by:borki
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




0
 

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.
0
 
LVL 85

Accepted Solution

by:
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
Else
  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.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

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

0
 

Author Comment

by:Nigel Keith-Walker
ID: 22830016
Borki

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.  
0
 

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
  gcon.open  
Else
  If gCON.State = adStateClosed Then
    gCon.Open DBCONNSTRING
  End If
End If
 
'/now test
DBConnect = gCON.State = adStateOpen
 
End Function

Open in new window

0
 

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.  
0
 

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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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