Solved

Using Class Module to connect VBA to SQL

Posted on 2008-10-29
8
3,545 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
  • 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 84

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

 

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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 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