Solved

DAO to ADO and Back

Posted on 2007-11-26
10
341 Views
Last Modified: 2008-02-01
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
Comment
Question by:NigelRocks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:Dxpert
ID: 20353393
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
 
LVL 10

Accepted Solution

by:
Dxpert earned 150 total points
ID: 20353462
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
 
LVL 10

Assisted Solution

by:3_S
3_S earned 200 total points
ID: 20353933
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
Technology Partners: 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!

 
LVL 1

Author Comment

by:NigelRocks
ID: 20355413
>>>>>>>>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
 
LVL 2

Assisted Solution

by:andrewneely
andrewneely earned 150 total points
ID: 20359314
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
 
LVL 10

Assisted Solution

by:Dxpert
Dxpert earned 150 total points
ID: 20359418
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
 
LVL 2

Expert Comment

by:andrewneely
ID: 20359486
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
 
LVL 10

Assisted Solution

by:3_S
3_S earned 200 total points
ID: 20359967
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
 
LVL 1

Author Comment

by:NigelRocks
ID: 20362197
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
 
LVL 2

Assisted Solution

by:andrewneely
andrewneely earned 150 total points
ID: 20365418
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

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 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…

688 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