Needing to insert records to table from another table

[orange]

Been out all day. Yes micron, all of your thoughts could be true. I'm just surprised that we haven't heard anything--good or bad.

[Cottonshirt]

Micron said: What I didn't see suggested is to create a SELECT query first and if it returns the records, convert it to an append query (without any autonumber field of course).

emhill57 (the op) said: That user enter 750 records into a table on the wrong back-end.

as I understand the problem, the table where the records reside, and the table where the records need to be, are in different databases. there is no relationship between the two and there is, therefore, no way to APPEND them from one to the other. that is precisely why I suggested doing it manually.

maybe I'm wrong. and if I am I would appreciate someone explaining to me how two different BE can talk to each other.


take care,


Cottonshirt

[orange]

I think the OP should provide some sample data. There may well be some details we have not been told that have relevance in any solution.

As for FE talking to different backends, FE (could be custom) linked to separate BEs.

[Cottonshirt]

orange said: As for FE talking to different backends, FE (could be custom) linked to separate BEs.

maybe we are talking about different things, but linking one FE to two BE is not connecting two BE to each other.

and, linking one FE to two BE does not permit APPEND of records from one BE to the other.

you can IMPORT database objects (forms, queries, and tables) but you cannot APPEND, which is what micron suggested. you can IMPORT a query as a table. or you can IMPORT a query as a query, in which case you also have to IMPORT the underlying tables. but you cannot APPEND.

my objective here remains to understand micron's post, and hopefully learn something. micron wondered why no one had suggested doing an APPEND, and I still think it is actually impossible.

please correct me if I'm wrong.


many thanks,


Cottonshirt

[June7]

Why would you think it not possible for DB A to set links to both DB B and DB C and append records from B to C?

I have used VBA to replicate records from linked tables (or queries using those tables) to another db that is not linked.

Only one table, no related dependent records?

750 records could be a simple copy/paste.

[Cottonshirt]

June7 said: Why would you think it not possible for DB A to set links to both DB B and DB C and append records from B to C?

because this page on the Microsoft Support Forum says, "If your goal is to add records from one database to an existing table in another database, you should consider importing the records to a new table and then creating an append query. You cannot append records to an existing table during an import operation." (added emphasis).

that says that you have to IMPORT them first, and then you can APPEND records only within a single database. you cannot APPEND records from one DB to another.

June7 said: I have used VBA to replicate records from linked tables (or queries using those tables) to another db that is not linked. Only one table, no related dependent records? 750 records could be a simple copy/paste.

this may all be true, but the word APPEND doesn't appear in anything you said. we are not talking about simply moving records from one place to another by any means possible. the question was: can you APPEND from one db to another?


many thanks,


Cottonshirt

[isladogs]

@cottonshirt
I always think the easiest way to test this sort of question is just to try it for yourself...
BUT to save you time, the answer is ...YES.
In fact, I regularly do this as a way of synchronising data etc.

As far as Access is concerned, linked tables are just like local tables. You can therefore append from one linked table to another or run other type of action query as well

[Micron]

Maybe the confusion arises from importing vs appending via a query. They are two different things to my way of understanding, so

You cannot append records to an existing table during an import operation.

means just that - import - and AFAIK, the reason is that such an import would overwrite what's in the table. Maybe that's not what they meant but I didn't follow the link. An append query is another animal, and I side with those who say it is possible to append (vial query) from one linked table to another when each table is linked to a different db.

[Cottonshirt]

Micron said: means just that - import - and AFAIK, the reason is that such an import would overwrite what's in the table.

that Microsoft Support webpage I linked to says: "Each import operation creates a new object in the destination database. You cannot overwrite an existing object or append records to an existing table by using an import operation."

so, IMPORT transfers one or more database objects (form, table, query, report etc) from one DB to another. this sounds to me like it is analogous to a drag and drop operation, where you simply drag the whole object from one folder to another.

that whole page is about understanding linking and importing, which it treats as distinctly different processes. if you want to Link, do this, but if you want to Import, do that.

so when it said that you cannot APPEND during an import, I assumed that it meant only when importing, but that you can link and APPEND.

but at the bottom of the same page (where it is talking explicitly about linking, not importing) it says quite clearly that you can't do that either: "Remember that if you want to append the records in the source table to a table in the destination database, you must use an append query instead of running a linking operation" (emphasis added).

which, I freely admit, I find very confusing. for example, how does it know where to APPEND the records if the DB's are not even linked together?

isladogs said: You can therefore append from one linked table to another or run other type of action query as well.

but isladogs says you can do this anyway.

I'm going to put two old backups of my DB to one side and have a go at this because I want to know how this works.

thanks everybody for your input.



Cottonshirt

[orange]

Just to add more confusion to the mix:
-you can import without using the import wizard (create your own vba) and append data to a table

[Micron]

Yes, you can import objects, but you can do data imports as well, yes?
Please let us know how you make out because while I have not used fe to ever append from dbA linked table to dbB linked table in the same fe. I just can't see why it's not possible. "...you must use an append query" seems to support that. However, I have no idea what they mean by "instead of running a linking operation". I guess I'll have to follow that link.

[isladogs]

Linking is different from importing .
Similarly of course, appending is different from importing
You can only do one of those things at a time.

However you can add a link and then append data to the new linked table
Or you can import a table and then append data to the new local table.
Or link a table then change it to local....

And, whilst I'm on a roll, you can also use drag & drop to import or link tables (or any other database object). See https://www.youtube.com/watch?v=uQp8jCqmoA

EDIT: you can even use VBA to edit an external table that isn't linked

[June7]

@Cottonshirt, sorry, I misunderstood the point of your comment. The original question is about getting data from one db table to another db existing table via a third db. That sounds like appending to me. This can be accomplished by setting links to both data sources (via the 'import' utility) then executing an INSERT SELECT action SQL. So, agreed, import and append cannot be in one step. I am not sure anyone said there must be one action that does both although a single VBA procedure can execute the required steps but building the code would take longer than accomplishing manually.

OP already has a frontend linked to correct backend. Now set link to db table with 750 records and replicate records by INSERT SELECT or copy/paste then delete the link as well as the bad db. Or don't set a link. Open frontend and 750-record backend side by side. Copy/paste records.

If there really are no dependent records, this is a simple replication.