; *******************************************************
; Examples for Access.au3
; *******************************************************
;
AutoItSetOption ("MustDeclareVars", 1)

#include "Access.au3"
Global $datafield48

;MsgBox (0, "1.", "Funtion _AccessOpen()")
;	Example_AccessOpen()
;MsgBox (0, "2.", "Function _AccessTableExists()")
;	Example_AccessTableExists()
;MsgBox (0, "3.", "Function _AccessTablesCount()")
;	Example_AccessTablesCount()
;MsgBox (0, "4.", "Function _AccessFieldsCount()")
;	Example_AccessFieldsCount()
;MsgBox (0, "5.", "Function _AccessFieldExists()")
;	Example_AccessFieldExists()
;MsgBox (0, "6.", "Function_AccessRecordsCount()")
;	Example_AccessRecordsCount()
;MsgBox (0, "7.", "Function_AccessTablesList()")
;	Example_AccessTablesList()
;MsgBox (0, "8.", "Function_AccessFieldsList()")
;	Example_AccessFieldsList()
MsgBox (0, "9.", "Function _AccessRecordList() - Show entry")
	Example_AccessRecordList()
MsgBox (0, "10.", "Function_AccessRecord_Edit()")
	Example_AccessRecord_Edit()

;-- New in Rev 2.1
;MsgBox (0, "11.", "Example_ErrorCode_Msg ()")
;	Example_ErrorCode_Msg()

;MsgBox (0, "12.", "Example_AccessActionQuery()")
;	Example_AccessActionQuery ()

;MsgBox (0, "13.", "Example_AccessSelectQueryFetch()")
;	Example_AccessSelectQueryFetch ()

;MsgBox (0, "14.", "Example_AccessRecordMove()")
;	Example_AccessRecordMove()
 MsgBox (0,0, "Done")

;--------------------------------------------------------------
	;++++++++++++later

	; _AccessUpdateTable ($oNewDB, $oTable)
	; _AccessCreateTable (ByRef $o_object, $s_TableName ="")
;================================================================

Exit

; *******************************************************
; 1 - Open existing Access file
; *******************************************************
;
Func Example_AccessOpen()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	Else
		MsgBox(0, "Information", "Database file was opened :-" & @CR & @ScriptDir & "\testdb.mdb")
	EndIf

	_AccessClose($o_DataBase)
EndFunc   ;==>Example_AccessOpen

; *******************************************************
; 2 - Open existing Access file, Check if table is Exisits
; *******************************************************
;
Func Example_AccessTableExists()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $O_TableObject

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	$O_TableObject = _AccessTableExists ($o_DataBase, "tblContacts")
	If $O_TableObject = 0 Then
		MsgBox(0, "Information", "Table does not Exist. ")
		Return
	Else
		MsgBox(0, "Information", "Table tblContacts Exists. ")
	EndIf

	_AccessClose($o_DataBase)
EndFunc   ;==>Example_AccessTableExists

; *******************************************************
; 3 - Open existing Access file, Count Tables
; *******************************************************
;
Func Example_AccessTablesCount()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $n_UserTables, $n_All_Tables

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	$n_UserTables = _AccessTablesCount ($o_DataBase)
	$n_All_Tables = _AccessTablesCount ($o_DataBase, False)

	MsgBox(0, "Information", "User Table count is " & $n_UserTables)

	MsgBox(0, "Information", " All Table count is " & $n_All_Tables)



	_AccessClose($o_DataBase)
EndFunc   ;==>Example_AccessTableExists

; *******************************************************
; 4 - Count Number of fields in table
; *******************************************************
;
Func Example_AccessFieldsCount()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	;Local $O_TableObject

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	Local $n_Fields_Count = _AccessFieldsCount ($o_DataBase, "tblContacts")

	MsgBox (0, "Information", "No of Fields in Table1 is " & $n_Fields_Count)

	_AccessClose($o_DataBase)

EndFunc

; *******************************************************
; 5 - Check if a field is Exists
; *******************************************************
;
Func Example_AccessFieldExists()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $O_TableObject

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	$O_TableObject = _AccessTableExists ($o_DataBase, "tblContacts")
	If $O_TableObject = 0 Then
		MsgBox(0, "Information", "tblContacts Table does not Exists. ")
		Return
	EndIf

	Local $b_FieldExists = _AccessFieldExists ($O_TableObject, "fldQSLConfByS")
	if $b_FieldExists = 0 Then
		MsgBox(0, "Information", "Field fldQSLConfByS does not Exists. ")
		Return
	Else
		MsgBox (0, "Information", "Field fldQSLConfByS Exists.")
	EndIf

	_AccessClose($o_DataBase)
EndFunc

; *******************************************************
; 6 - Count Number of fields in table
; *******************************************************
Func Example_AccessRecordsCount()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	Local $n_RecCount = _AccessRecordsCount ($o_DataBase, "tblContacts")

	MsgBox(0, "Information", "No. Of Records in Table  is " & $n_RecCount)

	_AccessClose($o_DataBase)
EndFunc

; *******************************************************
; 7 - List tables in database
; *******************************************************

Func Example_AccessTablesList()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $avArray_Tables

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	$avArray_Tables = _AccessTablesList ($o_DataBase)
	_ArrayDisplay ($avArray_Tables)

	$avArray_Tables = _AccessTablesList ($o_DataBase, False)
	_ArrayDisplay ($avArray_Tables)

	_AccessClose($o_DataBase)

EndFunc
; *******************************************************
; 8 - List Fields tables
; *******************************************************

Func Example_AccessFieldsList()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $O_TableObject, $avArray_Fields ;, $O_TableObject

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf


	$avArray_Fields = _AccessFieldsList ($o_DataBase, "tblContacts")
	_ArrayDisplay ($avArray_Fields,"Fields")

	_AccessClose($o_DataBase)
EndFunc

; ******************************************************************************************
; 9 - 	List Record # (manual entry for now). Going to be a For/Next loop later to read each
;		record and add "Q" to fldQSLConfByS if empty or not in field
; ******************************************************************************************
Func Example_AccessRecordList()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $O_TableObject, $avArray_Record ;, $O_TableObject

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

		$avArray_Record = _AccessRecordList ($o_DataBase, "tblContacts", 7)	;show # entry in DB for now, Automated later
		ConsoleWrite($avArray_Record[1][0])
		_ArrayDisplay ($avArray_Record,"Records")	;display for testing
		MsgBox($MB_SYSTEMMODAL, "Row 48", "Value in Row 48: "&_ArrayToString($avArray_Record, "", 48,48))

;   check if Letter "Q" is in the field fldQSLConfByS. If Q is in the string -> next record
;	If not, add letter Q (todo: call Example_AccessRecord_Edit() to write record)
	$datafield48 = (_ArrayToString($avArray_Record,"",48,48))
if StringInStr($datafield48,"Q") Then
	MsgBox(0,"","Letter Q is in field: -> " & $datafield48)
Else
	MsgBox(0,"","Letter Q is not in field: -> " & $datafield48)
	$datafield48 = $datafield48 & "Q"
	msgbox(0,""," added""Q"", new value: " & $datafield48)
EndIf

_AccessClose($o_DataBase)
EndFunc

; *******************************************************
; 10 - Edit/change Record
; *******************************************************
Func Example_AccessRecord_Edit()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $avData[66][2]
	Local $n_Feedback

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	;---------- Data Setup
		$avData[0] [0] = "fldAge"
		$avData[1] [0] = "fldARCI"
		$avData[2] [0] = "fldband"
		$avData[3] [0] = "fldCall"
		$avData[4] [0] = "fldCategory"
		$avData[5] [0] = "fldCheck"
		$avData[6] [0] = "fldClass"
		$avData[7] [0] = "fldComments"
		$avData[8] [0] = "fldComputerName"
		$avData[9] [0] = "fldContestID"
		$avData[10] [0] = "fldContinent"
		$avData[11] [0] = "fldCountryDXCC"
		$avData[12] [0] = "fldCountryWorked"
		$avData[13] [0] = "fldCountyR"
		$avData[14] [0] = "fldCountyS"
		$avData[15] [0] = "fldCQZone"
		$avData[16] [0] = "fldDateStr"
		$avData[17] [0] = "fldFists"
		$avData[18] [0] = "fldFrequency"
		$avData[19] [0] = "fldFuture1"
		$avData[20] [0] = "fldFuture2"
		$avData[21] [0] = "fldGridR"
		$avData[22] [0] = "fldGridS"
		$avData[23] [0] = "fldIARUZone"
		$avData[24] [0] = "fldfldInitials"
		$avData[25] [0] = "fldIOTA"
		$avData[26] [0] = "fldITUZone"
		$avData[27] [0] = "fldLightHouse"
		$avData[28] [0] = "fldMode"
		$avData[29] [0] = "fldModeContest"
		$avData[30] [0] = "fldNameR"
		$avData[31] [0] = "fldNameS"
		$avData[32] [0] = "fldOperator"
		$avData[33] [0] = "fldOther1"
		$avData[34] [0] = "fldOther2"
		$avData[35] [0] = "fldOther3"
		$avData[36] [0] = "fldOther4"
		$avData[37] [0] = "fldOther5"
		$avData[38] [0] = "fldOther6"
		$avData[39] [0] = "fldOther7"
		$avData[40] [0] = "fldOther8"
		$avData[41] [0] = "fldPoints"
		$avData[42] [0] = "fldPower"
		$avData[43] [0] = "fldPrecedence"
		$avData[44] [0] = "fldPrefix"
		$avData[45] [0] = "fldPropMode"
		$avData[46] [0] = "fldQSLConfByR"
		$avData[47] [0] = "fldQSLConfByS"
		$avData[48] [0] = "fldQSLR"
		$avData[49] [0] = "fldQSLS"
		$avData[50] [0] = "fldQTHGroup"
		$avData[51] [0] = "fldRstR"
		$avData[52] [0] = "fldRstS"
		$avData[53] [0] = "fldSatName"
		$avData[54] [0] = "fldSection"
		$avData[55] [0] = "fldSerialNoR"
		$avData[56] [0] = "fldSerialNoS"
		$avData[57] [0] = "fldSPC"
		$avData[58] [0] = "fldSPCNum"
		$avData[59] [0] = "fldState"
		$avData[60] [0] = "fldStation"
		$avData[61] [0] = "fldTenTen"
		$avData[62] [0] = "fldTimeOffStr"
		$avData[63] [0] = "fltTimeOnStr"
		$avData[64] [0] = "fldTransmitterID"
		$avData[65] [0] = "fldPrimaryKey"

	;---------- Edit/Change Field 48 only
		$avData[0] [0] = ""		;How to keep old value?
		$avData[1] [1] = ""		;does every field need to be read from db
		$avData[2] [1] = ""		;and rewritten?
		$avData[3] [1] = ""		;maybe _ArrayToString($avArray_Record,"",X,X) where X = recordnumber?
		$avData[4] [1] = ""
		$avData[5] [1] = ""
		$avData[6] [1] = ""
		$avData[7] [1] = ""
		$avData[8] [1] = ""
		$avData[9] [1] = ""
		$avData[10] [1] = ""
		$avData[11] [1] = ""
		$avData[12] [1] = ""
		$avData[13] [1] = ""
		$avData[14] [1] = ""
		$avData[15] [1] = ""
		$avData[16] [1] = ""
		$avData[17] [1] = ""
		$avData[18] [1] = ""
		$avData[19] [1] = ""
		$avData[20] [1] = ""
		$avData[21] [1] = ""
		$avData[22] [1] = ""
		$avData[23] [1] = ""
		$avData[24] [1] = ""
		$avData[25] [1] = ""
		$avData[26] [1] = ""
		$avData[27] [1] = ""
		$avData[28] [1] = ""
		$avData[29] [1] = ""
		$avData[30] [1] = ""
		$avData[31] [1] = ""
		$avData[33] [1] = ""
		$avData[34] [1] = ""
		$avData[35] [1] = ""
		$avData[36] [1] = ""
		$avData[37] [1] = ""
		$avData[38] [1] = ""
		$avData[39] [1] = ""
		$avData[40] [1] = ""
		$avData[41] [1] = ""
		$avData[42] [1] = ""
		$avData[43] [1] = ""
		$avData[44] [1] = ""
		$avData[45] [1] = ""
		$avData[46] [1] = ""
		$avData[47] [1] = ""
		$avData[48] [1] = $datafield48 	;old or modified value with letter "Q"
		$avData[49] [1] = ""
		$avData[50] [1] = ""
		$avData[51] [1] = ""
		$avData[52] [1] = ""
		$avData[53] [1] = ""
		$avData[54] [1] = ""
		$avData[55] [1] = ""
		$avData[56] [1] = ""
		$avData[57] [1] = ""
		$avData[58] [1] = ""
		$avData[59] [1] = ""
		$avData[60] [1] = ""
		$avData[61] [1] = ""
		$avData[62] [1] = ""
		$avData[63] [1] = ""
		$avData[64] [1] = ""
		$avData[65] [1] = ""

	$n_Feedback = _AccessRecordEdit ($o_DataBase, "tblContacts", $avData, 8)	;# manual 1-8 for demo database
	if $n_Feedback <> 1 Then
			MsgBox (0, "Info. feedback", "Error in writting Data")
			Return
	EndIf

;---------- Close Database

	_AccessClose($o_DataBase)

EndFunc

; *******************************************************
; 11 - _AccessErrCode and _AccessErrMsg
; *******************************************************
Func Example_ErrorCode_Msg()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $h_Query

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	Local $feedback =_AccessActionQuery ($o_DataBase, "INSERT-INTO tblTable1 ( fld1, fld2 ) values ('Try add rec', 5)"  )

	if $feedback =0 Then
		MsgBox (0,0, _AccessErrCode() & " " & _AccessErrMsg ())
	Else
		MsgBox (16,"Info",	"Done without Errors")
	EndIf

	_AccessClose($o_DataBase)
EndFunc


; *******************************************************
; 12 - _AccessErrCode and _AccessErrMsg
; *******************************************************
Func Example_AccessActionQuery()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $h_Query

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	Local $feedback =_AccessActionQuery ($o_DataBase, "INSERT INTO tblTable1 ( fld1, fld2 ) values ('Try add rec', 5)"  )
	if $feedback = 0 Then
		MsgBox (0,0, _AccessErrCode() & " " & _AccessErrMsg ())
	Else
		MsgBox (16,"Info",	"Done without Errors")
	EndIf

	_AccessClose($o_DataBase)
EndFunc


; *******************************************************
; 13 -_AccessSelectQuery _AccessFetchData
; *******************************************************
Func Example_AccessSelectQueryFetch ()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $h_Query, $aRow [1]

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	;Local $feedback =_AccessSelectQuery ($o_DataBase, "Select * From  tblTable1  where  fld1=3"  )
	Local $feedback =_AccessSelectQuery ($o_DataBase, "Select * From  tblContacts", $h_Query )

	if $feedback = 0 Then
		MsgBox (0,0, _AccessErrCode() & " " & _AccessErrMsg ())
		_AccessClose($o_DataBase)
		Return
	EndIf

	while _AccessFetchData ( $h_Query, $aRow) = 1
		_ArrayDisplay ($aRow)
		If _AccessRecordMove($h_Query, 1) = 0 Then ExitLoop
	WEnd
	MsgBox (0,"Info", "All Rec. in Query is read" & @LF & _AccessErrCode() & " " & _AccessErrMsg () )

	_AccessClose($o_DataBase)
EndFunc


; *******************************************************
; 14 -_AccessRecordMove
; *******************************************************
Func Example_AccessRecordMove ()
	Local $o_DataBase = _AccessOpen(@ScriptDir & "\testdb.mdb")
	Local $h_Query, $aRow [1]

	If $o_DataBase = 0 Then
		MsgBox(0, "Information", "Database file is not found :-" & @CR & @ScriptDir & "\testdb.mdb")
		Return
	EndIf

	;Local $feedback =_AccessSelectQuery ($o_DataBase, "Select * From  tblTable1  where  fld1=3"  )
	Local $feedback =_AccessSelectQuery ($o_DataBase, "Select * From  tblContacts", $h_Query )

	if $feedback = 0 Then
		MsgBox (0,0, _AccessErrCode() & " " & _AccessErrMsg ())
		_AccessClose($o_DataBase)
		Return
	EndIf

	;---------
	$feedback = _AccessRecordMove($h_Query, 3) ; move 3 rec. forward.
	if $feedback <> 1 Then
		MsgBox (0,0, _AccessErrCode() & " " & _AccessErrMsg ())
		_AccessClose($o_DataBase)
		Return
	EndIf

	_AccessFetchData ( $h_Query, $aRow)
	_AccessFetchData ( $h_Query, $aRow)
	_ArrayDisplay ($aRow)

	_AccessRecordMove($h_Query, -1) ; move to Back .
	_AccessFetchData ( $h_Query, $aRow)
	_ArrayDisplay ($aRow)


	_AccessRecordMove($h_Query, 0) ; move to First Rec.
	_AccessFetchData ( $h_Query, $aRow)
	_ArrayDisplay ($aRow)


	_AccessRecordMove($h_Query, 0, False) ; move to Last Rec.
	_AccessFetchData ( $h_Query, $aRow)
	_ArrayDisplay ($aRow)

	_AccessClose($o_DataBase)
EndFunc