Need Help, Ilogic Microsoft Access tables to list ??

[willemTHLQC]

Im using scripts based of this ilogic and it works great .

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"

Imports System.Data.OleDb
Imports System.Data
Imports System.Xml


Sub Main()

	Dim Table_ As String = "EmployeeInfo01"
	Dim query As String = "SELECT * FROM " & Table_
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	cnn.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim da As New OleDbDataAdapter(cmd)
	da.Fill(ds, Table_)
	cnn.Close()
	Dim t1 As DataTable = ds.Tables(Table_)
	Dim row As DataRow
	Dim Item(2) As String
	For Each row In t1.Rows
		MessageBox.Show("EID : " & row(0) & " and Employee Name : " & row(1)
	Next
End Sub

 i want to build pre select for for this. 

this means that in the accdb file there are more then one  table

for example have 

EmployeeInfo01

EmployeeInfo02

EmployeeInfo03

EmployeeInfo04

EmployeeInfo05

EmployeeInfo06

Employee_Tables_list.Add(??) *checks the accdb  file tables and add them to the list**

MultiValue.List("Employee_Tables") = Employee_Tables_list 

does anybody know how to do this??

[chandra.shekar.g]

@willemTHLQC,

Try below iLogic code to get names from all tables (Assumed that employee names are extracted from 2nd column. As columns are starting from 0th column, 2nd column would becomes 1st column (row(1)) in coding).

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Imports System.Collections



Sub Main()
	Dim cnt As Integer 
	cnt = 6
	Dim oList(cnt) As String 
	oList(0) = "EmployeeInfo1"
	oList(1) = "EmployeeInfo2"
	oList(2) = "EmployeeInfo3"
	oList(3) = "EmployeeInfo4"
	oList(4) = "EmployeeInfo5"
	oList(5) = "EmployeeInfo6"
	
	Dim Employee_Tables_list As New ArrayList()
	
	For i = 0 To cnt -1
		Dim Table_ As String = oList(i)
		Dim query As String = "SELECT * FROM " & Table_
		Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Chandra\Autodesk Cases\Inventor\Sep-2019\15772811\employeeinfo\EmployeeInfo.accdb;Persist Security Info=False;"
		Dim ds As New DataSet
		Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
		cnn.Open()
		Dim cmd As New OleDbCommand(query, cnn)
		Dim da As New OleDbDataAdapter(cmd)
		da.Fill(ds,Table_)
		cnn.Close() 	
		Dim dt As DataTable = ds.Tables(Table_)
		Dim row As DataRow 
		For Each row In dt.Rows
			Employee_Tables_list.Add (row(1))
		Next
	Next 
	
	For Each s In Employee_Tables_list 
		MessageBox.Show(s)
	Next
	
End Sub

Thanks and regards,

[willemTHLQC]

Tank you for you time chandra.shekar.g,

But this is not how i meant it.

it needs to read the accdb file and export the the current table names to a list when i add a table name in the accdb file it will be added next time when i run the script, i dont want it edit the ilogic script every time im adding a table to the file.

[chandra.shekar.g · Accepted solution]

@willemTHLQC,

Finally, list of tables are added to "Employee_Tables_list" using below iLogic code.

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Imports System.Collections




Sub Main() 
	Dim Employee_Tables_list As New ArrayList()  
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path of database\EmployeeInfo.accdb;Persist Security Info=False;"
	 
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	cnn.Open() 
	  
	Dim oRows() As DataRow 
	oRows = cnn.GetSchema("Tables").Select("Table_Type = 'table'")
	
	Dim oRow As DataRow
	For Each oRow In oRows
		Employee_Tables_list.Add(oRow("TABLE_NAME"))
	Next
	 
	cnn.Close() 
		 
	For Each s In Employee_Tables_list 
		MessageBox.Show(s)
	Next
End Sub

Thanks ans regards,

[willemTHLQC]

Thank you, this works great 👍