Jump to content

Microsoft Access database

wolf9228

By wolf9228
in AutoIt Example Scripts

 Share

Recommended Posts

wolf9228 Universalist

wolf9228

Posted
Posted (edited)

ODBC is an API that uses Structured Query Language (SQL) as the database access language. You can
access a wide variety of database management systems (DBMSs) with the same ODBC source code that
is directly incorporated into an application's source code. With the Microsoft ODBC Desktop Database
Drivers, a user of an ODBC-enabled application can open, query, and update a desktop database through
the ODBC interface.

The Microsoft ODBC Desktop Database Drivers are a Microsoft Jet-based set of ODBC drivers. Whereas
Microsoft ODBC Desktop Database Drivers 2.0 include both 16-bit and 32-bit drivers, versions 3.0
and later include only 32-bit drivers that work on Windows 95 or later, Windows NT Workstation or
Server version 4.0, Windows 2000 Professional, or Windows 2000 Server. These drivers provide access
to the following types of data sources:
Microsoft Access
Microsoft Excel
Paradox
dBASE
Text

Microsoft Access Driver Programming Consideration
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710302%28v=vs.85%29.aspx

Microsoft Access Data Types
http://msdn.microsoft.com/en-us/library/windows/desktop/ms714540%28v=vs.85%29.aspx

Microsoft Access Data Types
http://www.w3schools.com/sql/sql_datatypes.asp


MS_AccessDB.au3

#Include <WinAPI.au3>

Global $odbc32 = DllOpen("odbc32.dll") , $StructToString = False , $AUTOCOMMIT = True


Func MS_AccessDatabaseCreate($DbFileName = "MSDataBase.mdb",$DshWnd = 0,$OverExFile = False)
; $OverExFile ==> overwrite existing file
Local $ODBC_ADD_SYS_DSN = 4
Local $lpszDriver = "Microsoft Access Driver (*.MDB)" & Chr(0)
Local $lpszAttributes = "CREATE_DB=" & $DbFileName & " General" & Chr(0)
if Not($DshWnd) Then $DshWnd = _WinAPI_GetDesktopWindow()

if ($OverExFile) And (FileExists($DbFileName)) Then
if Not(FileDelete($DbFileName)) Then Return SetError(1,0,False)
EndIf

Local $Return = DllCall("odbccp32.dll","BOOL","SQLConfigDataSource","HWND",$DshWnd , _
"WORD",$ODBC_ADD_SYS_DSN,"str",$lpszDriver,"str",$lpszAttributes)
if @error Or Not($Return[0]) Then Return SetError(2,0,False)

Return True

EndFunc

Func MS_AccessDatabaseConnect($DbFileName = "MSDataBase.mdb",$UserID = "",$PassWord = "",$DshWnd = 0)

Local $SQL_HANDLE_ENV = 1  , $SQL_NULL_HANDLE = 0 , $SQL_ATTR_ODBC_VERSION = 200
Local $SQL_OV_ODBC3 = 3,$Return = 0,$SQL_HANDLE_DBC = 2,$SQL_LOGIN_TIMEOUT = 103
Local $SQL_SUCCESS = 0  , $SQL_SUCCESS_WITH_INFO = 1 ,  $SQL_DRIVER_NOPROMPT = 0
Local $TIMEOUT = 5 , $hDatabase[2], $SQL_AUTOCOMMIT = 102,$SQL_AUTOCOMMIT_ON = 1
Local $SQL_IS_INTEGER = -6

Local $InConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & $DbFileName
if StringLen($UserID) Then $InConnectionString &= ";UID=" & $UserID
if StringLen($PassWord) Then $InConnectionString &= ";PWD=" & $PassWord
if Not($DshWnd) Then $DshWnd = _WinAPI_GetDesktopWindow()

Local $Return = DllCall($odbc32,"short","SQLAllocHandle", _
"short",$SQL_HANDLE_ENV,"ptr",$SQL_NULL_HANDLE,"ptr*",0)
if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then
Return SetError(1,0,0)
EndIf
Local $henv = $Return[3]

$Return = DllCall("odbc32.dll","short","SQLSetEnvAttr","ptr",$henv, _
"long",$SQL_ATTR_ODBC_VERSION,"long",$SQL_OV_ODBC3,"long",0)
if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv)
Return SetError(2,0,0)
EndIf

$Return = DllCall($odbc32,"short","SQLAllocHandle","short",$SQL_HANDLE_DBC,"ptr",$henv,"ptr*",0)
if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv)
Return SetError(3,0,0)
EndIf
Local $hdbc = $Return[3]

$Return = DllCall($odbc32,"short","SQLSetConnectAttr","ptr", _
$hdbc,"long",$SQL_LOGIN_TIMEOUT,"long",$TIMEOUT,"long",$SQL_IS_INTEGER)
if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then
DllCall($odbc32,"short","SQLDisconnect","ptr",$hdbc)
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_DBC,"ptr",$hdbc)
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv)
Return SetError(4,0,0)
EndIf

if ($AUTOCOMMIT) Then
$Return = DllCall($odbc32,"short","SQLSetConnectAttr","ptr", _
$hdbc,"long",$SQL_AUTOCOMMIT,"long",$SQL_AUTOCOMMIT_ON,"long",$SQL_IS_INTEGER)
if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then
DllCall($odbc32,"short","SQLDisconnect","ptr",$hdbc)
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_DBC,"ptr",$hdbc)
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv)
Return SetError(5,0,0)
EndIf
EndIf

$Return = DllCall($odbc32,"short","SQLDriverConnect", _
"ptr",$hdbc ,"HWND",$DshWnd,"str",$InConnectionString, _
"short",-3,"ptr",0,"short",0,"short*",0,"short",$SQL_DRIVER_NOPROMPT)
if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then
DllCall($odbc32,"short","SQLDisconnect","ptr",$hdbc)
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_DBC,"ptr",$hdbc)
DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv)
Return SetError(6,0,0)
EndIf

$hDatabase = DllStructCreate("ptr hdbc;ptr henv")
DllStructSetData($hDatabase,"hdbc",$hdbc)
DllStructSetData($hDatabase,"henv",$henv)
Return $hDatabase

EndFunc

Func MS_AccessDatabaseDisconnect($hDatabase)
Local $SQL_HANDLE_ENV = 1,$SQL_HANDLE_DBC = 2
Local $hdbc = DllStructGetData($hDatabase,"hdbc")
Local $henv = DllStructGetData($hDatabase,"henv")
SQLDisconnect($hdbc)
if @error Then Return SetError(1,0,False)
SQLFreeHandle($SQL_HANDLE_DBC,$hdbc)
if @error Then Return SetError(2,0,False)
SQLFreeHandle($SQL_HANDLE_ENV,$henv)
if @error Then Return SetError(3,0,False)
Return True
EndFunc

Func CreateTable($hDatabase,$TableName,$ColumnsListNameTypeSize)
Local $SQL_HANDLE_STMT = 3
Local $StatementText = "CREATE TABLE " & $TableName & " (" & $ColumnsListNameTypeSize & ")"

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,False)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func InsertInToTable($hDatabase,$TableName,$ColumnsList,$ValueList)
Local $SQL_HANDLE_STMT = 3
Local $StatementText = "INSERT INTO " & $TableName & " (" & $ColumnsList & _
") VALUES(" & $ValueList & ")"

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,False)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func ColumnsAdd($hDatabase,$TableName,$ColumnsListNameTypeSize)
Local $SQL_HANDLE_STMT = 3
Local $StatementText = "ALTER TABLE " & $TableName & _
" ADD " & $ColumnsListNameTypeSize

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,False)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func UpdateTable($hDatabase,$TableList,$ColumnsList,$OperationsList)
Local $SQL_HANDLE_STMT = 3
Local $StatementText = "UPDATE " & $TableList & " SET " & _
$ColumnsList & " WHERE " & $OperationsList

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,False)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func DeleteFromTable($hDatabase,$TableName,$OperationsList)
Local $SQL_HANDLE_STMT = 3
Local $StatementText = "DELETE FROM " & _
$TableName & " WHERE " & $OperationsList

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,False)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func TableRemove($hDatabase,$TableName)
Local $SQL_HANDLE_STMT = 3
Local $StatementText = "DROP TABLE " & $TableName

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,False)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func SelectRecordsGroup($hDatabase,$TableName,$ColumnsList = "",$OperationList = "")
;$ColumnsList Can be empty string ""
;$OperationList Can be empty string ""
Local $SQL_HANDLE_STMT = 3 , $SQL_Error = -1 , $SQL_INTEGER = 4
Local $CountStatText  = "SELECT COUNT(*) FROM " & $TableName
Local $StatementText = "SELECT "
if StringLen($ColumnsList) Then
$StatementText &= $ColumnsList & " FROM " & $TableName
Else
$StatementText &= "* FROM " & $TableName
EndIf
if StringLen($OperationList) Then
$StatementText &= " WHERE " & $OperationList
$CountStatText &= " WHERE " & $OperationList
EndIf

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(1,0,0)

SQLExecDirect($hStatement,$CountStatText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(2,0,0)
EndIf

SQLFetch($hStatement)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(3,0,0)
EndIf

Local $DataStruct = DllStructCreate("INT") , $Col_Num = 1
SQLGetData($hStatement,$Col_Num,$SQL_INTEGER,$DataStruct)
if @error Or @extended = $SQL_Error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(4,0,0)
EndIf
Local $RowsCount = DllStructGetData($DataStruct,1)
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)

$hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(5,0,0)

SQLExecDirect($hStatement,$StatementText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(6,0,0)
EndIf

Local $ColsCount = SQLGetColsCount($hStatement)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(7,0,0)
EndIf

Local $ihStatement = 0, $tagSt = ""
$tagSt = "PTR HST;INT RSC;INT CSC;WCHAR STT[" & (StringLen($StatementText) + 1) & "]"
$ihStatement = DllStructCreate($tagSt)
DllStructSetData($ihStatement,"HST",$hStatement)
DllStructSetData($ihStatement,"RSC",$RowsCount)
DllStructSetData($ihStatement,"CSC",$ColsCount)
DllStructSetData($ihStatement,"STT",$StatementText)
Return $ihStatement

EndFunc

Func GetRecordsData(ByRef $hStatement,$hStatementFree = True)

Local $SQL_TYPE_DATE = 91 , $SQL_TYPE_TIME = 92, $SQL_TYPE_TIMESTAMP = 93
Local $SQL_BIT = -7,$SQL_VARCHAR = 12,$SQL_LONGVARCHAR = -1,$SQL_CHAR = 1
Local $SQL_VARBINARY = -3 ,$SQL_REAL = 7,$SQL_GUID = -11,$SQL_INTEGER = 4
Local $SQL_TINYINT = -6,$SQL_DOUBLE = 8 ,$Row_Num = 4 , $SQL_SMALLINT = 5
Local $SQL_LONGVARBINARY = -4  , $SQL_NUMERIC = 2  , $SQL_HANDLE_STMT = 3
Local $DatType , $ColSize , $ColName , $SQL_BINARY = -2 , $SQL_Error = -1

Local $HandleSta = DllStructGetData($hStatement,"HST")
Local $RowsCount = DllStructGetData($hStatement,"RSC")
Local $ColsCount = DllStructGetData($hStatement,"CSC")
Local $StateText = DllStructGetData($hStatement,"STT")
if Not($RowsCount) Or Not($ColsCount) Then Return SetError(1,0,0)

Local $ReturnArray[$RowsCount + 3][$ColsCount]
While(SQLFetch($HandleSta))
For $Col_Num = 1 To $ColsCount
Local $DescribeArray = SQLDescribeCol($HandleSta,$Col_Num)
if @error Then Return SetError(2,0,0)
$ColName = $DescribeArray[0]
$ColSize = $DescribeArray[1]
$DatType = $DescribeArray[2]
if ($Row_Num = 4) Then
$ReturnArray[0][$Col_Num - 1] = $ColName
$ReturnArray[1][$Col_Num - 1] = $ColSize
$ReturnArray[2][$Col_Num - 1] = $DatType
EndIf
Local $VALUES = "",$GetDataerror = 0
Switch $DatType
Case $SQL_CHAR , $SQL_VARCHAR , $SQL_LONGVARCHAR
Local $GetSizeStruct = DllStructCreate("CHAR[1]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$GetSizeStruct,True)
if @error Then Return SetError(3,0,0)
if @extended <> $SQL_Error Then
Local $Length = @extended
Local $DataStruct = DllStructCreate("CHAR[" & ($Length + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
$VALUES = DllStructGetData($DataStruct,1)
Else
$VALUES = "" ;NULL VALUE
EndIf
Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY
Local $GetSizeStruct = DllStructCreate("BYTE[1]")
SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,$GetSizeStruct,True)
if @error Then Return SetError(4,0,0)
if @extended <> $SQL_Error Then
Local $Length = @extended
Local $DataStruct = DllStructCreate("BYTE[" & $Length & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,$DataStruct)
$VALUES = $DataStruct
Else
$VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TYPE_DATE
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(5,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
Local $DataStruct = DllStructCreate("SHORT year;SHORT month;SHORT day")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_DATE,$DataStruct)
if @error Then Return SetError(6,0,0)
if (@extended = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TYPE_TIME
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(7,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
$DataStruct = DllStructCreate("SHORT hour;SHORT minute;SHORT second")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_TIME,$DataStruct)
if @error Then Return SetError(8,0,0)
if (@extended = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TYPE_TIMESTAMP
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(9,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
Local $DataStruct = DllStructCreate("SHORT year;SHORT month;" & _
"SHORT day;SHORT hour;SHORT minute;SHORT second")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_TIMESTAMP,$DataStruct)
if @error Then Return SetError(10,0,0)
if (@extended = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_GUID
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(11,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
Local $DataStruct = DllStructCreate("ulong Data1;" & _
"ushort Data2;ushort Data3;byte Data4[8]")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_GUID,$DataStruct)
if @error Then Return SetError(12,0,0)
if (@extended = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TINYINT , $SQL_BIT
Local $DataStruct = DllStructCreate("BYTE")
SQLGetData($HandleSta,$Col_Num,$SQL_TINYINT,$DataStruct)
if @error Then Return SetError(13,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_INTEGER
Local $DataStruct = DllStructCreate("INT")
SQLGetData($HandleSta,$Col_Num,$SQL_INTEGER,$DataStruct)
if @error Then Return SetError(14,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_REAL
Local $DataStruct = DllStructCreate("float")
SQLGetData($HandleSta,$Col_Num,$SQL_REAL,$DataStruct)
if @error Then Return SetError(15,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_DOUBLE
Local $DataStruct = DllStructCreate("DOUBLE")
SQLGetData($HandleSta,$Col_Num,$SQL_DOUBLE,$DataStruct)
if @error Then Return SetError(16,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_NUMERIC
Local $DataStruct = DllStructCreate("BYTE[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_NUMERIC,$DataStruct)
if @error Then Return SetError(17,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_SMALLINT
Local $DataStruct = DllStructCreate("SHORT")
SQLGetData($HandleSta,$Col_Num,$SQL_SMALLINT,$DataStruct)
if @error Then Return SetError(18,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case Else
Return SetError(19,0,0)
EndSwitch
$ReturnArray[$Row_Num - 1][$Col_Num - 1] = $VALUES
Next
$Row_Num += 1
WEnd

if ($hStatementFree) Then
SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta)
$hStatement = 0
Else
SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0
SQLExecDirect($HandleSta,$StateText)
EndIf

;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type

Return $ReturnArray
EndFunc

Func EnumRecordsData(ByRef $hStatement,$EnumRecDataProc,$hStatementFree = True)

;---------------------EnumRecDataProc----------------------------------------------------------
;Func EnumRecDataProc($IsNullValue,$Value,$ColName,$ColFormat,$ColMaxSize,$RowNum,$ColNum)
;return
;True ;Continue
;False ; Stop
;
;return true;
;EndFunc
;---------------------EnumRecDataProc----------------------------------------------------------

Local $SQL_TYPE_DATE = 91 , $SQL_TYPE_TIME = 92, $SQL_TYPE_TIMESTAMP = 93
Local $SQL_BIT = -7,$SQL_VARCHAR = 12,$SQL_LONGVARCHAR = -1,$SQL_CHAR = 1
Local $SQL_VARBINARY = -3 ,$SQL_REAL = 7,$SQL_GUID = -11,$SQL_INTEGER = 4
Local $SQL_TINYINT = -6,$SQL_DOUBLE = 8 ,$Row_Num = 4 , $SQL_SMALLINT = 5
Local $SQL_LONGVARBINARY = -4  , $SQL_NUMERIC = 2  , $SQL_HANDLE_STMT = 3
Local $DatType , $ColSize , $ColName , $SQL_BINARY = -2 , $SQL_Error = -1

Local $iReg = DllCallbackRegister($EnumRecDataProc,"int","int;int;int;int;int;int;int")
if @error Then Return SetError(1,0,False)
DllCallbackFree($iReg)

Local $HandleSta = DllStructGetData($hStatement,"HST")
Local $RowsCount = DllStructGetData($hStatement,"RSC")
Local $ColsCount = DllStructGetData($hStatement,"CSC")
Local $StateText = DllStructGetData($hStatement,"STT")
if Not($RowsCount) Or Not($ColsCount) Then Return SetError(2,0,False)

While(SQLFetch($HandleSta))
For $Col_Num = 1 To $ColsCount
Local $DescribeArray = SQLDescribeCol($HandleSta,$Col_Num)
if @error Then Return SetError(3,0,False)
$ColName = $DescribeArray[0]
$ColSize = $DescribeArray[1]
$DatType = $DescribeArray[2]
Local $VALUES = "",$GetDataerror = 0
Switch $DatType
Case $SQL_CHAR , $SQL_VARCHAR , $SQL_LONGVARCHAR
Local $GetSizeStruct = DllStructCreate("CHAR[1]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$GetSizeStruct,True)
if @error Then Return SetError(4,0,False)
$GetDataerror = @extended
if $GetDataerror <> $SQL_Error Then
Local $Length = $GetDataerror
Local $DataStruct = DllStructCreate("CHAR[" & ($Length + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
$VALUES = DllStructGetData($DataStruct,1)
Else
$VALUES = "" ;NULL VALUE
EndIf
Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY
Local $GetSizeStruct = DllStructCreate("BYTE[1]")
SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,$GetSizeStruct,True)
if @error Then Return SetError(5,0,False)
$GetDataerror = @extended
if $GetDataerror <> $SQL_Error Then
Local $Length = $GetDataerror
Local $DataStruct = DllStructCreate("BYTE[" & $Length & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,$DataStruct)
$VALUES = $DataStruct
Else
$VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TYPE_DATE
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(6,0,0)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
Local $DataStruct = DllStructCreate("SHORT year;SHORT month;SHORT day")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_DATE,$DataStruct)
if @error Then Return SetError(7,0,False)
$GetDataerror = @extended
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TYPE_TIME
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(8,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
$DataStruct = DllStructCreate("SHORT hour;SHORT minute;SHORT second")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_TIME,$DataStruct)
if @error Then Return SetError(9,0,False)
$GetDataerror = @extended
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TYPE_TIMESTAMP
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(10,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
Local $DataStruct = DllStructCreate("SHORT year;SHORT month;" & _
"SHORT day;SHORT hour;SHORT minute;SHORT second")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_TIMESTAMP,$DataStruct)
if @error Then Return SetError(11,0,False)
$GetDataerror = @extended
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_GUID
if ($StructToString) Then
Local $DataStruct = DllStructCreate("CHAR[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$DataStruct)
if @error Then Return SetError(12,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Else
Local $DataStruct = DllStructCreate("ulong Data1;" & _
"ushort Data2;ushort Data3;byte Data4[8]")
$VALUES = $DataStruct
SQLGetData($HandleSta,$Col_Num,$SQL_GUID,$DataStruct)
if @error Then Return SetError(13,0,False)
$GetDataerror = @extended
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
EndIf
Case $SQL_TINYINT , $SQL_BIT
Local $DataStruct = DllStructCreate("BYTE")
SQLGetData($HandleSta,$Col_Num,$SQL_TINYINT,$DataStruct)
if @error Then Return SetError(14,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_INTEGER
Local $DataStruct = DllStructCreate("INT")
SQLGetData($HandleSta,$Col_Num,$SQL_INTEGER,$DataStruct)
if @error Then Return SetError(15,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_REAL
Local $DataStruct = DllStructCreate("float")
SQLGetData($HandleSta,$Col_Num,$SQL_REAL,$DataStruct)
if @error Then Return SetError(16,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_DOUBLE
Local $DataStruct = DllStructCreate("DOUBLE")
SQLGetData($HandleSta,$Col_Num,$SQL_DOUBLE,$DataStruct)
if @error Then Return SetError(17,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_NUMERIC
Local $DataStruct = DllStructCreate("BYTE[" & ($ColSize + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_NUMERIC,$DataStruct)
if @error Then Return SetError(18,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case $SQL_SMALLINT
Local $DataStruct = DllStructCreate("SHORT")
SQLGetData($HandleSta,$Col_Num,$SQL_SMALLINT,$DataStruct)
if @error Then Return SetError(19,0,False)
$GetDataerror = @extended
$VALUES = DllStructGetData($DataStruct,1)
if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE
Case Else
Return SetError(20,0,False)
EndSwitch
Local $IsNullValue = ($GetDataerror == $SQL_Error)
Local $Return = Call($EnumRecDataProc,$IsNullValue , _
$VALUES,$ColName,$DatType,$ColSize,$Row_Num - 3,$Col_Num)
if Not($Return) Then
if ($hStatementFree) Then
SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta)
$hStatement = 0
Else
SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0
SQLExecDirect($HandleSta,$StateText)
EndIf
Return True
EndIf
Next
$Row_Num += 1
WEnd

if ($hStatementFree) Then
SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta)
$hStatement = 0
Else
SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0
SQLExecDirect($HandleSta,$StateText)
EndIf

Return True

EndFunc

Func SaveStreamFile($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$FilePath)

Local $nBytes

Local $ByteStruct = GetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue)
if @error Then Return SetError(1,0,False)

Local $hFile = _WinAPI_CreateFile($FilePath,1)
if @error Then Return SetError(2,0,False)

_WinAPI_WriteFile($hFile,DllStructGetPtr($ByteStruct),DllStructGetSize($ByteStruct),$nBytes)
if @error Then Return SetError(3,0,False)
_WinAPI_CloseHandle($hFile)

Return True

EndFunc

Func SetStreamFile($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$FilePath)

Local $nBytes
Local $FileSize = FileGetSize($FilePath)
if @error Then Return SetError(1,0,False)

Local $hFile = _WinAPI_CreateFile($FilePath,2,2)
if @error Then Return SetError(2,0,False)

Local $DataStruct = DllStructCreate("BYTE[" & $FileSize & "]")
_WinAPI_ReadFile($hFile,DllStructGetPtr($DataStruct),$FileSize,$nBytes)
if @error Then Return SetError(3,0,False)
_WinAPI_CloseHandle($hFile)

SetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$DataStruct)
if @error Then Return SetError(4,0,False)

Return True

EndFunc

Func GetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue)

Local $OpList = $PrimaryKeyName & " = " & $PrimaryKeyValue  ,  $nBytes
Local $SQL_BINARY = -2 , $SQL_VARBINARY = -3 , $SQL_LONGVARBINARY = -4

Local $hStatement = SelectRecordsGroup($hDatabase,$TableName,$ColumnName,$OpList)
if @error Then Return SetError(1,0,0)

Local $ReturnArray = GetRecordsData($hStatement)
if @error Then Return SetError(2,0,0)

Switch $ReturnArray[2][0]
Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY
Local $ByteStruct = $ReturnArray[3][0]
Return $ByteStruct
Case Else
Return SetError(3,0,0)
EndSwitch

EndFunc

Func SetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$DataStruct)

Local $OpList = $PrimaryKeyName & " = " & $PrimaryKeyValue  ,  $nBytes  ,  $SQL_HANDLE_STMT = 3

Local $StructSize = DllStructGetSize($DataStruct)
if @error Then Return SetError(1,0,False)

Local $StructPtr = DllStructGetPtr($DataStruct)
if @error Then Return SetError(2,0,False)

Local $ByteStruct = DllStructCreate("BYTE[" & $StructSize & "]",$StructPtr)
if @error Then Return SetError(3,0,False)

Local $hStatement = GetStmtHandle($hDatabase)
if @error Then Return SetError(4,0,False)

Local $SBytes = DllStructGetData($ByteStruct,1)
Local $SText = "UPDATE " & $TableName & " SET " & $ColumnName _
& " = " & $SBytes & " WHERE " & $OpList

SQLExecDirect($hStatement,$SText)
if @error Then
SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return SetError(5,0,False)
EndIf

SQLFreeHandle($SQL_HANDLE_STMT,$hStatement)
Return True

EndFunc

Func SQLDisconnect($ConnectionHandle)

Local $Return = DllCall($odbc32,"short","SQLDisconnect","ptr",$ConnectionHandle)
if @error Or ($Return[0] <> 0) Then Return SetError(1,0,False)
Return True

EndFunc

Func SQLExecDirect($StatementHandle,$StatementText)
Local $TextLength = StringLen($StatementText)

Local $Return = DllCall($odbc32,"short","SQLExecDirect","ptr",$StatementHandle, _
"str",$StatementText,"long",$TextLength)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False)
Return True

EndFunc

Func SQLFreeStmt($StatementHandle,$Option)

Local $Return = DllCall($odbc32,"short","SQLFreeStmt","ptr",$StatementHandle,"ushort",$Option)
if @error Or ($Return[0] <> 0) Then Return SetError(1,0,False)
Return True

EndFunc

Func SQLFreeHandle($HandleType,$Handle)

Local $Return = DllCall($odbc32,"short","SQLFreeHandle","short",$HandleType,"ptr",$Handle)
if @error Or ($Return[0] <> 0) Then Return SetError(1,0,False)
Return True

EndFunc

Func GetStmtHandle($hDatabase)

Local $SQL_HANDLE_STMT = 3
Local $hdbc = DllStructGetData($hDatabase,"hdbc")

Local $Return = DllCall($odbc32,"short","SQLAllocHandle","short",$SQL_HANDLE_STMT,"ptr",$hdbc,"ptr*",0)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,0)
Local $hStatement = $Return[3]
Return $hStatement

EndFunc

Func SQLFetch($StatementHandle)

Local $Return = DllCall($odbc32,"short","SQLFetch","ptr",$StatementHandle)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False)
Return SetError(0,$Return[0],True)

EndFunc

Func SQLDescribeCol($StatementHandle,$ColumnNumber)

Local $Return = DllCall($odbc32,"short","SQLDescribeCol","ptr",$StatementHandle _
,"short",$ColumnNumber,"str*",0,"short",0,"short*",0,"short*",0,"ULONG*" _
,0,"short*",0,"short*",0)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,0)
Local $NameLengthPtr = $Return[5]

Local $ColumnNameSt = DllStructCreate("CHAR[" & ($NameLengthPtr + 1) & "]")
$Return = DllCall($odbc32,"short","SQLDescribeCol","ptr",$StatementHandle,"short", _
$ColumnNumber,"struct*",$ColumnNameSt,"short",($NameLengthPtr * 2),"ptr",0,"short*" _
,0,"ULONG*",0,"short*",0,"short*",0)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(2,0,0)

Local $DescribeArray[3]
$DescribeArray[0] = DllStructGetData($ColumnNameSt,1)
$DescribeArray[1] = $Return[7]
$DescribeArray[2] = $Return[6]
Return $DescribeArray

EndFunc

Func SQLGetData($StatementHandle,$Col_Num,$TargetType,$DataStruct,$NullSize = False)

if ($NullSize) Then
$Return = DllCall($odbc32,"short","SQLGetData","ptr",$StatementHandle _
,"USHORT",$Col_Num,"short",$TargetType,"struct*",$DataStruct,"ULONG", _
0,"ULONG*",0)
Else
$Return = DllCall($odbc32,"short","SQLGetData","ptr",$StatementHandle _
,"USHORT",$Col_Num,"short",$TargetType,"struct*",$DataStruct,"ULONG", _
DllStructGetSize($DataStruct),"ULONG*",0)
EndIf

if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False)
Return SetError(0,$Return[6],True)

EndFunc

Func SQLCOMMIT($hDatabase)

Local $hdbc = DllStructGetData($hDatabase,"hdbc") , $SQL_HANDLE_DBC = 2,$SQL_COMMIT = 0
Local $Return = DllCall($odbc32,"short","SQLEndTran","short",$SQL_HANDLE_DBC _
,"ptr",$hdbc,"short",$SQL_COMMIT)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False)
Return SetError(0,0,True)

EndFunc

Func SQLGetColsCount($StatementHandle)

Local $Return = DllCall($odbc32,"short","SQLNumResultCols","ptr",$StatementHandle,"short*",0)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,0)
Return $Return[2]

EndFunc
 

Example_ArrayData.au3

#include <Array.au3>
#include "MS_AccessDB.au3"

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1",@error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2",@error))

CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY,Name VARCHAR(100),Age INT,Country VARCHAR(100)")
if @error Then Exit(MsgBox(0,"ErrorMsg 3",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 7",@error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity")
if @error Then Exit(MsgBox(0,"ErrorMsg 8",@error))
$iReturnArray = GetRecordsData($hstmt)
if @error Then Exit(MsgBox(0,"ErrorMsg 9",@error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Name")
if @error Then Exit(MsgBox(0,"ErrorMsg 10",@error))
$iReturnArray = GetRecordsData($hstmt)
if @error Then Exit(MsgBox(0,"ErrorMsg 11",@error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Name Column")


 MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 12",@error))

Example_CreateTable.au3

#include <Array.au3>
#include "MS_AccessDB.au3"

$iFile = FileOpen("iFile.txt",2)
FileWrite($iFile,"Stream iFile")
FileClose($iFile)

$jFile = FileOpen("jFile.txt",2)
FileWrite($jFile,"Stream jFile")
FileClose($jFile)

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2"," Error Is Number  "  & @error))

CreateTable($hDatabase,"iBinary","Id INT PRIMARY KEY,Files LONGBINARY,iString LONGCHAR")
;Create iBinary Table And Three Columns Id , Files And iString
;(Id Data Type Int) , (Files Data Type LONGBINARY) , (iString Data Type LONGCHAR) , (PRIMARY KEY Is Id)
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"iBinary","Id,iString","1,'One'")
;Insert In iBinary Table (Id = 1 and iString = 'One')
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"iBinary","Id,iString","2,'Two'")
;Insert In iBinary Table (Id = 2 and iString = 'Two')
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
SetStreamFile($hDatabase,"iBinary","Files","Id","1","iFile.txt")
;SetStreamFile WHERE Id = 1
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
SetStreamFile($hDatabase,"iBinary","Files","Id","2","jFile.txt")
;SetStreamFile WHERE Id = 2

if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"iBinary")
; Select iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")

SaveStreamFile($hDatabase,"iBinary","Files","Id","1","iNewFile.txt")
;SaveStreamFile WHERE Id = 1
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))
SaveStreamFile($hDatabase,"iBinary","Files","Id","2","jNewFile.txt")
;SaveStreamFile WHERE Id = 1
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))

 MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 12"," Error Is Number  "  & @error))

Example_Date_Time.au3

#include <Array.au3>
#include "MS_AccessDB.au3"

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2"," Error Is Number  "  & @error))
$iColumns = "Id INT PRIMARY KEY,iDATE DATE,iTIME TIME,iTIMESTAMP TIMESTAMP,iGUID GUID"
CreateTable($hDatabase,"Calendar",$iColumns)
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
$ValueList = "1,{d '2012-11-22'},{t '12:30:11'},{ts '2012-11-11 12:30:01'}" & _
",'{56a868a9-0ad4-11ce-b03a-0020af0ba770}'"
; Id = 1 , iDATE = '2012-11-22' , iTIME = '12:30:01' , iTIMESTAMP = '2012-11-11 12:30:01'
InsertInToTable($hDatabase,"Calendar","Id,iDATE,iTIME,iTIMESTAMP,iGUID",$ValueList)
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))

$ValueList = "2,{d '2011-01-02'},{t '10:20:08'},{ts '2011-01-02 10:20:08'}," & _
"'{56a868b4-0ad4-11ce-b03a-0020af0ba770}'"
; Id = 2 , iDATE = '2011-01-02' , iTIME = '10:20:08' , iTIMESTAMP = '2011-01-02 10:20:08'
InsertInToTable($hDatabase,"Calendar","Id,iDATE,iTIME,iTIMESTAMP,iGUID",$ValueList)
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))

$ValueList = "3,{d '2000-12-04'},{t '01:01:01'},{ts '2000-12-04 01:01:01'}," & _
"'{56a868b1-0ad4-11ce-b03a-0020af0ba770}'"
; Id = 3 , iDATE = '2000-12-04' , iTIME = '01:01:01' , iTIMESTAMP = '2000-12-04 01:01:01'
InsertInToTable($hDatabase,"Calendar","Id,iDATE,iTIME,iTIMESTAMP,iGUID",$ValueList)
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))

 $hstmt = SelectRecordsGroup($hDatabase,"Calendar")
; Select Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))

_ArrayDisplay($iReturnArray,"Calendar Table Structs Data")
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
; if All values of the elements is a TIMESTAMP Struct
;http://msdn.microsoft.com/en-us/library/windows/desktop/ms716289%28v=vs.85%29.aspx
;In ODBC 3.x, SQL_TYPE_DATE, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP is
;returned in *DataTypePtr for date, time, or timestamp data, respectively;
;in ODBC 2.x, SQL_DATE, SQL_TIME, or SQL_TIMESTAMP is returned. The Driver
;Manager performs the required mappings when an ODBC 2.x application is
;working with an ODBC 3.x driver or when an ODBC 3.x application is working
;with an ODBC 2.x driver.
;DATE Struct // tag "SHORT year;SHORT month;SHORT day" // or NullValue
;TIME Struct // tag "SHORT hour;SHORT minute;SHORT second" // or NullValue
;TIMESTAMP Struct // tag "SHORT year;SHORT month;SHORT day;SHORT hour;SHORT minute;SHORT second"

$StructToString = True
 $hstmt = SelectRecordsGroup($hDatabase,"Calendar")
; Select Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))

_ArrayDisplay($iReturnArray,"Calendar Table Staings Data")
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
; if All values of the elements is a TIMESTAMP Struct
;http://msdn.microsoft.com/en-us/library/windows/desktop/ms716289%28v=vs.85%29.aspx
;In ODBC 3.x, SQL_TYPE_DATE, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP is
;returned in *DataTypePtr for date, time, or timestamp data, respectively;
;in ODBC 2.x, SQL_DATE, SQL_TIME, or SQL_TIMESTAMP is returned. The Driver
;Manager performs the required mappings when an ODBC 2.x application is
;working with an ODBC 3.x driver or when an ODBC 3.x application is working
;with an ODBC 2.x driver.
;DATE Struct // tag "SHORT year;SHORT month;SHORT day" // or NullValue
;TIME Struct // tag "SHORT hour;SHORT minute;SHORT second" // or NullValue
;TIMESTAMP Struct // tag "SHORT year;SHORT month;SHORT day;SHORT hour;SHORT minute;SHORT second"

 MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))

Example_Delete_Add.au3

#include <Array.au3>
#include "MS_AccessDB.au3"

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2"," Error Is Number  "  & @error))

CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")


DeleteFromTable($hDatabase,"Identity","Id = 2 OR Id = 4") ; Delete Row In Id = 2 And Row In Id = 4
$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")
 MS_AccessDatabaseDisconnect($hDatabase) ;Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))


MS_AccessDatabaseCreate("pMSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 12"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("pMSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 13"," Error Is Number  "  & @error))


CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY")
if @error Then Exit(MsgBox(0,"ErrorMsg 14"," Error Is Number  "  & @error))
ColumnsAdd($hDatabase,"Identity","Name LONGCHAR") ; Add Name Column // Data Type CHAR Size 100
if @error Then Exit(MsgBox(0,"ErrorMsg 15"," Error Is Number  "  & @error))
ColumnsAdd($hDatabase,"Identity","Age INT") ; Add Age Column // Data Type INT
if @error Then Exit(MsgBox(0,"ErrorMsg 16"," Error Is Number  "  & @error))
ColumnsAdd($hDatabase,"Identity","Country LONGCHAR") ; Add Country Column // Data Type CHAR Size 100
if @error Then Exit(MsgBox(0,"ErrorMsg 17"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 18"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 19"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 20"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 21"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 22"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")

TableRemove($hDatabase,"Identity") ; Remove Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 23"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"Identity")
if (@error) Then
MsgBox(0," Error Is Number  "  & @error,"Error Select Identity Table")
Else
MsgBox(0," Error Is Number  "  & @error,"Select Identity Table")
EndIf

MS_AccessDatabaseDisconnect($hDatabase) ;Disconnect pMSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 24"," Error Is Number  "  & @error))

Example_EnumData.au3

#include <Array.au3>
#include "MS_AccessDB.au3"

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2"," Error Is Number  "  & @error))
CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR,NULL_COL INT")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","1,'Sword',10,'British',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","2,'Knight',20,'Egypt',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","3,'Fighter',30,'Japan',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","4,'Lightning',40,'Turkey',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt,False)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")

EnumRecordsData($hstmt,"EnumRecDataProc")
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))

MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))

Func EnumRecDataProc($IsNullValue,$Value,$ColName,$ColFormat,$ColMaxSize,$RowNum,$ColNum)

;return
;True ;Continue
;False ; Stop
;$RowNum // $ColNum : Start From One 1
;$ColFormat : Data Taype
;NullValue Is Blank ("")

Local $SQL_TYPE_DATE = 91  ,   $SQL_TYPE_TIME = 92 ,   $SQL_TYPE_TIMESTAMP = 93
Local $SQL_BIT = -7, $SQL_VARCHAR = 12, $SQL_LONGVARCHAR = -1, $SQL_BINARY = -2
Local $SQL_CHAR = 1,$SQL_VARBINARY = -3,$SQL_LONGVARBINARY = -4,$SQL_GUID = -11
Local $SQL_TINYINT = -6  ,  $SQL_INTEGER = 4  , $SQL_REAL = 7 , $SQL_DOUBLE = 8
Local $SQL_NUMERIC = 2 , $SQL_SMALLINT = 5

$MsgText = "Column Name Is " & $ColName & @CRLF
$MsgText &= "Column Max Size Is " &  $ColMaxSize & " byte" & @CRLF
$MsgText &= "Column Data Type Is " & $ColFormat & @CRLF
$MsgText &= "Column Number Is " & $ColNum & @CRLF
$MsgText &= "Row Number Is " & $RowNum & @CRLF
$MsgText &= "IsNullValue Is " & $IsNullValue & @CRLF
$MsgText &= "Value Is " & $Value & @CRLF

MsgBox(0,"EnumRecDataProc Msg",$MsgText)

Switch $ColFormat
Case $SQL_CHAR , $SQL_VARCHAR , $SQL_LONGVARCHAR
;CHAR String or NullValue // $IsNullValue
Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY
;BYTE Struct tag BYTE[$ColMaxSize] or NullValue // $IsNullValue
Case $SQL_TYPE_DATE
;DATE Struct // tag "SHORT year;SHORT month;SHORT day" // or NullValue // $IsNullValue
Case $SQL_TYPE_TIME
;TIME Struct // tag "SHORT hour;SHORT minute;SHORT second" // or NullValue // $IsNullValue
Case $SQL_TYPE_TIMESTAMP
;TIMESTAMP Struct // tag "SHORT year;SHORT month;SHORT day;SHORT hour;SHORT minute;SHORT second"
; or NullValue // $IsNullValue
Case $SQL_GUID
;GUID Struct // tag "ulong Data1;ushort Data2;ushort Data3;byte Data4[8]"
; or NullValue // $IsNullValue
Case $SQL_TINYINT , $SQL_BIT
;BYTE Number or NullValue // $IsNullValue
Case $SQL_INTEGER
;INT Number or NullValue // $IsNullValue
Case $SQL_REAL
;float Number or NullValue // $IsNullValue
Case $SQL_DOUBLE
;DOUBLE Number or NullValue // $IsNullValue
Case $SQL_NUMERIC
;BYTE Struct tag BYTE[$ColMaxSize] or NullValue // $IsNullValue
Case $SQL_SMALLINT
;SHORT Number or NullValue // $IsNullValue
EndSwitch

return True
;return
;True ;Continue
;False ; Stop

EndFunc

Example_Set_Insert.au3

#include <Array.au3>
#include "MS_AccessDB.au3"

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2"," Error Is Number  "  & @error))

Dim $SetColumnsList
CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR ,NULL_COL INT")
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"Identity")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")

MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") ; Connect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))

$SetColumnsList = "Name = 'Thunder',Age = 40,Country = 'France'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 1")
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))
$SetColumnsList = "Name = 'Spear',Age = 30,Country = 'India'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 2")
if @error Then Exit(MsgBox(0,"ErrorMsg 12"," Error Is Number  "  & @error))
$SetColumnsList = "Name = 'Flood',Age = 20,Country = 'Korea'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 3")
if @error Then Exit(MsgBox(0,"ErrorMsg 13"," Error Is Number  "  & @error))
$SetColumnsList = "Name = 'Star',Age = 10,Country = 'Russia'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 4")
if @error Then Exit(MsgBox(0,"ErrorMsg 14"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"Identity")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 15"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 16"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Identity Table")
MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 17"," Error Is Number  "  & @error))

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") ; Connect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 18"," Error Is Number  "  & @error))

CreateTable($hDatabase,"iBinary","Id INT PRIMARY KEY,File LONGBINARY,iString LONGCHAR")
;Create iBinary Table And Three Columns Id , File And iStrinig
if @error Then Exit(MsgBox(0,"ErrorMsg 19"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"iBinary","Id,File,iString","1," & SToB('One') & ",'One'")
;Insert In iBinary Table (Id = 1 ; File = SToB('One') ; iString = 'One')
if @error Then Exit(MsgBox(0,"ErrorMsg 20"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"iBinary","Id,File,iString","2," & SToB('Tow') & ",'Two'")
;Insert In iBinary Table (Id = 2 ; File = SToB('Tow') ; iString = 'Two')
if @error Then Exit(MsgBox(0,"ErrorMsg 21"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"iBinary")
; Select iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 22"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 23"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"iBinary Table")
MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 24"," Error Is Number  "  & @error))

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") ; Connect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 25"," Error Is Number  "  & @error))


$SetColumnsList = "File = " & SToB('UPDATE One') & ",iString = 'UPDATE One'"
UpdateTable($hDatabase,"iBinary",$SetColumnsList,"Id = 1")
if @error Then Exit(MsgBox(0,"ErrorMsg 26"," Error Is Number  "  & @error))
$SetColumnsList = "File = " & SToB('UPDATE Tow') & ",iString = 'UPDATE Two'"
UpdateTable($hDatabase,"iBinary",$SetColumnsList,"Id = 2")
if @error Then Exit(MsgBox(0,"ErrorMsg 27"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"iBinary")
; Select iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 28"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 29"," Error Is Number  "  & @error))
_ArrayDisplay($iReturnArray,"iBinary Table")
MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 30"," Error Is Number  "  & @error))

Func SToB($Str)
Return StringToBinary($Str)
EndFunc

New_MS_AccessDB.zip

Edited by wolf9228

صرح السماء كان هنا

 

ptrex Universalist

ptrex

Posted
Posted

@Wolf9228

Great Job ! :graduated:

Thanks for sharing

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

wolf9228 Universalist

wolf9228

Posted
  • Author
Posted (edited)

Is there anyway you can adapt this for newer ODBC? This crash for me on win7 x64 with office 2010 and office 2007 accdb file.

The functions of the project work without Microsoft Office programs

I installed Windows 8 64-bit in my computer

After running the script there were some errors

errors has been modified

Thank you :)

Edited by wolf9228

صرح السماء كان هنا

 

ptrex Universalist

ptrex

Posted
Posted

@all

Indeed ODBC is the middleware software bewteen the DB and the client. And it should not matter which client software you are using.

Thanks for fixing things.

Rgds,

ptrex

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

GrantJ Seeker

GrantJ

Posted
Posted

Thank you for this it will be very useful.

If I wanted to use this with a Pervasive SQL database for example should it just be a matter of changing the ODBC connect string and any SQL commands to the Pervasive requirement?

Thanks

Grant

  • 2 weeks later...
wolf9228 Universalist

wolf9228

Posted
  • Author
Posted

Thank you for this it will be very useful.

If I wanted to use this with a Pervasive SQL database for example should it just be a matter of changing the ODBC connect string and any SQL commands to the Pervasive requirement?

Thanks

Grant

I did not understand what you mean but you can look at the information

Drivers, which are used by functions Project this link

Microsoft-Supplied ODBC Drivers

صرح السماء كان هنا

 

  • 1 month later...
StewartWilkinson Seeker

StewartWilkinson

Posted
Posted (edited)

Hi,

I have been trying this out to create a database from a CSV file and found that there appear to be a couple of problems (on Windows 7 64 bit):

  • The $Return variable is not defined in the SQLGetData function which caused errors when I called GetRecordsData (with MustDeclareVars set).

  • I get 'subscript used with non-Array variable' for '$hDatabase' if I call 'SQLCOMMIT' using the handle returned by MS_AccessDatabaseConnect.
Is there a way to configure the PRIMARY KEY name when I create a table like:

CreateTable($hDB,'"References"',"Code VARCHAR(20) PRIMARY KEY, Description VARCHAR(50)")

The sample version of a database structure that I am trying to re-produce (with content added from a CSV) shows (in MS Access) that the index has a name the same as the column name (Code).

Stewart

Edited by StewartWilkinson
  • 1 month later...
pfurness Seeker

pfurness

Posted
Posted

Hi,

I have been using the library to create records in an access (.mdb) file. I have come to the point where I am trying to query the database.

I went back to the examples for ideas on how to implement this.

I am getting an error with both of the examples (Example_ArrayData and Example_EnumData) on the SelectRecordsGroup call of Error 4. Looking at the Function this is when an SQLGetData call is made. Has anyone got any suggestions on why this call may be failing?

Peter

  • 10 years later...
Parsix Adventurer

Parsix

Posted
Posted

hi, how to use GROUP BY after WHERE in query

i get error 6 in MsgErr8

 

my query 

SELECT tbl_info.ID, tbl_info.FN, tbl_info.LN FROM tbl_info WHERE tbl_info.LN LIKE '%JO%' GROUP BY tbl_info.LN

 

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...