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
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 (2) |
Tidak ada komentar:
Posting Komentar