• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

DAO to ADO and Back

Hello,

I have a Visual Basic 6.0 app that I've been using over Access for years, and am now migrating over to SQL Server.  

I had to use ADO to connect to SQL Server (as far as I know), but the rest of the code still uses DAO.  My old way of connecting to Access was like this:

Set gdbKnowledgeTracker = OpenDatabase(App.Path + "\Data\KnowledgeTracker.mdb")

gdbKnowledgeTracker is the global database I refer to in the entire app.  Obviously, what I need to do is hook this thing into SQL Server.  Here's my SQL Server connection code:

' Open with SQL Server
Dim thisConnection As ADODB.Connection
Set thisConnection = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

thisConnection.Open "Driver={SQL Server};Server=COPS14\COPSDEV;Database=KnowledgeTracker;Trusted_Connection=Yes;"


What do I need to add so that I can still use my gdbKnowledgeTracker object?
0
NigelRocks
Asked:
NigelRocks
  • 3
  • 3
  • 2
  • +1
6 Solutions
 
DxpertCommented:
Ok, I don't really think this is possible. Once you open a connection using ADO, you can't have DAO code using it...
0
 
DxpertCommented:
So if you want to have a centralized way of managing your connection, and getting disconnected recorsets you could have something like this:
Option Explicit
 
Private moCon As ADODB.Connection
 
Private Function fbOpenConnection() As Boolean
   fbOpenConnection = False
   
   'create connection object
   If moCon Is Nothing Then
      Set moCon = New ADODB.Connection
      moCon.ConnectionString = msConStr
   End If
 
   'open connection
   If moCon.State = ObjectStateEnum.adStateClosed Then
      moCon.Mode = meConMode
      moCon.CursorLocation = CursorLocationEnum.adUseClient
      moCon.Open
   End If
      
   fbOpenConnection = (moCon.State = ObjectStateEnum.adStateOpen)
   
End Function
 
Private Sub CloseConnection()
   If Not moCon Is Nothing Then
      If moCon.State <> ObjectStateEnum.adStateClosed Then
         moCon.Close
      End If
   End If
End Sub
 
Public Function foSqlExec(ByVal sSql As String, _
                          Optional ByVal bReadOnly As Boolean = True) As ADODB.Recordset
                          
   Dim oRs As ADODB.Recordset
 
   If fbOpenConnection() Then
   
      Set oRs = New ADODB.Recordset
      
      'set lock type
      If bReadOnly Then
         oRs.LockType = LockTypeEnum.adLockReadOnly
      Else
         oRs.LockType = LockTypeEnum.adLockBatchOptimistic
      End If
      
      'connect and open the record set
      oRs.ActiveConnection = moCon
      oRs.CursorType = CursorTypeEnum.adOpenStatic
      oRs.CursorLocation = CursorLocationEnum.adUseClient
      oRs.Open sSql, , , , CommandTypeEnum.adCmdText
      
      'disconnect record set and close connection
      Set oRs.ActiveConnection = Nothing
      CloseConnection
      
      Set foSqlExec = oRs
      Set oRs = Nothing
 
   End If
End Function

Open in new window

0
 
3_SCommented:
It is kind of possible

change thisConnection with gdbKnowledgeTracker and then you can use
gdbKnowledgeTracker.execute "delete blabla"
Now you can update delete and insert, but if you need to select data you must use a recordset.

You should show an example of you dao code we need to replace with a minimum of changing.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
NigelRocksAuthor Commented:
>>>>>>>>change thisConnection with gdbKnowledgeTracker and then you can use
gdbKnowledgeTracker.execute "delete blabla"


Not following you.

gdbKnowledgeTracker does not have a connection object.  That being the case, there's no way that you mean the Dim and Set statements, correct?

Do you mean do somthing like this:?

gdbKnowledgeTracker.Open = "Provider=sqloledb;Network Library=DNETLIB;datasource=SQLSERVER,1433;Initial catalog=KnowledgeTracker;Trusted Connection=Yes;Encrypt=YES"


That gives me an error also.  Can you elaborate?

0
 
andrewneelyCommented:
Here is a cheat.  Create an Access DB that links to the sql server.  use DAO to access the linked access table.  I've sucessfully done this to allow use of our SQL server without refactoring thousands of lines of code (which still needs to be done, but I haven't the time today to do so.)

The change from DAO to ADO isn't that bad (as say from ADO to ADO.NET), so give refactoring the code strong consideration.
0
 
DxpertCommented:
If you going that route, you should also read this:

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn2.microsoft.com/en-us/library/bb188204.aspx  (there's some stuff related to DAO towards the middle of the page)

Remember though, that there are significant performance loss using DAO/Access Link/SQL versus pure ADO/SQL.
0
 
andrewneelyCommented:
Not to mention that it makes it more prone to fail (the more steps, the more points of possible faliure), but it does work.
0
 
3_SCommented:
yes I mean putting
' Open with SQL Server
Dim gdbKnowledgeTracker As ADODB.Connection
Set gdbKnowledgeTracker = New ADODB.Connection
gdbKnowledgeTracker.Open "Driver={SQL Server};Server=COPS14\COPSDEV;Database=KnowledgeTracker;Trusted_Connection=Yes;"

I you used .execute with dao then you can easily adapt your code, but you would make it al lot easier for us if you could give an example of the DAO code you have to convert. (for example how do you do an select, insert..?)
0
 
NigelRocksAuthor Commented:
OK, here's some DAO code:

1)  The first method is just to save an entity; you'll see the INSERT in there.

2)  The second one is a SELECT that will populate a combo box.


I'll work on your above example to integrat ADO and RDO.  If I only have to re-write SELECT statements, I'll probably create a routine that does all the ADO stuff in one place.
Private Sub cmdSave_Click()    
    
    If txtTechTypeName.Text = "" Then
        MsgBox "Enter a technology type name."
        Exit Sub
    ElseIf txtTechTypeDesc.Text = "" Then
        MsgBox "Enter a technology type description."
        Exit Sub
    End If
    
    gsSQL = "INSERT INTO Technology_Types "
    gsSQL = gsSQL + "(Technology_Type_Name,Technology_Description) "
    gsSQL = gsSQL + "VALUES(" + sSQLString(txtTechTypeName.Text) + "," + sSQLString(txtTechTypeDesc.Text) + ")"
    
    gdbKnowledgeTracker.Execute gsSQL
    
    If Me.Tag = "frmAddTechnologyType" Then
        frmAddTechnologyType.lstTechnologyTypes.AddItem (txtTechTypeName.Text)
        frmAddTechnologyType.lstTechnologyTypes.Refresh
        Me.Tag = ""
    End If
    
   If Me.Tag = "frmWorkHistoryTaskNew" Then
        frmWorkHistoryTaskNew.lstTechType.AddItem txtTechTypeName.Text
        frmWorkHistoryTaskNew.lstTechType.Refresh
        Me.Tag = ""
    End If
        
    MsgBox "Record Saved"
    
    ' clear fields
    txtTechTypeName.Text = ""
    txtTechTypeDesc.Text = ""
    
    If Me.Tag = "frmNewTask" Then
        frmWorkHistoryTaskNew.lstTechType.Refresh
    End If
    
    Set mdbNewTechType = Nothing
 
End Sub
 
 
 
Public Sub PopulateComboBox(ByVal rsTable As Recordset, sItem As String, cboBox As ComboBox)
' The purpose of this subroutine is to populate a given combo box
' with the contents of the first column of a given recordset
    On Error GoTo FillBoxError
    
    If rsTable.RecordCount = 0 Then Exit Sub
    
    cboBox.Clear
    rsTable.MoveFirst
    
    Do While rsTable.EOF = False
        cboBox.AddItem rsTable(sItem)
        rsTable.MoveNext
    Loop
 
    cboBox.Refresh
    
    Exit Sub
FillBoxError:
    Call DisplayErrorMessage("PopulateComboBox", Err)
End Sub

Open in new window

0
 
andrewneelyCommented:
Here is your code, with ADO code inserted.  Note:  You have to go in and put a reference in for ADO for this to work.

dim ADOCommand as new adodb.command
dim ADOConnection as new adodb.connection

private sub Form1_load()
      ADOConnection.open("Driver={SQLServer};Server=SQLServerMachineName;Database=InitialDatabas;Trusted_Connection=Yes;")
      dim myRecordset as new ADODB.Recordset
      'open a ado db recordset pulling a field from a table based on a condition
      myRecordset.open "select field1 from table1 where coniditon1 = true",ADOConnection,adLockOptimistic

end sub

Private Sub cmdSave_Click()    
   
    If txtTechTypeName.Text = "" Then
        MsgBox "Enter a technology type name."
        Exit Sub
    ElseIf txtTechTypeDesc.Text = "" Then
        MsgBox "Enter a technology type description."
        Exit Sub
    End If
   
    gsSQL = "INSERT INTO Technology_Types "
    gsSQL = gsSQL + "(Technology_Type_Name,Technology_Description) "
    gsSQL = gsSQL + "VALUES(" + sSQLString(txtTechTypeName.Text) + "," + sSQLString(txtTechTypeDesc.Text) + ")"
   
' Old ADO
'    gdbKnowledgeTracker.Execute gsSQL

'ADO Code
   ADOCommand.activeconnection=adoconnection
   ADOCommand.commandText=gsSQL
   ADOCommand.Execute

    If Me.Tag = "frmAddTechnologyType" Then
        frmAddTechnologyType.lstTechnologyTypes.AddItem (txtTechTypeName.Text)
        frmAddTechnologyType.lstTechnologyTypes.Refresh
        Me.Tag = ""
    End If
   
   If Me.Tag = "frmWorkHistoryTaskNew" Then
        frmWorkHistoryTaskNew.lstTechType.AddItem txtTechTypeName.Text
        frmWorkHistoryTaskNew.lstTechType.Refresh
        Me.Tag = ""
    End If
       
    MsgBox "Record Saved"
   
    ' clear fields
    txtTechTypeName.Text = ""
    txtTechTypeDesc.Text = ""
   
    If Me.Tag = "frmNewTask" Then
        frmWorkHistoryTaskNew.lstTechType.Refresh
    End If
   
    Set mdbNewTechType = Nothing
 
End Sub
 
 
 'Change Recordset to ADODB.Recordset.  Otherwise, should work as written.
Public Sub PopulateComboBox(ByVal rsTable As ADODB.Recordset, sItem As String, cboBox As ComboBox)
' The purpose of this subroutine is to populate a given combo box
' with the contents of the first column of a given recordset
    On Error GoTo FillBoxError
    'Note:  Sometimes the recordset is not
    'populated.  I'd do a "IF RS.EOF and RS.BOF then exit Sub"
    If rsTable.RecordCount = 0 Then Exit Sub
   
    cboBox.Clear
    rsTable.MoveFirst
   
    Do While rsTable.EOF = False
        cboBox.AddItem rsTable(sItem)
        rsTable.MoveNext
    Loop
 
    cboBox.Refresh
   
    Exit Sub
FillBoxError:
    Call DisplayErrorMessage("PopulateComboBox", Err)
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now