New record added in ODBC table

[GordonRS]

Good morning all!

I have an ODBC link in an Access 2002 d/b to an AS400 table (with the ODBC refresh interval set to 10 seconds) and as the data changes, the data in form view changes just fine and dandy, thank you very much.
However, when a new record gets added to the AS400 physical file, the recordset doesnt update to show this, even if I use records>refresh. I have to close the form down and re-open it to see the new record.
Is there a method to achieve this?

Thanks!

Gordon

 

[Pat Hartman]

Requery will run the query again which will pick up any new records.

 

[GordonRS]

Hi Pat!

Thanks for the reply.

I should have said I was just viewing the data in datasheet mode from 'tables'. It just intrigued me that the datasheet view automatically refreshed existing rows with any changed data but simply ignored new rows being added.
Perhaps I should explain what I am trying to do. We have a GPRS application that will put incoming transactions in the form of a new .txt into a folder. I want to detect their arrival and import the data into a database. Oh, and the data is multi-lined (shame its not in XML format!).

Just kicking the dust in the hope of inspiration, really!

Gordon

 

[Pat Hartman]

In a database that anyone other than yourself will use, the user should not even have access to the database container. All interaction should be via forms. Only forms support events which are needed to ensure smooth interaction for the user (such as requery without closing). Also, when your tables are ODBC, it is even more important that your forms and reports be based on queries that select ONLY the minimum number of rows and columns to be displayed. Opening a form bound to a table or a query with no select clause or opening the table directly from the database container opens a pipeline to the backend database which ultimately transfers every row in the back end table to the client machine. This wastes the potential of an ODBC database to increase efficiency in Access applications.

I want to detect their arrival and import the data into a database.

Although you can do this with Access by using a timer, I wouldn't. I would use Windows itself. I would create a database that links to the main database and just contains an autoexec macro that runs code to import the text file. I would use the Windows scheduler to "run" this import database. The autoexec macro or startup form if you prefer run when the db is opened and they can contain whatever code you want. When the import is done, your code should close the database.