Solved

DAO to ADO and Back

Posted on 2007-11-26
10
314 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

21 Experts available now in Live!

Get 1:1 Help Now