Solved

Sage

Posted on 2000-04-18
5
214 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

685 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