accessing 1 field in a access table

rjef
rjef used Ask the Experts™
on
i used a sql command in Access vba code to add a record into a table ie INSERT INTO Results '  .  now i want to read another table that has 1 field in it and if it is a number add one to it and save it again .  if it is not a number then do not do anything with it.

your thoughts?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
This is simple to perform with VBA and DAO:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select YourField From YourTable")
' or, if some criteria:
' Set rs = CurrentDb.OpenRecordset("Select YourField From YourTable Where SomeField = " & SomeNumericValue & "")
' Set rs = CurrentDb.OpenRecordset("Select YourField From YourTable Where SomeField = '" & SomeTextValue & "'")
If IsNumeric(rs!Fields(0).Value) Then
    rs.Edit
        rs.Fields(0).Value = Val(rs.Fields(0).Value) + 1
        ' or, if the field's data type is Text:
        ' rs.Fields(0).Value = LTrim(Str(Val(rs.Fields(0).Value) + 1))
    rs.Update
End If
rs.Close

Open in new window

Top Expert 2014

Commented:
You can also do this with just a simple update statement.
Example:
dbEngine(0)(0).Execute("Update OneRowTable Set SomeField=SomeField+1 Where IsNumeric(SomeField)=True")

Open in new window

Author

Commented:
what if the table is linked?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It makes no difference.

Author

Commented:
i get an error says something about unable to update linked file using isam
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can only update linked Access or ODBC tables.
Linked Text files and Excel worksheets are not updatable directly; that takes some more code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial