Jumat, 15 September 2017

Re: [MS_AccessPros] Upgrade to new code

 

You can use DLookup() or DCount() to determine if records in a table or query meet criteria.


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, September 15, 2017 4:31:45 PM
To: John Viescas JohnV@msn.com [MS_Access_Professionals]
Subject: 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 
(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: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar