Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
Anonymous
in reply to: AlexFielder

Just came across this thread while searching for iLogic Access Commands.

 

I'm working on a tool that will read and write records into an Access Database. The above code was remixed into something that worked for me. Using an access database found on this forum I was able to determine the next available line (or row) to enter employee data in. I found this particularly helpful in being the bases of my code in recording invoices, PO's and quotes.

 

Below is the line of code I've used... attach you will find the access database (based on another user's database on this forum).

 

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 = "EmployeeInfo"
	Dim query As String = "SELECT * FROM " & Table_
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
    Dim ds12 As DataSet
    ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")
	
		'DETERMINE THE NEXT AVAILABLE ROW IN ACCESS
		If ds12.Tables("EmployeeInfo").Rows.Count = 0 Then
			i = i + 1
      	Else
		   query = "SELECT MAX(EID) from EmployeeInfo"
           cmd = New OleDbCommand(query, con)
           adp = New OleDbDataAdapter(cmd)
           ds12 = New DataSet
           adp.Fill(ds12, "EmployeeInfo")
           i = (ds12.Tables(0).Rows(0).Item(0)) + 1
           con.Close()
       End If
	   
	   'VERIFY THE ABOVE CODE
		MessageBox.Show(i)
		
   End Sub