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: dbalorenzini@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar