Insert into statement using 2 seperate datsbases

[gillianm1]

Hi,

I have the following code:

insert into DWCSYPER
(CPCONO,CPDIVI,CPPETP,CPYEA4,CPPERI,CPTX15,CPFDAT,CPTDAT,CPNCDY,CPNWDY,CPNBDY)
select CPCONO,CPDIVI,CPPETP,CPYEA4,CPPERI,CPTX15,CPFDAT,CPTDAT,CPNCDY,CPNWDY,CPNBDY
from CSYPER

The problem is I'm inserting to a SQL Server database and selecting from an Access database. I've heard I can use the IN clause (ie. 'Insert INTO DWCSYPER IN path (CPCONO,....)' )to specify an external database but I don't know the exact syntax for the path expression. Can anyone help?
[sig][/sig]

 

[MikeLacey]

Hi Gillian,

If, within Access, you define the SQL Server table by attaching an external table (from the File menu I think) you will be able to insert/update/delete on that table from within Access. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[LordOfCode]

You can add a linked server to your SQL Sever in order to connect to your Access Database or any other.

If you do this you can use function OpenQuery() to execute any SQL Instruction.

Sample01

Select * from OpenQuery(ACCESS_LINKED_SERVER,'SELECT * FROM <tablename>')


Insert into OpenQuery(ACCESS_LINKED_SERVER,'SELECT * FROM <tablename>') Values(<Value1>,<Value2>,...,<Valuen>)

See help about OpenRowSet() function too [sig]<p>Edwin Dalorzo<br><a href=mailto:edalorzo@hotmail.com>edalorzo@hotmail.com</a><br><a href= > </a><br> [/sig]