Solved

Sage

Posted on 2000-04-18
5
212 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
ID: 2728358
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
ID: 2728371
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
ID: 2728375
Thanks.  Please submit an answer and I will give you your points.
0
 
LVL 2

Accepted Solution

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

Author Comment

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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

821 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