Re: [MS_AccessPros] Upgrade to new code

 

Thank you for your candor.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On ‎Friday‎, ‎September‎ ‎15‎, ‎2017‎ ‎03‎:‎39‎:‎16‎ ‎PM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Sheesh, Art.  You'd think you had never built a DAO recordset before.  Apply what you should have learned by now.  Or, do you expect us to write all your code for you?

Hint:  Instead of assigning stuff to Adodclvl.Recordsource, assign it to a string variable - named something like strSQL.  Then use strSQL to open a DAO Recordset and navigate through it.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Sep 15, 2017, at 10:34 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thans for looking at it. I have a tone of it. But what would you use instead of a adodc control? This piece check to see if there is any douments in the table that meet the criteria...

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On ‎Thursday‎, ‎September‎ ‎14‎, ‎2017‎ ‎09‎:‎00‎:‎33‎ ‎PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Art,

About 40% of the lines of code are garbage that hasn't been removed. The other lines seem to create an  ADO recordset for some purpose that we don't know and displays the record count in a text box. If this recordset is for a form or report , then just create your Access query and set it as the record source. If it feeds a list box, just use it for a Row Source.

Here are the only pertinent lines of code

Private Function InitializeArraysArcGrp() As Boolean ' set to True if there are some documents returned in the query

On Error Resume Next

InitializeArraysArcGrp = True

Dim i%

AdodcLvl.ConnectionString = connectionStrg

AdodcDoc.ConnectionString = connectionStrg

AdodcLvl.RecordSource = "SELECT [Archive].[ArchiveName], [MainTitle].[MainLevelTitle], " & _

"[eSubFolderTable].[SubLevelOneName], [Archive].[ArchiveKey], " & _

"[MainTitle].[MainTitleKey], [eSubFolderTable].[SubLevelOneKey], [MainTitle].[IndexKey01], " & _

"[MainTitle].[IndexKey02], [MainTitle].[IndexKey03], [MainTitle].[IndexKey04], " & _

"[MainTitle].[IndexKey05], [MainTitle].[IndexKey06], [MainTitle].[IndexKey07], " & _

"[MainTitle].[IndexKey08], [MainTitle].[IndexKey09], [MainTitle].[IndexKey10], " & _

"[Archive].[IndexKey01Label], [Archive].[IndexKey02Label], [Archive].[IndexKey03Label], " & _

"[Archive].[IndexKey04Label], [Archive].[IndexKey05Label], [Archive].[IndexKey06Label], " & _

"[Archive].[IndexKey07Label], [Archive].[IndexKey08Label], [Archive].[IndexKey09Label], " & _

"[Archive].[IndexKey10Label] FROM ((Archive LEFT JOIN MainTitle ON [Archive].[ArchiveKey] " & _

"=[MainTitle].[ArchiveKey]) LEFT JOIN eSubFolderTable ON [Archive].[ArchiveKey] " & _

"=[eSubFolderTable].[eCabinetKey]) WHERE 1=1"

' this methode is not used anymore because it queries all documents, then a loop is used

' to find and print the documents belonging to each eFolder or eSubFolder.  Instead only

' documents specific to a certain eFolder or eSubFolder are directly queried everytime

' that eFolder or that eSubFolder is being printed.  (look at code below <' print documents>

' and <'Query documents>

If allECabinets = False Then

AdodcLvl.RecordSource = AdodcLvl.RecordSource & " and [Archive].[ArchiveKey] = '" & archiveKey & "'"

If mnuOptionsAllEFolders.Checked = False Then

AdodcLvl.RecordSource = AdodcLvl.RecordSource & " and [MainTitle].[MainTitleKey] = '" & mainTitleKey & "'"

End If

End If

' add search criteria to SQL statement if searching by eIndexFields is selected

If checkIndexes.Value = 1 Then

AdodcLvl.RecordSource = AdodcLvl.RecordSource & txtSQLStatement.Text

End If

' sort records and refresh recordSet

AdodcLvl.RecordSource = AdodcLvl.RecordSource & " ORDER BY [Archive].[ArchiveName], [Archive].[archiveKey], " & _

"[MainTitle].[MainLevelTitle], [MainTitle].[mainTitleKey], [eSubFolderTable].[SubLevelOneName]"

AdodcLvl.Refresh

AdodcLvl.Recordset.MoveFirst

AdodcLvl.Recordset.MoveLast

AdodcLvl.Recordset.MoveFirst

Text1.Text = "# Records = " & AdodcLvl.Recordset.RecordCount

i = AdodcLvl.Recordset.RecordCount

If i <> 0 Then

arrLvl = AdodcLvl.Recordset.GetRows(i)

ElseIf i = 0 Then

' set InitializeArraysArcGrp to False to indicate that there is no records in the recordSet

' this value is used to print the report or not

InitializeArraysArcGrp = False

End If

AdodcLvl.Recordset.Close

Regards,

Duane 

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com[MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, September 14, 2017 6:16 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Upgrade to new code

 

We are currently updating a vb6 application into an access database. We were looking at the code and we see they used a lot of ADODBwith ADODC controls: Here is as amole:

Private Function InitializeArraysArcGrp() As Boolean ' set to True if there are some documents returned in the query
    On Error Resume Next
    InitializeArraysArcGrp = True
    Dim i%
    AdodcLvl.ConnectionString = connectionStrg
    AdodcDoc.ConnectionString = connectionStrg
    
'    AdodcLvl.RecordSource = "SELECT 0[Archive].[ArchiveName], 1[MainTitle].[MainLevelTitle], " & _
        "2[eSubFolderTable].[SubLevelOneName], 3[SubLevelTwo].[SubLevelTwoName], " & _
        "4[SubLevelThree].[SubLevelThreeName], 5[SubLevelFour].[SubLevelFourName], " & _
        "6[SubLevelFive].[SubLevelFiveName], 7[SubLevelSix].[SubLevelSixName], " & _
        "8[Archive].[ArchiveGroupKey], 9[Archive].[ArchiveKey], 10[MainTitle].[MainTitleKey], " & _
        "11[eSubFolderTable].[SubLevelOneKey], 12[SubLevelTwo].[SubLevelTwoKey], 13[SubLevelThree].[SubLevelThreeKey], " & _
        "14[SubLevelFour].[SubLevelFourKey], 15[SubLevelFive].[SubLevelFiveKey], 16[SubLevelSix].[SubLevelSixKey], " & _
        "17[MainTitle].[IndexKey01], 18[MainTitle].[IndexKey02], 19[MainTitle].[IndexKey03], " & _
        "20[MainTitle].[IndexKey04], 21[MainTitle].[IndexKey05], 22[MainTitle].[IndexKey06], " & _
        "23[MainTitle].[IndexKey07], 24[MainTitle].[IndexKey08], 25[MainTitle].[IndexKey09], " & _
        "26[MainTitle].[IndexKey10], " & _
        "[Archive].[IndexKey01Label], [Archive].[IndexKey02Label], [Archive].[IndexKey03Label], " & _
        "[Archive].[IndexKey04Label], [Archive].[IndexKey05Label], [Archive].[IndexKey06Label], " & _
        "[Archive].[IndexKey07Label], [Archive].[IndexKey08Label], [Archive].[IndexKey09Label], " & _
        "[Archive].[IndexKey10Label] " & _
        "FROM ((((((Archive LEFT JOIN MainTitle ON [Archive].[ArchiveKey] =[MainTitle].[ArchiveKey]) " & _
        "LEFT JOIN eSubFolderTable ON [Archive].[ArchiveKey] =[eSubFolderTable].[eCabinetKey]) " & _
        "LEFT JOIN SubLevelTwo ON [eSubFolderTable].[SubLevelOneKey] =[SubLevelTwo].[SubLevelOneKey]) " & _
        "LEFT JOIN SubLevelThree ON [SubLevelTwo].[SubLevelTwoKey] =[SubLevelThree].[SubLevelTwoKey]) " & _
        "LEFT JOIN SubLevelFour ON [SubLevelThree].[SubLevelThreeKey] =[SubLevelFour].[SubLevelThreeKey]) " & _
        "LEFT JOIN SubLevelFive ON [SubLevelFour].[SubLevelFourKey] =[SubLevelFive].[SubLevelFourKey]) " & _
        "LEFT JOIN SubLevelSix ON [SubLevelFive].[SubLevelFiveKey] =[SubLevelSix].[SubLevelFiveKey] " & _
        "Where [Archive].[ArchiveName] <> 'SSSSSzS'"
        
    AdodcLvl.RecordSource = "SELECT [Archive].[ArchiveName], [MainTitle].[MainLevelTitle], " & _
        "[eSubFolderTable].[SubLevelOneName], [Archive].[ArchiveKey], " & _
        "[MainTitle].[MainTitleKey], [eSubFolderTable].[SubLevelOneKey], [MainTitle].[IndexKey01], " & _
        "[MainTitle].[IndexKey02], [MainTitle].[IndexKey03], [MainTitle].[IndexKey04], " & _
        "[MainTitle].[IndexKey05], [MainTitle].[IndexKey06], [MainTitle].[IndexKey07], " & _
        "[MainTitle].[IndexKey08], [MainTitle].[IndexKey09], [MainTitle].[IndexKey10], " & _
        "[Archive].[IndexKey01Label], [Archive].[IndexKey02Label], [Archive].[IndexKey03Label], " & _
        "[Archive].[IndexKey04Label], [Archive].[IndexKey05Label], [Archive].[IndexKey06Label], " & _
        "[Archive].[IndexKey07Label], [Archive].[IndexKey08Label], [Archive].[IndexKey09Label], " & _
        "[Archive].[IndexKey10Label] FROM ((Archive LEFT JOIN MainTitle ON [Archive].[ArchiveKey] " & _
        "=[MainTitle].[ArchiveKey]) LEFT JOIN eSubFolderTable ON [Archive].[ArchiveKey] " & _
        "=[eSubFolderTable].[eCabinetKey]) WHERE 1=1"
    
    ' this methode is not used anymore because it queries all documents, then a loop is used
    ' to find and print the documents belonging to each eFolder or eSubFolder.  Instead only
    ' documents specific to a certain eFolder or eSubFolder are directly queried everytime
    ' that eFolder or that eSubFolder is being printed.  (look at code below <' print documents>
    ' and <'Query documents>
    
'    AdodcDoc.RecordSource = "SELECT Document.DocumentName, Document.DocumentLocation, " & _
        "Document.ArchiveGroupKey, Document.ArchiveKey, Document.MainTitleKey, " & _
        "Document.SubLevelOneKey, Document.SubLevelTwoKey, Document.SubLevelThreeKey, " & _
        "Document.SubLevelFourKey, Document.SubLevelFiveKey, Document.SubLevelSixKey, " & _
        "Document.DocLevel FROM Document Where Document.DocLevel <> '10'"
        
    If allECabinets = False Then
        AdodcLvl.RecordSource = AdodcLvl.RecordSource & " and [Archive].[ArchiveKey] = '" & archiveKey & "'"
'        AdodcDoc.RecordSource = AdodcDoc.RecordSource & " and Document.ArchiveKey = '" & archiveKey & "'"
        If mnuOptionsAllEFolders.Checked = False Then
            AdodcLvl.RecordSource = AdodcLvl.RecordSource & " and [MainTitle].[MainTitleKey] = '" & mainTitleKey & "'"
'            AdodcDoc.RecordSource = AdodcDoc.RecordSource & " and Document.MainTitleKey = '" & mainTitleKey & "'"
        End If
    End If
    
    ' add search criteria to SQL statement if searching by eIndexFields is selected
    If checkIndexes.Value = 1 Then
        AdodcLvl.RecordSource = AdodcLvl.RecordSource & txtSQLStatement.Text
    End If
    
    ' sort records and refresh recordSet
    AdodcLvl.RecordSource = AdodcLvl.RecordSource & " ORDER BY [Archive].[ArchiveName], [Archive].[archiveKey], " & _
        "[MainTitle].[MainLevelTitle], [MainTitle].[mainTitleKey], [eSubFolderTable].[SubLevelOneName]"
    AdodcLvl.Refresh
    
'    AdodcDoc.RecordSource = AdodcDoc.RecordSource & " ORDER BY Document.DocumentName"
'    AdodcDoc.Refresh
    
    ' move to last and first record to ensure that the query is ready.
    AdodcLvl.Recordset.MoveFirst
    AdodcLvl.Recordset.MoveLast
    AdodcLvl.Recordset.MoveFirst
    Text1.Text = "# Records = " & AdodcLvl.Recordset.RecordCount
'    Text2.Text = "# Documents = " & AdodcDoc.Recordset.RecordCount
    i = AdodcLvl.Recordset.RecordCount
    If i <> 0 Then
        arrLvl = AdodcLvl.Recordset.GetRows(i)
    ElseIf i = 0 Then
        ' set InitializeArraysArcGrp to False to indicate that there is no records in the recordSet
        ' this value is used to print the report or not
        InitializeArraysArcGrp = False
    End If
    AdodcLvl.Recordset.Close
'    AdodcDoc.Recordset.Close
    
'    AdodcDoc.Recordset.MoveLast
'    AdodcDoc.Recordset.MoveFirst
'    i = AdodcDoc.Recordset.RecordCount
'    If i <> 0 Then
'        arrDoc = AdodcDoc.Recordset.GetRows(i)
'        noDocuments = False
'    ElseIf i = 0 Then
''        InitializeArraysArcGrp = False
'        noDocuments = True
'    End If

What would you suggest to use instead of ADODC controls which are not used any more. Any ideas would be great...

Thank you,

Art Lorenzini

SD

__._,_.___

---

Posted by: Art Lorenzini <dbalorenzini@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___