Ilogic MS Access find next empty record and record new record

[j.romo]

Hello

Been using excel Spreadsheets to collect model information and ran into some issues with users trying to open the same Spreadsheet at the same time and giving us some headaches.

Now we are trying to go further and use a database with MS access,

and as far as i vcould find information on this board the only thing I could do is read information from the Database

Now I want to add information from Ilogic to the database, but just cant find the next empty record to record on it the fields I need. Here is the code Im using.

SyntaxEditor Code Snippet

Sub Main()

	Dim Table1 As String = "CAD"
	Dim query As String = "SELECT * FROM " & Table1
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.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, Table1)
	cnn.Close()
	Dim t1 As DataTable = ds.Tables(Table1)
	Dim row As DataRow
	Dim Item(2) As String
	'Dim oNAME As String
Dim OPROJ As String
Dim oCREAT As String
Dim oDAT As String
Dim oCOMENT As String
Dim oSTATUS As String
oNAME = MT_NAMING
OPROJ = iProperties.Value("Project", "Project")
oDAT = iProperties.Value("Project", "Creation Date")
oCREAT = ThisApplication.GeneralOptions.UserName
oCOMENT = DESCRIPCION
oSTATUS = STATUS_MASTER	


	For Each row In t1.Rows
		If row(1) = oNAME Then
		i = MessageBox.Show("This part Number is allready recorded", "CAD", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)
ElseIf row(1) = "" Then
	i = MessageBox.Show("Testing empty record", "CAD", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)
	row(1)=MT_NAMING

	End If
	
		
	Next
End Sub

[dean.morrison]

Hi,

 I havent done this with ilogic, but i use VBA to do similar..

This is the code i usually use to add new rows to the database..

rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open "Tablename", cn, , , adCmdTable

rs.AddNew

rs!FieldName = DataString

rs.Update
rs.Close

I hope that helps you.

Dean.

[j.romo]

Thanks for the reply but I dont Understand how to use your code.

[dean.morrison]

Ahhh ok.. i thought u may be able to adapt it to ilogic..

i have no time to look into it for you, maybe someone else can help.

[j.romo · Accepted solution]

Finally did it, 

SyntaxEditor Code Snippet

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

 SyntaxEditor Code Snippet

Sub Main()

'	Dim Tabla As String = "CAD"
	Dim query As String = "SELECT * FROM CAD WHERE PartNo =  '" & MT_NAMING & "'"
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.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, "CAD")
	cnn.Close()
	Dim **** = ds.Tables("CAD").rows.count

If **** > 0 Then
	MessageBox.Show(**** & "This Part Number is allready added to the database!", MT_NAMING )
Else 
	

Dim OPROJ As String
Dim oCREAT As String
Dim oDAT As Date
Dim oCOMENT As String
Dim oSTATUS As String
oNAME = MT_NAMING
OPROJ = iProperties.Value("Project", "Project")
oDAT = iProperties.Value("Project", "Creation Date")
oCREAT = ThisApplication.GeneralOptions.UserName
oCOMENT = DESCRIPCION
oSTATUS = STATUS_MASTER	

    Dim Table_ As String = "CAD"
    Dim query1 As String = "INSERT INTO " & Table_ & "(Proyect, PartNo, AutorDePieza, FechaCreacion, DescripcionDePieza,Estado) VALUES ('"&iProperties.Value("Project", "Project")&"','"& MT_NAMING &"', '"& oCREAT &"', '"& oDAT &"', '"& oCOMENT &"', '"& STATUS_MASTER &"')"
    Dim MDBConnString_1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.accdb;Persist Security Info=False;"
    Dim ds1 As New DataSet
    Dim cnn1 As OleDbConnection = New OleDbConnection(MDBConnString_1)
    cnn1.Open()
    Dim cmd1 As New OleDbCommand
    cmd.Connection = cnn1
    cmd.CommandText = query1
    cmd.ExecuteNonQuery()
    cnn1.Close()
End If
End Sub

[AlexFielder]

Thanks for sharing!

[Anonymous]

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