Error: M$ jet database engine does not recognize 'AA0023' as a valid field name

[Tempalli]

I am using M.S.Access as backend database and able to add records from Excel but not able to Edit & Update the specific record from M.s Access with reference to cell value given in excel. Kinldy help me.

Regards,
ANANTH

[puppydogbuddy]

I am using M.S.Access as backend database and able to add records from Excel but not able to Edit & Update the specific record from M.s Access with reference to cell value given in excel. Kinldy help me.

Regards,
ANANTH

If your table and workbook are linked, see the following KB article from microsoft:

http://support.microsoft.com/kb/904953

[Tempalli]

Kindly help me on this code. I am not able to work on this.

Sub EDIT_UPATE()
Dim Path As String
Dim rs As DAO.Recordset
Dim AccountId As String

Path = "C:\Documen ts and Settings\Jaganm ohan\Desktop\db 1.mdb"
Set Db = Workspaces(0).O penDatabase(Pat h, ReadOnly:=True)
Set rs = Db.OpenRecordse t("Accounts")

rs.FindFirst "AccountId = 2235" ' DAO only

If Not rs.NoMatch Then

rs.Edit ' DAO only
rs!Amount = 200
rs.Update
Else
MsgBox "Record Not Found"
End If

End Sub

[puppydogbuddy]

Kindly help me on this code. I am not able to work on this.

Sub EDIT_UPATE()
Dim Path As String
Dim rs As DAO.Recordset
Dim AccountId As String

Path = "C:\Documen ts and Settings\Jaganm ohan\Desktop\db 1.mdb"
Set Db = Workspaces(0).O penDatabase(Pat h, ReadOnly:=True)
Set rs = Db.OpenRecordse t("Accounts")

rs.FindFirst "AccountId = 2235" ' DAO only

If Not rs.NoMatch Then

rs.Edit ' DAO only
rs!Amount = 200
rs.Update
Else
MsgBox "Record Not Found"
End If

End Sub

I am not sure what this has to do with your original question, but see below:

Sub EDIT_UPATE()
Dim Path As String
Dim rs As DAO.Recordset
Dim AccountId As String

Path = "C:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=False)
Set rs = Db.OpenRecordset("Accounts", DbOpenDynaset)


If rs.RecordCount <> 0 Then[INDENT]
              rs.FindFirst "AccountId = 2235" ' DAO only
              If Not rs.NoMatch Then
                  rs.Edit ' DAO only
                  rs!Amount = 200
                  rs.Update
             Else
                  MsgBox "Record Not Found"
             End If[/INDENT]
End If
rs.Close
Set Db = Nothing
Set rs = Nothing

EndSub

[Tempalli]

Thanks for your help in this regards, it is working fine.

I am having a AccountNo in Sheets("Sheet1" ).Range("A1") for "AccountID" ,
How can i define in this code to edit and update the record.

Kindly help me if it is possible.

(Ex.rs.FindFirs t "AccountId = "????" )

Regards,
Ananth

I am not sure what this has to do with your original question, but see below:

Sub EDIT_UPATE()
Dim Path As String
Dim rs As DAO.Recordset
Dim AccountId As String

Path = "C:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=False)
Set rs = Db.OpenRecordset("Accounts", DbOpenDynaset)


If rs.RecordCount <> 0 Then[INDENT]
              rs.FindFirst "AccountId = 2235" ' DAO only
              If Not rs.NoMatch Then
                  rs.Edit ' DAO only
                  rs!Amount = 200
                  rs.Update
             Else
                  MsgBox "Record Not Found"
             End If[/INDENT]
End If
rs.Close
Set Db = Nothing
Set rs = Nothing

EndSub

[puppydogbuddy]

There was a lot more to be done to give youwhat you are looking for. see below:

Sub EDIT_UPATE()
Dim xl As Object
Dim xlSht As Object
Dim xlWrkBk As Object
Dim xlFilePath As String
Dim xlFile As String

Dim dbFilePath As String
Dim rs As DAO.Recordset
Dim AccountId As String
Dim Amount As Currency
 
XlFile = “ xxxx.xls”
xlFilePath =  “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”
dbFilePath = "C:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"

‘open excel worksheet object
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(xlFile)
Set xlSht = xlWrkBk.Worksheets(1)

‘open access db table object
Set Db = Workspaces(0).OpenDatabase(dbFilePath, ReadOnly:=False)
Set rs = Db.OpenRecordset("Accounts", DbOpenDynaset)
 
‘loop until no more cells to copy to table 
If rs.RecordCount <> 0 Then[INDENT] rs.MoveFirst
       Do Until. rs.EOF = True
       rs.AddNew
            rs.Fields("AccountId") = xlSht.cells(2, "F")
            rs.Fields("Amount") = Nz(xlsSht.cells(2, "C"),0)         
      rs.Update
      rs.MoveNext[/INDENT]
     Loop
End If

'Closing excel
 xlWrkBk.Application.Quit
Set xl = Nothing

'test db table by itself
 rs.FindFirst "AccountId = 2235" ' DAO only
              If Not rs.NoMatch Then
                  rs.Edit ' DAO only
                  rs!Amount = 200
                  rs.Update
             Else
                  MsgBox "Record Not Found"
             End If
End If

'Closing recordset
rs.Close
Set Db = Nothing
Set rs = Nothing
 
End Sub

[Tempalli]

Kindly help me on this code where the error message shows as below.

The Microsoft jet database engine does not recognize 'AA0023' as a valid field name of expression

Regards,
Ananth

Field Name DataType
--------------------------------------------------
AccountID Text
LOCATION Text

--------------------------------------------------------------
Account ID = AA0023
LOCATION = INDIA
----------------------------------------------------------

Private Sub File_No_AfterUp date()
Dim Path As String
Dim rs As DAO.Recordset
Dim AccountId As String

Path = "C:\db1.mdb "
Set DB = Workspaces(0).O penDatabase(Pat h, ReadOnly:=False )
Set rs = DB.OpenRecordse t("Accounts", dbOpenDynaset)

If rs.RecordCount <> 0 Then
rs.FindFirst "AccountId = " & Me.File_No
If Not rs.NoMatch Then
rs.Edit ' DAO only
Me.Location = rs!Location
Else
MsgBox "Record Not Found"
End If
End If
rs.Close
Set DB = Nothing
Set rs = Nothing
End Sub

[Tempalli]

The Microsoft jet database engine does not recognize 'AA0023' as a valid field name

Kinldy help me on this code where i am receiving a error message as
--------- The Microsoft jet database engine does not recognize 'AA0023' as a valid field name of expression --------

Field Name DataType
AccountID Text
LOCATION Text

Account ID = AA0023
LOCATION = INDIA


Private Sub File_No_AfterUp date()
Dim Path As String
Dim rs As DAO.Recordset
Dim AccountId As String

Path = "C:\db1.mdb "
Set DB = Workspaces(0).O penDatabase(Pat h, ReadOnly:=False )
Set rs = DB.OpenRecordse t("Accounts", dbOpenDynaset)

If rs.RecordCount <> 0 Then
rs.FindFirst "AccountId = " & Me.File_No ' DAO only
If Not rs.NoMatch Then
rs.Edit ' DAO only
Me.Location = rs!Location
Else
MsgBox "Record Not Found"
End If
End If
rs.Close
Set DB = Nothing
Set rs = Nothing
End Sub

[FishVal]

Hi, there.

String constants should be enclosed with single quotes.
rs.FindFirst "AccountId = '" & Me.File_No & "'"

[MMcCarthy]

I have merged both posts into one. Please do not double post your question as it causes confusion among the experts when trying to answer your query.

ADMIN

[Tempalli]

Hi, there.

String constants should be enclosed with single quotes.
rs.FindFirst "AccountId = '" & Me.File_No & "'"

Thanks for your help. It is working fine.
Regards,
Ananth

[FishVal]

Glad you've got it working, Ananth.

Best regards,
Fish