Solved

DAO to ADO and Back

Posted on 2007-11-26
10
324 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

810 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