Results 1 to 9 of 9

Thread: Need help converting VBA to VB.Net

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Need help converting VBA to VB.Net

    I need some help to convert some VBA code to VB.Net code.

    Here is the VBA code...

    Code:
    Function DescribeIndexField(tdf As DAO.TableDef, strField As String) As String
      'Purpose: Indicate if the field is part of a primary key or unique index.
      'Return: String containing "P" if primary key, "U" if uniuqe index, "I" if non-unique index.
      ' Lower case letters if secondary field in index. Can have multiple indexes.
      'Arguments: tdf = the TableDef the field belongs to.
      ' strField = name of the field to search the Indexes for.
      '...the idxField value, for example:
      '   +TaskID;+ActionItemName
      
      On Error GoTo PROC_ERR
      
      Dim flgDebug As Boolean
      Dim i As Integer, iCount As Integer
      Dim strReturn As String 'Return string
      Dim idx As DAO.Index 'Each index of this table.
      Dim fld As DAO.Field 'Each field of the index
      
      For Each idx In tdf.Indexes
        iCount = 0
        For Each fld In idx.Fields
          If fld.Name = strField Then
            If idx.Primary Then
              strReturn = "P, " & idx.Fields
            ElseIf idx.Unique Then
              strReturn = "U, " & idx.Fields
            Else
              strReturn = "I, " & idx.Fields
            End If
          End If
          iCount = iCount + 1
        Next fld
      Next idx
      
      DescribeIndexField = strReturn
      
    PROC_EXIT:
      Set idx = Nothing
      Set fld = Nothing
      Exit Function
    
    PROC_ERR:
      Select Case Err.Number
        Case Else
          MsgBox "Error #" & Err.Number & vbCrLf & _
          Chr(34) & Err.Description & Chr(34), vbOKOnly + vbCritical, "DescribeIndexField"
      End Select
      
      Resume PROC_EXIT
      
    End Function
    This VBA function compiles and executes without errors in a Microsoft Access 2019 database application.

    I am having trouble in VB.Net with the statements that contain "idx.Fields". When the VBA code runs, for example, the idx.Fields value is "+TaskID;+ActionItemName". In this example, the index is a unique index and the 2 fields in the index are "TaskID" and "ActionItemName".

    My Visual Studio project has a reference to the "Microsoft.Office.interop.access.dao" assembly (version 15.0.0.0).

    Here is the VB.Net code that I need help with...

    Code:
    For Each fld As Field In idx.Fields
        If fld.Name = strField Then
            If idx.Primary Then
                strReturn = "P, " & idx.Fields
            ElseIf idx.Unique Then
                strReturn = "U, " & idx.Fields
            Else
                strReturn = "I, " & idx.Fields
            End If
        End If
        iCount = iCount + 1
    Next fld

    The VS Intellisense shows "BC32023 Expression is of type 'Object', which is not a collection type." and links to the following:

    https://docs.microsoft.com/en-us/dot...k%3Dk(BC32023)

    I've looked through a lot of Microsoft documentation without success, for example:

    DAO Index Objects
    https://docs.microsoft.com/en-us/off...dex-object-dao

    When I single-step through the VB.Net code in Debug mode, idx.Fields looks like a ComObject.

    Code:
    Debug.WriteLine(idx.Fields.ToString) -->> System.__ComObject
    I initially thought that I needed to cast idx.Fields but didn't know what it should be casted to.

    Any assistance would be greatly appreciated.
    Last edited by Mark@SF; Sep 9th, 2021 at 04:09 PM.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Need help converting VBA to VB.Net

    In your VBA code "idx" has a data type of DAO.TableDef. What data type is it in your .Net code? Are you using DAO???

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Need help converting VBA to VB.Net

    @wes4dbt -

    Thanks for taking a look at this with me.

    I'm declaring idx as a Microsoft.Office.Interop.Access.Dao.Index object. Here's the explicit declaration in my For...Next loop.

    Code:
    For Each idx As Microsoft.Office.Interop.Access.Dao.Index In tdf.Indexes
        iCount = 0
        Debug.WriteLine("{0}, {1}, {2}", idx.Name, idx.Primary, idx.Fields)
    Next idx
    The Debug.Writeline writes this result to the Immediate window, so I'm confident that I've declared the idx variable correctly.

    AddDate, False, System.__ComObject

    One thing that I pointed out in my first post is that the VBA code shows the value of idx.Fields to be "+TaskID;+ActionItemName" which does not look like something I would expect from a collection (MDSN explains that "Each Field object in the Fields collection of an Index object is a component of the index.").

    I've done a fair bit of VBA-to-VB.Net code conversions and have done a lot of MS Office automation of Excel, Word, and Access via various VB.Net applications. This issue is vexing for me because I'm lost for where to begin.

    Thank you again for your interest and help. I'm hoping to find a solution!
    Last edited by Mark@SF; Sep 9th, 2021 at 04:10 PM.

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Need help converting VBA to VB.Net

    Code:
    Debug.WriteLine(DirectCast(idx.Fields, TableDef.Fields).ToString)

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Need help converting VBA to VB.Net

    @paul -

    Thank you for your help.

    When I tried your suggestion, Intellisense shows the following error:

    Error BC30002 Type 'TableDef.Fields' is not defined.

    Code:
    For Each idx As Index In tdf.Indexes
        Debug.WriteLine(DirectCast(idx.Fields, TableDef.Fields).ToString)
    Next idx

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: Need help converting VBA to VB.Net

    try this way

    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Using cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=D:\Db2010.accdb")
                cnn.Open()
                Dim schemaIndexes As DataTable = cnn.GetSchema("Indexes")
                For Each row As DataRow In schemaIndexes.Rows
                    Debug.WriteLine(row.Field(Of String)("TABLE_NAME") & "-" & row.Field(Of String)("INDEX_NAME") & "-" & row.Field(Of String)("COLUMN_NAME"))
                Next row
            End Using
        End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Need help converting VBA to VB.Net

    @ChrisE -

    Thank you. Your suggestion works.

    Since my question was for how to use DAO to get the fields of an index, I'm going to leave this thread open to see if there's solution for the idx.Fields DAO error.

    Thanks again for your help!

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: Need help converting VBA to VB.Net

    Quote Originally Posted by Mark@SF View Post

    Since my question was for how to use DAO to get the fields of an index, I'm going to leave this thread open to see if there's solution for the idx.Fields DAO error.
    here you go...with DAO
    it won't work with Option Strict On, but I'm not going to try further with DAO an resolve that also

    Code:
    'Option Strict On
    Imports Microsoft.Office.Interop.Access.Dao
    
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim AccessDatabaseEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine()
            Dim AccessDatabase As Microsoft.Office.Interop.Access.Dao.Database
            Dim tbl As Microsoft.Office.Interop.Access.Dao.TableDef
            Dim fld As Microsoft.Office.Interop.Access.Dao.Field
            Dim idx As Microsoft.Office.Interop.Access.Dao.Index
            Dim strtblProperty As String '<- this is needed , or else there will be System.Runtime.InteropServices.COMException
    
            AccessDatabase = AccessDatabaseEngine.OpenDatabase("D:\Db2010.accdb")
            For Each tbl In AccessDatabase.TableDefs
                strtblProperty = TabellenAttribute(tbl)
                If strtblProperty = "" Then
                    With tbl
                        Debug.WriteLine(.Name)
                        Debug.WriteLine(String.Format("{0}  {1}", "DateCreated =", .DateCreated))
                        Debug.WriteLine(String.Format("{0}  {1}", "LastUpdated=", .LastUpdated))
                        Debug.WriteLine(String.Format("{0}  {1}", "Recordcount=", .RecordCount))
                        Debug.WriteLine("------------------------------------------------")
                        Debug.WriteLine("Indexes:")
                        For Each idx In tbl.Indexes
                            With idx
                                Debug.WriteLine(Space(4) & idx.Name)
                                Debug.WriteLine(Space(8))
                                Debug.WriteLine(IIf(.Primary, "Primary ", " "))
                                Debug.WriteLine(IIf(.Unique, "Unique ", " "))
                                Debug.WriteLine(IIf(.Foreign, "Foreign", " "))
                                'Debug.WriteLine("------------------------------------------------")
                                Debug.WriteLine(Space(8) & "Indexfields:")
                                For Each fld In idx.Fields
                                    Debug.WriteLine(Space(20) & fld.Name)
                                Next
                                Debug.WriteLine("------------------------------------------------")
                            End With
                        Next
                    End With
                End If
            Next
        End Sub
    
        Public Function TabellenAttribute(tbl As Microsoft.Office.Interop.Access.Dao.TableDef) As String
            Dim strResult As String = Nothing
            With tbl
                If CBool(.Attributes And Microsoft.Office.Interop.Access.Dao.TableDefAttributeEnum.dbSystemObject) Then
                    strResult = "SystemObject "
                End If
                If CBool(.Attributes And Microsoft.Office.Interop.Access.Dao.TableDefAttributeEnum.dbHiddenObject) Then
                    strResult = strResult + "HiddenObject "
                End If
                If CBool(.Attributes And Microsoft.Office.Interop.Access.Dao.TableDefAttributeEnum.dbAttachExclusive) Then
                    strResult = strResult + "AttachExclusive "
                End If
                If CBool(.Attributes And Microsoft.Office.Interop.Access.Dao.TableDefAttributeEnum.dbAttachSavePWD) Then
                    strResult = strResult + "AttachSavePWD "
                End If
                If CBool(.Attributes And Microsoft.Office.Interop.Access.Dao.TableDefAttributeEnum.dbAttachedTable) Then
                    strResult = strResult + "AttachedTable "
                End If
                If CBool(.Attributes And Microsoft.Office.Interop.Access.Dao.TableDefAttributeEnum.dbAttachedODBC) Then
                    strResult = strResult + "AttachedODBC "
                End If
            End With
            TabellenAttribute = strResult
        End Function
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Need help converting VBA to VB.Net

    @ChrisE -

    Thank you for following up with a DAO solution. I really appreciate your help!

    I see in your solution that Option Strict must be Off for it to work and with this compiler directive, the idx.Fields statements don't raise any errors.

    Can you explain why the compiler not like the idx.Fields object?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width