-
Sep 9th, 2021, 11:08 AM
#1
Thread Starter
Hyperactive Member
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.
-
Sep 9th, 2021, 01:46 PM
#2
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???
-
Sep 9th, 2021, 04:01 PM
#3
Thread Starter
Hyperactive Member
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.
-
Sep 9th, 2021, 06:42 PM
#4
Re: Need help converting VBA to VB.Net
Code:
Debug.WriteLine(DirectCast(idx.Fields, TableDef.Fields).ToString)
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Sep 10th, 2021, 01:04 AM
#5
Thread Starter
Hyperactive Member
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
-
Sep 10th, 2021, 02:34 AM
#6
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.
-
Sep 10th, 2021, 12:29 PM
#7
Thread Starter
Hyperactive Member
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!
-
Sep 14th, 2021, 01:43 AM
#8
Re: Need help converting VBA to VB.Net
Originally Posted by Mark@SF
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.
-
Sep 14th, 2021, 03:08 AM
#9
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|