Solved

Using Class Module to connect VBA to SQL

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

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

9 Experts available now in Live!

Get 1:1 Help Now