Deleting and Appending data in a table from a separate database (1 Viewer)

rmcafee

Registered User.
Local time
Today, 08:32
Joined
Oct 20, 2017
Messages
44
I'm trying to delete data in a table, say Table A, Database A, from another Database, like Database B. I hope to delete the data then "append" the data once the data has been cleared. I know how to do it within a single database through the use of delete and append queries and running them through a macro, but I don't know how to do it from one database to another. I'm guessing that it has to be done through VBA, but I don't even know where to begin, since I'm not really a programmer and don't use VBA much. Any help would be appreciated.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Feb 19, 2013
Messages
16,553
you can do it in a query

delete * from tableB in 'C:\path\to\databaseB.accdb'

INSERT INTO tableB ( fldName ) IN 'C:\path\to\databaseB.accdb'
SELECT tableA.fldName
FROM tableA
 

rmcafee

Registered User.
Local time
Today, 08:32
Joined
Oct 20, 2017
Messages
44
Thanks, I think we're on the right track.
Let me add some details. I need the data in all the fields in table A, database A to be deleted and then append all the fields in Table A, Database A from Table B, database B. Does this change your solution?
thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Feb 19, 2013
Messages
16,553
all depends on where your query is in relation to databaseA and databaseB, so you may need to swap the code around
 

Solo712

Registered User.
Local time
Today, 11:32
Joined
Oct 19, 2012
Messages
828
you can do it in a query

delete * from tableB in 'C:\path\to\databaseB.accdb'

INSERT INTO tableB ( fldName ) IN 'C:\path\to\databaseB.accdb'
SELECT tableA.fldName
FROM tableA

Careful CJ, this may not be what he wants.

If he wants to simply delete everything in table A and then dump into the table the contents of table A in Database B, then it's simply

DELETE * FROM tableA

"INSERT INTO tableA
"SELECT * FROM tableA in 'C:\path\to\databaseB.accdb'


But if he wants to restrict the delete and insert only the rows which are duplicated in the databases (maybe this what he means by "delete from another database") or answer to some other some other criteria then of course we need more information.

Best,
Jiri
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Feb 19, 2013
Messages
16,553
OP said

I know how to do it within a single database through the use of delete and append queries and running them through a macro, but I don't know how to do it from one database to another
the only thing he needs to pick up on is the use of the IN clause - which is a standard query parameter anyway

IN 'C:\path\to\databaseB.accdb'
 

Solo712

Registered User.
Local time
Today, 11:32
Joined
Oct 19, 2012
Messages
828
OP said

the only thing he needs to pick up on is the use of the IN clause - which is a standard query parameter anyway

IN 'C:\path\to\databaseB.accdb'

That's right, but it assumes that the name of the table to be appended from is the same in both databases, which it may not be. I use this technique during development to roll back table data from backups - where the names are the same. If I had some local table copy to transfer from it would be named differently than the destination table.

Best,
Jiri
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Feb 19, 2013
Messages
16,553
well the OP wasn't clear and has now gone dark......
 

rmcafee

Registered User.
Local time
Today, 08:32
Joined
Oct 20, 2017
Messages
44
Thanks All
These posts were very helpful and gave me exactly what I needed.
Using your tips, I was able to delete the data in the table and then add the new data to that same table from the other database via a query without any problems.
:)
 

Users who are viewing this thread

Top Bottom