Solved

DAO to ADO and Back

Posted on 2007-11-26
10
331 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

840 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