472,146 Members | 1,288 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

home > topics > microsoft access / vba > questions > error: m$ jet database engine does not recognize 'aa0023' as a valid field name

Join Bytes to post your question to a community of 472,146 software developers and data experts.

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

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
11 2849
puppydogbuddy
1,923 Expert 1GB
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
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
1,923 Expert 1GB
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
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
    1,923 Expert 1GB
    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
    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
    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
    2,653 Expert 2GB
    Hi, there.

    String constants should be enclosed with single quotes.
    rs.FindFirst "AccountId = '" & Me.File_No & "'"
    Oct 31 '07 #9
    MMcCarthy
    14,534 Expert Mod 8TB
    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
    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
    2,653 Expert 2GB
    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.

    Similar topics

    PHP
    Error log empty
    1 post views Thread by Wayno | last post: by
    Crosstab query error 3070
    reply views Thread by carl_birchall | last post: by
    openGauss security policy
    reply views Thread by leo001 | last post: by

    By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

    To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

    BYTES.COM © 2023
    About Bytes
    Advertise
    Terms Of Use
    Privacy Policy
    Contact Us
    Sitemap

    Follow us! Get the Latest Bytes Updates