Solved

Sage

Posted on 2000-04-18
5
210 Views
Last Modified: 2010-05-02
Can you send me a copy of you code module you used for ADO/DAO.  I am interested in looking at it.
0
Comment
Question by:tleveron
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:Sage020999
Comment Utility
I am pasteing the code here so that others can use the information as well.  I am currently doing some testing to see if the ADO 2.5 will resolve the ADO problems I was having with preformace.  I hope this satisfies your request.  - Sage





Option Explicit

Public vntDBMain As Variant
Public vntRSMain As Variant
Public DBOM As Integer

Public Const MSA351 = "Microsoft.Jet.OLEDB.3.51"
Public Const MSA360 = "Microsoft.Jet.OLEDB.4.0"
Public Const PSQL = "ERMS32"

'* Cursor Types
Public csOpenForwardOnly As Integer
Public csOpenDynamic As Integer
Public csOpenStatic As Integer



Enum DBType
  ADO = 1
  dao = 2
End Enum






Public Function OpenConnection(ByVal Source As String, ByRef vntDB As Variant) As Boolean

  Dim dbMain As dao.Database
  Dim adoMain As ADODB.Connection

 
  Select Case DBOM
    Case DBType.dao
      csOpenForwardOnly = 8
      csOpenDynamic = 2
      csOpenStatic = 4
    Case DBType.ADO
      csOpenForwardOnly = adOpenForwardOnly
      csOpenDynamic = adOpenDynamic
      csOpenStatic = adOpenStatic
  End Select



  If DBOM = DBType.ADO Then
    Set adoMain = New ADODB.Connection
    adoMain.Provider = m_strProvider
    adoMain.ConnectionString = Source
    m_strConnectionString = "Data Source = " & Source
    m_strDatConnection = "Provider = " & m_strProvider & ";" & m_strConnectionString & ";"
    adoMain.CursorLocation = adUseServer
    adoMain.Open
    Set vntDB = adoMain
    DBOM = DBType.ADO
  Else
    Set dbMain = OpenDatabase(Source, False, False)
    Set vntDB = dbMain
    DBOM = DBType.dao
  End If

  OpenConnection = True

End Function

Public Function OpenRecordset(ByVal strSQL As String, ByVal vntDB As Variant, _
                         ByRef vntRS As Variant, Optional dbCursorType As Integer, _
                         Optional dbLockType As Integer) As Boolean
 
 
  Dim rstMain As dao.Recordset
  Dim radoMain As ADODB.Recordset
 
 
  If DBOM = DBType.ADO Then
    Set radoMain = New ADODB.Recordset
    Set vntRS = radoMain
    vntRS.Open strSQL, vntDB, dbCursorType, IIf(dbCursorType = 0, adLockReadOnly, adLockPessimistic)
    'vntRS.Open strSql, vntDB ', dbCursorType
  Else
    Set vntRS = rstMain
    Set vntRS = vntDB.OpenRecordset(strSQL, dbCursorType)
  End If

  If Not vntRS.EOF Then
    OpenRecordset = True
  End If

End Function





Public Function Edit(vntRST As Variant) As Boolean

  If DBOM = DBType.ADO Then
    'Do nothing, there is not a edit method for ADO
  Else
    vntRST.Edit
  End If
 
End Function

Public Function State(RST As Variant) As Integer

  If DBOM = DBType.ADO Then
    State = RST.State
  Else
    State = 1
  End If
 

End Function

Public Sub Find(RST As Variant, strSQL As String)

  If DBOM = DBType.ADO Then
    RST.Find strSQL
  Else
    RST.FindFirst strSQL
  End If

End Sub



Public Function isNothing(rstRecordset As Variant) As Boolean

  On Error GoTo Bailout
  If rstRecordset = Empty Then
    isNothing = True
  End If
Bailout:
End Function

Public Sub CommitTrans(Connection As Variant)
  If DBOM = DBType.ADO Then
    Connection.CommitTrans
  Else
    DBEngine.CommitTrans
  End If
End Sub

Public Sub BeginTrans(Connection As Variant)
  If DBOM = DBType.ADO Then
    Connection.BeginTrans
  Else
    DBEngine.BeginTrans
  End If
End Sub

Public Sub RollBack(Connection As Variant)

  If DBOM = DBType.ADO Then

    Connection.RollbackTrans
  Else
    DBEngine.RollBack
  End If

End Sub

Public Function LikeChar() As String
  If CS.DBOM = DBType.ADO Then
    LikeChar = "%"
  Else
    LikeChar = "*"
  End If
End Function
0
 
LVL 2

Expert Comment

by:Sage020999
Comment Utility
I sent you the old one.  Please disregard.  It works but the proformace is awful.


Option Explicit

#If CSADO Then
  Public gConn As adodb.Connection
#Else
  Public gConn As dao.Database
#End If

Public Const MSA351 = "Microsoft.Jet.OLEDB.3.51"
Public Const MSA360 = "Microsoft.Jet.OLEDB.4.0"
Public Const PSQL = "ERMS32"

'* Cursor Types
Public Enum csCursorTypes
  #If CSADO Then
    csOpenForwardOnly = adOpenForwardOnly
    csOpenDynamic = adOpenDynamic
    csOpenStatic = adOpenStatic
  #Else
    csOpenForwardOnly = dbOpenForwardOnly
    csOpenDynamic = dbOpenDynaset
    csOpenStatic = dbOpenSnapshot
  #End If
End Enum







Public Function OpenConnection(Optional ConnectionString As String, Optional UserID As String, _
                Optional Password As String, Optional Options As Long) As Boolean

  On Error GoTo errOpen
 
  #If CSADO Then
    Set gConn = New adodb.Connection
    gConn.Provider = m_strProvider
    gConn.ConnectionString = ConnectionString
    m_strConnectionString = "Data Source = " & ConnectionString
    m_strDatConnection = "Provider = " & m_strProvider & ";" & m_strConnectionString & ";"
    gConn.CursorLocation = adUseServer
    gConn.Open
  #Else
    Set gConn = OpenDatabase(ConnectionString, False, Options)
  #End If
 
  OpenConnection = True
  GoTo Bailout
errOpen:
  OpenConnection = False
  MsgBox "Connection failed.", vbInformation, "Error"
Bailout:

End Function























Public Function LikeChar() As String

    LikeChar = "%"

End Function

#If CSADO Then
  Public Function OpenRecordset(ByRef rstset As adodb.Recordset, Optional Source As String, Optional ActiveConn As adodb.Connection, _
                              Optional CursorType As csCursorTypes = adOpenUnspecified, Optional LockType As LockTypeEnum = adLockUnspecified, _
                              Optional Options As Long = -1) As Boolean

#Else
  Public Function OpenRecordset(ByRef rstset As dao.Recordset, Optional Source As String, Optional ActiveConn As dao.Database, _
                              Optional CursorType As csCursorTypes = adOpenUnspecified, Optional LockType As LockTypeEnum = adLockUnspecified, _
                              Optional Options As Long = -1) As Boolean
#End If


  '* In order for this function to work with DAO, you must change the ADODB. to DAO.
  '  Connection will be changed to Database
  On Error GoTo errOpen
  #If CSADO Then
    Set rstset = New adodb.Recordset
    rstset.Open Source, gConn, CursorType, LockType, Options
  #Else
    Set rstset = gConn.OpenRecordset(Source, CursorType)
   
  #End If
 
  OpenRecordset = True

 
Bailout:
  Exit Function
errOpen:
  OpenRecordset = False
  MsgBox "Could not open recordset: " & Err & " - " & Error, vbInformation, "Error"
  GoTo Bailout
  Resume

End Function
0
 

Author Comment

by:tleveron
Comment Utility
Thanks.  Please submit an answer and I will give you your points.
0
 
LVL 2

Accepted Solution

by:
Sage020999 earned 200 total points
Comment Utility
If you have any more questions you can reach me at Sbray@Cajunnet.com
0
 

Author Comment

by:tleveron
Comment Utility
Thanks.  Please submit an answer and I will give you your points.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 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

13 Experts available now in Live!

Get 1:1 Help Now