Copy data from one db to another using 'Select * Into... From... IN...' suddenly giving RT err 3251

[Micron]

Wouldn't life be a whole lot easier if you simply linked to the other table? Anyway, try just selecting from the foreign table and see what happens.
SELECT * FROM tblBookmarks IN 'S:\DataHome\BidBunker_DATA.accdb';



If your method worked for Isladogs but not you, it would seem that you have yet to address your version as the culprit. However, you've gone from SELECT * INTO to INSERT INTO, and I cannot recall ever seeing INSERT INTO without specifying the target fields in some way. Not sure if you can use the wildcard for that because I'd take the easy route and link the source tables.

[isladogs]

Hi Micron
Yes, you can do an INSERT query like that.
I agree with you completely about linking the source table(s) from the other database.

@Day4
The generalised version which I've just tested is:

DoCmd.RunSQL "INSERT INTO newTable SELECT * FROM " & tblName & " IN '" & dbPath & "'"

or use CurrentDb.Execute

In either case, it should run without error
However, do bear in mind that this code will allow you to repeatedly append the same data, so I recommend you empty the destination table first:

CurrentDb.Execute "DELETE * FROM newTable;", dbFailOnError
CurrentDb.Execute "INSERT INTO newTable SELECT * FROM " & tblName & " IN '" & dbPath & "';", dbFailOnError

However if you link the source table(s) as recommended above, the IN part of the query is no longer needed.
Make use of linked tables wherever possible. It makes coding much easier & there are no drawbacks!

[wvmitchell]

Can you inspect the rights on dbPath to determine if anything has changed?