Append new records (1 Viewer)

[karatelung]

I have a stripped down version of our DB (front-end and back-end) on a notebook computer so that certain people out of the office can enter info. I did have the VPN set up with the tables linked to our back-end, but the notebooks connect via analog dial-up. it works, but it's just way too slow.

So I'm just having them enter the info into on the stripped down version with the tables on the notebook. Then the new records will be appended. Here's my problem. I want an append query that will only append the records to the back-end DB where [autonumber] is greater than [autonumber] on our server. The table name is the same on both the notebook and server - tblOnCallEntry.

I don't know how to reference the [autonumber] field on the server. The drive is mapped, so I tried this as the criteria for [autonumber] on the notebook:

> e:\HFKDB_be.mdb.[tblOnCallEntry].[AutoNumber]

it didn't work. Does anyone have any ideas?

Thanks

 

[RV]

Just an idea, couldn't you use ODBC?
Access Help gives more info about connecting external databases by ODBC.

A question:

>I want an append query that will only append the records to the back-end DB where [autonumber] is greater than [autonumber] on our server<

You're telling you're using a stripped version of the database on the notebook.
So, actually there are TWO backends, one on the server and one on the notebook.
If data is entered both on the "office" backend and the "notebook" backend, don't you get your autonumbering screwed up?

HTH,

RV

 

[karatelung]

i can look into ODBC, but for the time being the append query might be the easiest.

the autonumbering should be okay because the only people entering information into that specific table will be the people with the notebooks.

wait, nevermind - there are two notebook computers, so i guess the autonumbering will get screwed up. i can base the append queries on two fields maybe - dateEnter and Client. i don't know.

i'd still like to know how to reference a field on another DB for future reference or in case i get this figured out.

Thanks

 

[Jon K]

You can use the IN Clause to refer to a table in an external database.

The following example inserts into table tblClient in E:\Main.mdb those records whose FirstName plus LastName do not appear in the table in E:\Main.mdb.


INSERT INTO tblClient IN 'E:\Main.mdb'
SELECT *
FROM Client
WHERE FirstName & LastName NOT IN (SELECT FirstName & LastName FROM tblClient IN 'E:\Main.mdb');

Hope this helps