Code Conversion from Access 2007 VBA to VB.Net RRS feed

  • Question

  • Hi All,

    I am rewriting an Access 2007 database application in VB.Net. I need to add empty records in to a table that after the empty records will be populated with actual information that will print to the labels in the correct position on a used sheet of labels.

    I have had this working for many years in VBA, but have no idea how to do it in VB.Net.

    The included code is what I have in VBA right now. I just need the code equivalent to replace Variant and NZ.

    Here is my VBA Code

            Dim bytPosition As Variant
            Dim bytCounter As Byte

    ' Add one empty record at the begin of the table before actually adding data after empty records.

              bytPosition = Nz(Me!txtStart.Value, 0)
              For bytCounter = 2 To bytPosition
    ' Code that went here to add one empty record at a time


    • Edited by Laredo_TX Friday, November 13, 2020 7:02 AM
    Friday, November 13, 2020 6:56 AM

All replies

  • Try this equivalent:

       Dim bytPosition As Byte

       If Not Byte.TryParse(txtStart.Text, bytPosition) Then bytPosition = 0

    Friday, November 13, 2020 9:35 AM
  • Ok, here is how I would approach this.

    And you well see that the code from VBA is very similar.

    The trick is to create a data table (think of this as a Access recordset).

    The only extra code is to create a "thingy" called a "command builder".

    This "thingy" creates the insert, update and delete commands.

    So you need this one extra thingy

    So, the code would/could work like this:

            Dim rst As New DataTable
            Using daREAD As New OleDbDataAdapter("SELECT * from tblFood2 where ID = 0",
                  New OleDbConnection(My.Settings.TestDB))
                Dim cmdBuilder = New OleDbCommandBuilder(daREAD)
                ' ADD 4 rows 
                For i = 1 To 4
                    With rst.Rows.Add
                        .Item("Food") = "Food test " & i
                    End With
                ' now save
            End Using

    Now above adds 4 rows to the table (field/column = food), but I of course could have set a few more columns.

    Note that you can often use sql insert statements (say just like in Access), but using a data table like above will get you up to speed quite a bit faster, since you feel more like writing a VBA reocrdset code approach, then having say to setup a sql insert statement - likely with some parameters.  The above is also nice, since then for date types, string types, and numbers? Well, you just assign to the new row in the table - no messing around with safe data type conversions  - it's all done for you.


    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Wednesday, November 18, 2020 11:10 PM