By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,376 Members | 3,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,376 IT Pros & Developers. It's quick & easy.

SQL Query for two Access Databases

P: 6
Hi
Could someone please help me.

I needed to pick data from a Access Database("AAAA") from a particular TABLE("TAAA") from a field("FAAA") based on a primary key("PAAA").
Then i have to copy that data to another Access Database("ABBB") to a table("TBBB") to a record based on the same primary key("PAAA") to the field ("FBBB").

Sorry abit complicated.
I needed to do this by an SQL query.
Could someone please help.

Thanks alot
Jaskaran
Oct 11 '07 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
Hi
Could someone please help me.

I needed to pick data from a Access Database("AAAA") from a particular TABLE("TAAA") from a field("FAAA") based on a primary key("PAAA").
Then i have to copy that data to another Access Database("ABBB") to a table("TBBB") to a record based on the same primary key("PAAA") to the field ("FBBB").

Sorry abit complicated.
I needed to do this by an SQL query.
Could someone please help.

Thanks alot
Jaskaran

Try:
INSERT INTO TBBB IN 'C:\DatabaseFolder\ABBB.mdb' (PAAA, FBBB) (SELECT PAAA, FAAA FROM TAAA IN 'C:\DatabaseFolder\AAAA.mdb'

When placed in ABBB, then the first IN can be dropped.

Nic;o)
Oct 11 '07 #2

P: 6
Try:
INSERT INTO TBBB IN 'C:\DatabaseFolder\ABBB.mdb' (PAAA, FBBB) (SELECT PAAA, FAAA FROM TAAA IN 'C:\DatabaseFolder\AAAA.mdb'

When placed in ABBB, then the first IN can be dropped.

Nic;o)


Thanks alot Nico :)))
Will try this and get back.
I really appreciate the help.
Oct 12 '07 #3

P: 6
Try:
INSERT INTO TBBB IN 'C:\DatabaseFolder\ABBB.mdb' (PAAA, FBBB) (SELECT PAAA, FAAA FROM TAAA IN 'C:\DatabaseFolder\AAAA.mdb'

When placed in ABBB, then the first IN can be dropped.

Nic;o)
Hi Nico

The "Insert Into" query seems to be for appending records. So i got an error.
My query was as below:

INSERT INTO tblFamily (PrimaryKey, FieldCommon) Select PrimaryKey, FieldCommon From tblDefaultFamily in 'C:\Documents and Settings\Jaskaran Singh\Desktop\db\UserMarkedParts.mdb '

But i need to update the table instead of appending records to it.
Could u please advice how to go about.
Your help is greatly appreciated.

Thanks
Jaskaran
Oct 15 '07 #4

nico5038
Expert 2.5K+
P: 3,072
Hmm, in such a case we normally append rows having an ID not found and UPDATE rows that have an existing ID.
There's however a chance that you're updating an existing correct value with a new incorrect value. Only when you're 100% sure that the field doesn't get corrupted you can use an update query.
I sometimes perform first a DELETE query to remove the ID's that are present in the table to be updated, followed by an append query. Thus the effect is the same as having and UPDATE and an INSERT.

For the UPDATE (or DELETE and INSERT) the same mechanism for using the table from another database can be used. Just create a standard UPDATE (or DELETE) query and use that.

Nic;o)
Oct 15 '07 #5

P: 6
Hmm, in such a case we normally append rows having an ID not found and UPDATE rows that have an existing ID.
There's however a chance that you're updating an existing correct value with a new incorrect value. Only when you're 100% sure that the field doesn't get corrupted you can use an update query.
I sometimes perform first a DELETE query to remove the ID's that are present in the table to be updated, followed by an append query. Thus the effect is the same as having and UPDATE and an INSERT.

For the UPDATE (or DELETE and INSERT) the same mechanism for using the table from another database can be used. Just create a standard UPDATE (or DELETE) query and use that.

Nic;o)
Hi Nico

Thanks for repling.
And thanks for the information.

I made a new query:
Update tblFamily Set CommonField= (Select CommonField from (Select CommonField,PrimayKey From tblDefaultFamily in 'C:\Documents and Settings\Jaskaran Singh\Desktop\db\UserMarkedParts.mdb ') where PrimayKey = "Uuuuu" ) where PrimayKey = "Uuuuu"

Here PrimayKey is the primary key in both tables.
But this gives an error: "operation must use an updatable query".
Tricky one.... i tried to directly update the field without the inside query and it worked.
But on using the inner query it gave the error. There is no password to my database.

Eventually i need to update all records of the field "CommonField" based on the primary key; and it should match with the "CommonField" in the external table based on the same primary key.

Please advice
Jaskaran
Oct 15 '07 #6

nico5038
Expert 2.5K+
P: 3,072
The UPDATE query is often very "sensitive", and that's the reason I often use a DELETE followed by an INSERT :-)
I do however notice that you've "nested" two SELECT queries. That shouldn't be necessary, just extract the needed field with one SELECT with the propriate WHERE clause.

Nic;o)
Oct 15 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.