ADO to DAO

I

IanT

Registered User.
Local time
Today, 17:06
Joined
Nov 30, 2001
Messages
191
Hi

I am trying to convert some code I have found from ADO to DAO. Can anyone advise how this code should be converted!

rst.Open strSelect, CurrentProject.Connection, adOpenDynamic
 
Where's the rest of the code first? Let's see what you have? And why do you want to convert to DAO?
 
The datbase I am currently using has code that uses DAO.


Code: 
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rngWord As Word.Range
Dim rst As DAO.Recordset
Dim strSelect As String
Dim intMonth As Integer
Dim intCount As Integer

'check for null controls
'If IsNull(Me.cboCustomer) Or IsNull(Me.lstYear) Then
'MsgBox "You must pick the customer " _
    '& "and year you want to print.", vbOKOnly
'GoTo ExitProfile
'End If

On Error Resume Next
'set appWord to current Word instance or create new one
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
End If
On Error GoTo ErrorHandler

'create new document based on template and clear default text
appWord.Visible = True
appWord.ScreenUpdating = False
Set doc = appWord.Documents.Add

'Set document title
Set rngWord = doc.Range(0)
With rngWord
.text = Me.cboCustomer.Value 'Column(0) '_
    '& " " & Me.lstYear & " Customer Profile"
.Style = doc.Styles("Title")
.InsertParagraphAfter
End With

'insert blurb
Set rst = CurrentDb.OpenRecordset("SELECT SuppierInfo FROM tblSupplierInfo")

intCount = doc.Paragraphs.Count
Set rngWord = doc.Paragraphs(intCount).Range

With rngWord
.Style = doc.Styles("Normal")
.InsertAfter rst.Fields("ProfileHeading")
.InsertParagraphAfter
rst.Close
'replace placeholders
    With .Find
        .MatchWholeWord = True
        .text = "[CustomerName]"
        .Replacement.text = Me.cboCustomer.Value
        .Replacement.Font.Italic = True
        .Execute Replace:=wdReplaceAll
        .text = "[Year]"
        '.Replacement.text = Me.lstYear
        .Replacement.Font.Italic = False
        .Execute Replace:=wdReplaceAll
    End With
End With

'retrieve sales
For intMonth = 1 To 12
    intCount = doc.Paragraphs.Count
    Set rngWord = doc.Paragraphs(intCount).Range
   
      'add month heading in Word
    With rngWord
        .InsertParagraphAfter
        .Move wdParagraph
        .Style = doc.Styles("Heading 2")
        .InsertAfter Format(intMonth & "/01/2000", "MMMM")
        .InsertParagraphAfter
    End With
        
    strSelect = "SELECT Client_Name FROM qrySummary" '_

    Set rst = CurrentDb.OpenRecordset("strSelect", dbOpenDynaset)
    'Set rs = db.OpenRecordset("TableName", dbOpenDynaset)
       
    'add product sales info
    If rst.EOF Then
        With rngWord
            .Move wdParagraph
            .Style = doc.Styles("Caption")
            .InsertAfter "No Sales To Customer During This Month"
            .InsertParagraphAfter
        End With
    Else
        With rngWord
            .Move wdParagraph
            .Style = doc.Styles("Normal")
            .InsertAfter "Supplier" & vbTab & "InvoicePrice"
            .Font.Bold = True
            .InsertParagraphAfter
        End With
        Do While Not rst.EOF
            With rngWord
                .Move wdParagraph
                .Style = doc.Styles("Normal")
                .InsertAfter rst.Fields("Supplier") _
                    & vbTab & vbTab & rst.Fields("InvoicePrice")
                .InsertParagraphAfter
                rst.MoveNext
            End With
        Loop
    End If
    rst.Close
Next intMonth

MsgBox "Profile Complete", vbOKOnly
appWord.ScreenUpdating = True

ExitProfile:
Set rst = Nothing
Set rngWord = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

ErrorHandler:
If Err = 5941 Then
    Resume Next
Else
    MsgBox Err & Err.Description
    Resume ExitProfile
End If
 
Last edited by a moderator:
So, you really mean you want to convert to ADO?
 
Hi

Here is the original code where I have not tried to convert to DAO.

Code: 
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rngWord As Word.Range
Dim rst As New ADODB.Recordset
Dim strSelect As String
Dim intMonth As Integer
Dim intCount As Integer

'check for null controls
If IsNull(Me.cboCustomer) Or IsNull(Me.lstYear) Then
MsgBox "You must pick the customer " _
    & "and year you want to print.", vbOKOnly
GoTo ExitProfile
End If

On Error Resume Next
'set appWord to current Word instance or create new one
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
End If
On Error GoTo ErrorHandler

'create new document based on template and clear default text
appWord.Visible = True
appWord.ScreenUpdating = False
Set doc = appWord.Documents.Add

'Set document title
Set rngWord = doc.Range(0)
With rngWord
.Text = Me.cboCustomer.Column(1) _
    & " " & Me.lstYear & " Customer Profile"
.Style = doc.Styles("Title")
.InsertParagraphAfter
End With

'insert blurb
rst.Open "SELECT ProfileHeading FROM tblHeading", _
    CurrentProject.Connection, adOpenStatic

intCount = doc.Paragraphs.Count
Set rngWord = doc.Paragraphs(intCount).Range

With rngWord
.Style = doc.Styles("Normal")
.InsertAfter rst.Fields("ProfileHeading")
.InsertParagraphAfter
rst.Close
'replace placeholders
    With .Find
        .MatchWholeWord = True
        .Text = "[CustomerName]"
        .Replacement.Text = Me.cboCustomer.Column(1)
        .Replacement.Font.Italic = True
        .Execute Replace:=wdReplaceAll
        .Text = "[Year]"
        .Replacement.Text = Me.lstYear
        .Replacement.Font.Italic = False
        .Execute Replace:=wdReplaceAll
    End With
End With

'retrieve sales
For intMonth = 1 To 12
    intCount = doc.Paragraphs.Count
    Set rngWord = doc.Paragraphs(intCount).Range
   
      'add month heading in Word
    With rngWord
        .InsertParagraphAfter
        .Move wdParagraph
        .Style = doc.Styles("Heading 2")
        .InsertAfter Format(intMonth & "/01/2000", "MMMM")
        .InsertParagraphAfter
    End With
        
    strSelect = "SELECT ProductName, SumofQuantity FROM qryUnitsSold " _
        & "WHERE CustomerID = """ & Me.cboCustomer & """ AND " _
        & "SalesMonth = " & intMonth _
        & " AND SalesYear = " & Me.lstYear
    rst.Open strSelect, CurrentProject.Connection, adOpenDynamic
    
    'add product sales info
    If rst.EOF Then
        With rngWord
            .Move wdParagraph
            .Style = doc.Styles("Caption")
            .InsertAfter "No Sales To Customer During This Month"
            .InsertParagraphAfter
        End With
    Else
        With rngWord
            .Move wdParagraph
            .Style = doc.Styles("Normal")
            .InsertAfter "Units Sold" & vbTab & "Product Name"
            .Font.Bold = True
            .InsertParagraphAfter
        End With
        Do While Not rst.EOF
            With rngWord
                .Move wdParagraph
                .Style = doc.Styles("Normal")
                .InsertAfter rst.Fields("SumOfQuantity") _
                    & vbTab & vbTab & rst.Fields("ProductName")
                .InsertParagraphAfter
                rst.MoveNext
            End With
        Loop
    End If
    rst.Close
Next intMonth

MsgBox "Profile Complete", vbOKOnly
appWord.ScreenUpdating = True

ExitProfile:
Set rst = Nothing
Set rngWord = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

ErrorHandler:
If Err = 5941 Then
    Resume Next
Else
    MsgBox Err & Err.Description
    Resume ExitProfile
End If
 
Last edited by a moderator:
Please use the appropriate tags when posting code to the forum. It makes it easier to read.

Also, why not just set a refernce to ADO?
 
I understood you can only have referenced either ADO or DOA not both!
 
AS long as you are explicit you can have a reference to both.

i.e.

Code: 
Dim rs1 As DAO.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As Recordset

rs1 will be DAO, rs2 will be ADO, and rs3 will be whichever of the two references (DAO and ADO) is higher in the list of references.
 
SJ is correct, which is why Access let's you reference both libraries at the same time because you can use both. Some people don't declare their variables correctly such as:

Dim rst As Recordset ... so Access looks to either the default library (ADO or DAO depending on which version of Access you have)... or if both are selected it chooses the first on the list.

So better woud be :
Dim rst As DAO.Recordset
or
Dim rst As ADODB.Recordset



As for your open string, what are you trying to do? ADO and DAO can both do similar (if not the same things) but sometimes one way has an easier solution.
 
You must log in or register to reply here.

Similar threads

J
Can ADO be used for BEGINTRANS COMMIT TRANS
Replies
10
Views
1,231
cheekybuddha
cheekybuddha
W
DAO to ADO
Replies
6
Views
1,147
CJ_London
C
MattBaldry
Solved Multi-Select List Box, Distinct Values
Replies
17
Views
1,209
Ken Sheridan
Ken Sheridan
D
Updating ADP - Which ADODB connection to use
Replies
2
Views
1,042
Diarmuid
D
D
Convert acrobat 9.0 PDF to Image .PNG Using VBA Code
2
Replies
22
Views
1,585
D_Walla
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom