Add Record to MS Access

[BryanVest]

I have a database built in MS Access 2007. I am trying to import the data from the GUI to access as a new record and cant seem to find the answer anywhere. Heres what I have.

Case $Button2
            $PalletID = GUICtrlRead($Input1)
            $Product = GUICtrlRead($Combo2)
            $Lot = GUICtrlRead($Input2)
            $Country = GUICtrlRead($Input3)
            $Expire = GUICtrlRead($Date1)
            $Quantity = GUICtrlRead($Input4)
            $dbname = "p:\Pallet Tag\Pallet Tracking_be.accdb"
            $tblname = "Naked Juice"
            $query = "INSERT INTO 'Naked Juice' (*) VALUES ('Test')"
            $adoCon = ObjCreate("ADODB.Connection")
            $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname)
            $adoRs = ObjCreate ("ADODB.Recordset")
            $adoRs.CursorType = 1
            $adoRs.LockType = 3
            $adoRs.Open ($query, $adoCon)

I get the following error. I seem like I connect to the database alright but cant seem to add a record.

Pallet Creator.au3 (65) : ==> The requested action with this object has failed.:
$adoRs.Open ($query, $adoCon)
$adoRs.Open ($query, $adoCon)^ ERROR

[water]

Add a COM error handler so you get more information about what happens.

Something like:

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

; Your code goes here

Func _ErrFunc($oError)
MsgBox(0, "COM error", "err.number is: " & @TAB & $oError.number & @CRLF & _
"err.windescription:" & @TAB & $oError.windescription & @CRLF & _
"err.description is: " & @TAB & $oError.description & @CRLF & _
"err.source is: " & @TAB & $oError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
"err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
"err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc ;==>_ErrFunc

[BryanVest]

This is where I am at now.

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
  Case $GUI_EVENT_CLOSE
   Exit  
  
   Case $Button2
   Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
; Your code goes here
Func _ErrFunc($oError)
MsgBox(0, "COM error", "err.number is: " & @TAB & $oError.number & @CRLF & _
"err.windescription:" & @TAB & $oError.windescription & @CRLF & _
"err.description is: " & @TAB & $oError.description & @CRLF & _
"err.source is: " & @TAB & $oError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
"err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
"err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc ;==>_ErrFunc
   $PalletID = GUICtrlRead($Input1)
   $Product = GUICtrlRead($Combo2)
   $Lot = GUICtrlRead($Input2)
   $Country = GUICtrlRead($Input3)
   $Expire = GUICtrlRead($Date1)
   $Quantity = GUICtrlRead($Input4)
   $dbname = "p:Pallet TagPallet Tracking_be.accdb"
   $tblname = "Naked Juice"
   $adoCon = ObjCreate("ADODB.Connection")
   $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname)
   $adoRs = ObjCreate ("ADODB.Recordset")
   $adoRs.CursorType = 3
   $sql = "INSERT INTO NJ ([PalletID], [LOTNumber], [ProductID], [ExpirationDate], [Quantity], [Customer], [CreatedTime], [CreatedDate],[Country])" & " VALUES ('" & $PalletID & "','" & $Lot & "')"
   $adoRS.Execute $sql
  EndSwitch
WEnd

The error with the handler is -2147352570 and the description is Unknown Name. The error line is

$adoRS.Execute $sql

Edited April 3, 2012 by BryanVest

[BrewManNH]

You can't have your function inside the While loop, move the Global line to before the While statement, and move the function to after the Wend statement. That's the first thing you'll need to do.

[water]

Shouldn't it be

$adoRS.Execute($sql)

[BryanVest]

Same error though even with the suggested changes.

expandcollapse popup

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
  Case $GUI_EVENT_CLOSE
   Exit  
  
   Case $Button2
  

   $PalletID = GUICtrlRead($Input1)
   $Product = GUICtrlRead($Combo2)
   $Lot = GUICtrlRead($Input2)
   $Country = GUICtrlRead($Input3)
   $Expire = GUICtrlRead($Date1)
   $Quantity = GUICtrlRead($Input4)
   $dbname = "p:Pallet TagPallet Tracking_be.accdb"
   $tblname = "NJ"
   $adoCon = ObjCreate("ADODB.Connection")
   $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname)
   $adoRs = ObjCreate ("ADODB.Recordset")
   $adoRs.CursorType = 3
   $sql = "INSERT INTO NJ ([PalletID], [LOTNumber], [ProductID], [ExpirationDate], [Quantity], [Customer], [CreatedTime], [CreatedDate],[Country])" & " VALUES ('" & $PalletID & "','" & $Lot & "')"
   $adoRS.Execute ($sql)
  EndSwitch
WEnd
; Your code goes here
Func _ErrFunc($oError)
MsgBox(0, "COM error", "err.number is: " & @TAB & $oError.number & @CRLF & _
"err.windescription:" & @TAB & $oError.windescription & @CRLF & _
"err.description is: " & @TAB & $oError.description & @CRLF & _
"err.source is: " & @TAB & $oError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
"err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
"err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc ;==>_ErrFunc

[water]

I don't think you need the [ and ].

Check this.

[BryanVest]

I don't think you need the [ and ].

Check this.

Yeah I had them without originally, but I added them when I saw the brackets on some other site. I still have the same error.

[water]

For me it sounds like you want to insert a field into table "NJ" that isn't defined in this table.

Did you check the database definition for table "NJ"?

[BryanVest]

I figured it out

Changed

$adoRS.Execute ($sql)

To

$adoCon.Execute ($sql)

and everything imported perfectly. Thank you!