none
access select all tablet and all fields RRS feed

  • Question

  • Private Sub ComboBoxs()
            Access.AddParam("@True", True)
            Access.ExecQuery("SELECT * FROM * ")
    
            If NoErrors(True) = False Then Exit Sub
    
            For Each R As DataRow In Access.DBDT.Rows
                CBXStatus.Items.Add(R("Status FROM tblstatus"))
    
            Next

    i am trying to select all fields from all tablets in my access database the above code is what i though would work but no, can anyone help

    the code selects the info from the relevant field from the relevant table and places it into a combo box

    im trying to shorten my code significantly as the code is currently 59 lines long and that make the program run slow

    if any more info is needed please ask as i know it might be a strange request


    Tuesday, January 30, 2018 10:44 AM

Answers

All replies

  • Hello,

    If this is not VB.NET but instead VBA code inside MS-Access I need to move your question to the Access for developers forum, please let me know if this is VB.NET or code for inside MS-Access.

    If VB.NET here is solution that requires at least VS2015 for the Iterator/Yield construct. I'm only doing one field here but the code can be modified to do as many fields as you need by returning a class rather than a single string.

    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New DataOperations
            For Each userName As String In ops.GetData
                ComboBox1.Items.Add(userName)
            Next
        End Sub
    End Class
    Public Class DataOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Public Iterator Function GetData() As IEnumerable(Of String)
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT username from login"
                    }
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                    While reader.Read
                        Yield reader.GetString(0)
                    End While
    
                End Using
            End Using
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Tuesday, January 30, 2018 10:46 AM
    Moderator
  • this is a vb.net application with ms access as the database, so vb.net
    Tuesday, January 30, 2018 10:55 AM
  • this is a vb.net application with ms access as the database, so vb.net

    So then the following (modified from my first post) shows how to populate a ComboBox with each field but shows one field. Note Button1 click event shows how to get all fields from the selected ComboBox item.

    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim userList As New List(Of User)
    
            Dim ops As New DataOperations
            For Each user As User In ops.GetData
                userList.Add(user)
            Next
            ComboBox1.DataSource = userList
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim user As User = CType(ComboBox1.SelectedItem, User)
            MessageBox.Show($"{user.Id}, {user.UserName}, {user.Country}")
        End Sub
    End Class
    Public Class DataOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Public Iterator Function GetData() As IEnumerable(Of User)
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT id,username, country from login"
                    }
    
                    cn.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                    While reader.Read
                        Yield New User With
                            {
                                .Id = reader.GetInt32(0),
                                .UserName = reader.GetString(1),
                                .Country = reader.GetString(2)
                            }
                    End While
    
                End Using
            End Using
        End Function
    End Class
    Public Class User
        Public Property Id As Integer
        Public Property UserName As String
        Public Property Country As String
        Public Overrides Function ToString() As String
            Return UserName
        End Function
    End Class
    

    Or

    Only change to the first is the following


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 30, 2018 11:10 AM
    Moderator
  • to make it easier ill try and writ its as some one would say it

    i want to select all fields from all tables, then for each combo box fill it with the relevant data,:

    select all fields from all tablets

    place data from status field in tblstatus into combobox status

    place data from priority from table tbl priority into combobox priority

    i can do the combobox fills separately and with alot of select statments but want to know if i can shorten it

    Tuesday, January 30, 2018 11:37 AM

  • i can do the combobox fills separately and with alot of select statments but want to know if i can shorten it

    Well the only way to get at data is to use SELECT statements, no way around that.

    In regards to SELECT * FROM *, there is no * for FROM.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 30, 2018 12:22 PM
    Moderator
  • ok thats fine thank you for your time

    Tuesday, January 30, 2018 12:33 PM
  • Take this code from our website and put instead of that console writeline a select * from on each table which comes in the scheme

    http://www.vb-tips.com/AccessListTables.aspx


    Success Cor

    Tuesday, January 30, 2018 12:41 PM
  • Not sure exactly what you want to do with the ComboBoxes, but the below code will loop through all of the tables in an Access database and display the respective column names and associated values for each row:

        Sub GetDataFromAllAccessTables()
    
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                    "Data Source=C:\Users\...\Documents\My Database\Access\Northwind.accdb;"
            Dim schemaConnection As New System.Data.OleDb.OleDbConnection(connectionString)
            schemaConnection.Open()
            Dim restrictions() As String = {Nothing, Nothing, Nothing, Nothing}
            Dim collectionName As String = "Tables"
            Dim dt As DataTable = schemaConnection.GetSchema(collectionName, restrictions)
    
            Dim dataConnection As New System.Data.OleDb.OleDbConnection(connectionString)
            dataConnection.Open()
    
            For Each tableRow As DataRow In dt.Rows
                Select Case tableRow("TABLE_TYPE")
                    Case "TABLE", "LINK", "PASS-THROUGH"
    
                        Console.WriteLine(tableRow("TABLE_NAME") & ":" & tableRow("TABLE_TYPE"))
    
                        Dim accessDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" & tableRow("TABLE_NAME") & "]", dataConnection)
                        Dim accessDataTable As New DataTable
                        accessDataAdapter.Fill(accessDataTable)
    
                        Dim rowOfData As DataRow
                        For Each rowOfData In accessDataTable.Rows
                            Dim columnOfData As System.Data.DataColumn
                            For Each columnOfData In accessDataTable.Columns
    
                                Console.WriteLine(columnOfData.ColumnName & ":" & rowOfData(columnOfData.ColumnName).ToString)
    
                            Next
                        Next
    
                    Case Else
                        'Probably a system table or query
                End Select
            Next
    
            dataConnection.Close()
            schemaConnection.Close()
    
        End Sub


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, January 30, 2018 2:15 PM