By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,293 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,293 IT Pros & Developers. It's quick & easy.

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

P: 31
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
Sep 2 '07 #1
Share this Question
Share on Google+
11 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
Sep 2 '07 #2

P: 31
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:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
Set rs = Db.OpenRecordset("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
Sep 3 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
Set rs = Db.OpenRecordset("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:
Expand|Select|Wrap|Line Numbers
  1. Sub EDIT_UPATE()
  2. Dim Path As String
  3. Dim rs As DAO.Recordset
  4. Dim AccountId As String
  5.  
  6. Path = "C:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
  7. Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=False)
  8. Set rs = Db.OpenRecordset("Accounts", DbOpenDynaset)
  9.  
  10.  
  11. If rs.RecordCount <> 0 Then
  12.               rs.FindFirst "AccountId = 2235" ' DAO only
  13.               If Not rs.NoMatch Then
  14.                   rs.Edit ' DAO only
  15.                   rs!Amount = 200
  16.                   rs.Update
  17.              Else
  18.                   MsgBox "Record Not Found"
  19.              End IfEnd If
  20. rs.Close
  21. Set Db = Nothing
  22. Set rs = Nothing
  23.  
  24. EndSub
Sep 3 '07 #4

P: 31
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.FindFirst "AccountId = "????" )

Regards,
Ananth


I am not sure what this has to do with your original question, but see below:
Expand|Select|Wrap|Line Numbers
  1. Sub EDIT_UPATE()
  2. Dim Path As String
  3. Dim rs As DAO.Recordset
  4. Dim AccountId As String
  5.  
  6. Path = "C:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
  7. Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=False)
  8. Set rs = Db.OpenRecordset("Accounts", DbOpenDynaset)
  9.  
  10.  
  11. If rs.RecordCount <> 0 Then
  12.               rs.FindFirst "AccountId = 2235" ' DAO only
  13.               If Not rs.NoMatch Then
  14.                   rs.Edit ' DAO only
  15.                   rs!Amount = 200
  16.                   rs.Update
  17.              Else
  18.                   MsgBox "Record Not Found"
  19.              End If
End If
  • rs.Close
  • Set Db = Nothing
  • Set rs = Nothing
  •  
  • EndSub
  • Sep 4 '07 #5

    puppydogbuddy
    Expert 100+
    P: 1,923
    There was a lot more to be done to give youwhat you are looking for. see below:
    Expand|Select|Wrap|Line Numbers
    1. Sub EDIT_UPATE()
    2. Dim xl As Object
    3. Dim xlSht As Object
    4. Dim xlWrkBk As Object
    5. Dim xlFilePath As String
    6. Dim xlFile As String
    7.  
    8. Dim dbFilePath As String
    9. Dim rs As DAO.Recordset
    10. Dim AccountId As String
    11. Dim Amount As Currency
    12.  
    13. XlFile = “ xxxx.xls”
    14. xlFilePath =  “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”
    15. dbFilePath = "C:\Documents and Settings\Jaganmohan\Desktop\db1.mdb"
    16.  
    17. ‘open excel worksheet object
    18. Set xl = CreateObject("Excel.Application")
    19. Set xlWrkBk = GetObject(xlFile)
    20. Set xlSht = xlWrkBk.Worksheets(1)
    21.  
    22. ‘open access db table object
    23. Set Db = Workspaces(0).OpenDatabase(dbFilePath, ReadOnly:=False)
    24. Set rs = Db.OpenRecordset("Accounts", DbOpenDynaset)
    25.  
    26. ‘loop until no more cells to copy to table 
    27. If rs.RecordCount <> 0 Then
       rs.MoveFirst
    28.        Do Until. rs.EOF = True
    29.        rs.AddNew
    30.             rs.Fields("AccountId") = xlSht.cells(2, "F")
    31.             rs.Fields("Amount") = Nz(xlsSht.cells(2, "C"),0)         
    32.       rs.Update
    33.       rs.MoveNext     Loop
    34. End If
    35.  
    36. 'Closing excel
    37.  xlWrkBk.Application.Quit
    38. Set xl = Nothing
    39.  
    40. 'test db table by itself
    41.  rs.FindFirst "AccountId = 2235" ' DAO only
    42.               If Not rs.NoMatch Then
    43.                   rs.Edit ' DAO only
    44.                   rs!Amount = 200
    45.                   rs.Update
    46.              Else
    47.                   MsgBox "Record Not Found"
    48.              End If
    49. End If
    50.  
    51. 'Closing recordset
    52. rs.Close
    53. Set Db = Nothing
    54. Set rs = Nothing
    55.  
    56. End Sub
    Sep 4 '07 #6

    P: 31
    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_AfterUpdate()
    Dim Path As String
    Dim rs As DAO.Recordset
    Dim AccountId As String

    Path = "C:\db1.mdb"
    Set DB = Workspaces(0).OpenDatabase(Path, ReadOnly:=False)
    Set rs = DB.OpenRecordset("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
    Oct 31 '07 #7

    P: 31
    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_AfterUpdate()
    Dim Path As String
    Dim rs As DAO.Recordset
    Dim AccountId As String

    Path = "C:\db1.mdb"
    Set DB = Workspaces(0).OpenDatabase(Path, ReadOnly:=False)
    Set rs = DB.OpenRecordset("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
    Oct 31 '07 #8

    FishVal
    Expert 2.5K+
    P: 2,653
    Hi, there.

    String constants should be enclosed with single quotes.
    rs.FindFirst "AccountId = '" & Me.File_No & "'"
    Oct 31 '07 #9

    MMcCarthy
    Expert Mod 10K+
    P: 14,534
    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
    Oct 31 '07 #10

    P: 31
    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
    Oct 31 '07 #11

    FishVal
    Expert 2.5K+
    P: 2,653
    Glad you've got it working, Ananth.

    Best regards,
    Fish
    Oct 31 '07 #12

    Post your reply

    Sign in to post your reply or Sign up for a free account.