import data query

[krisleech]

I am running into a of confusion with an append query.
Basically i have a master database (destination) and many data entry databases (source).
All the data from the data entry databases needs to be imported into the master database.
I am having trouble specifying where the data entry database is, i create a new query, change it to an append query, you are then asked which database u wish to append to (i choose the master local master database), then what?

How do i choose where the source database is ?

Any help please.. Kris Leech

 

[Jon K]

You can use an IN Clause in the SQL statement of an append query to tell Access where to look for the source database.


The following SQL statement appends all the records from the data entry table in C:\My Documents\Data Entry Database1.mdb to the master table:-

INSERT INTO [MasterTable]
SELECT *
FROM [DateEntryTable] IN "C:\My Documents\Data Entry Database1.mdb"


You can directly type the SQL statement in the SQL View of a new query in the master database.

Note. If the master table contains a primary key, no duplicate primary key values can be appended to the table.

 

[krisleech]

thanks for that jon, that simple, yet i have a advanced MS-Access book and it does not explain this. Maybe I need a dedicated SQL book?

Is it possible to let the user enter the location of source database, like putting [Please enter value] in the cirteria, so you get a pop-up box where the user can enter something.

(even better would be a browse button so the user can navigate to the correct directory that contains the database)

Best wishes, Kris.

 

[Jon K]

I don't think you can use a parameter in the IN Clause, as Access will mistake the parameter as the file name.

However, you can always build and run your query with VBA code.

 

[krisleech]

im having a problem with the IN clause, i keep getting a file not found error. The path and filename are correct, i even copy pasted the path and filename from the databases properties to prevent typo's.

Any suggestions?


Looks like im gonna have to learn VB @ Jon. I already use delphi and C, and now another.


Kris Leech

 

[krisleech]

In clause solved

sloved the problem of file not found.
the path was fine.
when looking at the file in Win XP it is labeled pss.mdb
therefore i thought fine the filename is pss.mdb
but no, the label does not include the file extension, so the file is actually called pss.mdb.mdb
so renaming the files label to pss solved the problem, the file was now pss.mdb and the query worked as expected.

I dont know how the files extension got to be included in the files label, i dont think i did it, so maybe a querk in win XP??

Kris.