[AccessD] Copy Record from one db to another

You missed out the required single quotes....

db1.Execute "INSERT INTO tblProductStructure.* in '" & Me.txtTargetDatabase & "' SELECT tblBOMReport.* from tblBOMReport"

I.e. the full path to the destination database needs to be surrounded with single quotes.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, November 10, 2011 4:50 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Copy Record from one db to another

NO - but I tried it with bracketing and same result.  

Anyway I added a few lines of code and got it working by brute force - couple of DAO recordsets, .AddNew, loop through the source fields --> target field, .Update, walla. Done.

Not elegant but effective.

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, November 10, 2011 1:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Copy Record from one db to another

Any spaces in the target database's  path?

--
Stuart

On 10 Nov 2011 at 12:18, Rocky Smolin wrote:

> I will delete from the target db all the records in the source that 
> match up.
> 
> But that still leaves me with the copy (or append actually) which I 
> was trying to do with an INSERT INTO statement but can't seem to get 
> the right syntax.  I have the target db path and name available in a 
> text
box.
> 
> But this is not working:
> 
> db1.Execute "INSERT INTO tblProductStructure.* in " & 
> Me.txtTargetDatabase & " SELECT tblBOMReport.* from tblBOMReport"
> 
> At this point I'll have deleted all the records from the target that 
> match the source so the source records can all be appended in one 
> swell
foop.
> 
> R
> 
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
> Sent: Thursday, November 10, 2011 12:10 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Copy Record from one db to another
> 
> When you say "COPY", does that mean all target records with matching 
> keys from the source are deleted and then inserted ?
> Or is it a mixed bag where some new ones are inserted and some 
> existing ones updated ?
> 
> 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:accessd- 
> > bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> > Sent: Thursday, November 10, 2011 2:22 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: [AccessD] Copy Record from one db to another
> >
> > Dear List:
> >
> > I need to copy specific records with about 35-40 fields per record 
> > from one database to another.  No autonumber PK.  Are there any 
> > slick tricks for doing this?  I have the path and file name of the 
> > target and the table will be present there.  The source db is CurrentDb.
> >
> > MTIA
> >
> > Rocky Smolin
> > Beach Access Software
> > 858-259-4334
> > www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com 
> > <http://www.e-z-mrp.com/>
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com