:: how do I insert records in a .mdb linked server ::

:: how do I insert records in a .mdb linked server ::

Post by Carlos Rodrigue » Tue, 05 Feb 2002 23:03:13



hi, I'm using sqlserver 2000 and i need to insert records in a access file
linked in sql server.
I have an on insert trigger in a sqlserver table and I need to insert the
same records in a linked .mdb file.
thanks.
carlos
 
 
 

:: how do I insert records in a .mdb linked server ::

Post by oj » Wed, 06 Feb 2002 04:40:48


carlos,

assuming your access linked server is srv...

insert into srv...tbname(a,b,c)
select a,b,c from sqlservr_tablename

--
-oj
http://rac4sql.home.attbi.com


Quote:> hi, I'm using sqlserver 2000 and i need to insert records in a access file
> linked in sql server.
> I have an on insert trigger in a sqlserver table and I need to insert the
> same records in a linked .mdb file.
> thanks.
> carlos


 
 
 

:: how do I insert records in a .mdb linked server ::

Post by Rishi M. Agrawal [M » Thu, 07 Feb 2002 07:41:29


(This is assuming that you can query the linked server to the mdb file
properly).

Or you could use the OPENQUERY syntax as follows:

insert into openquery (srv, 'select a,b,c from tbname where 1=0')
select a,b,c from sqlservr_tablename

Refer the following article for more details:
http://support.microsoft.com/support/kb/articles/q270/1/19.asp

Hope that helps.

Regards,
Rishi M. Agrawal, MCDBA

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------


| Subject: Re: :: how do I insert records in a .mdb linked server ::
| Date: Mon, 4 Feb 2002 11:40:48 -0800
| Lines: 24
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400

| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: 32.97.239.32
| Path: cpmsftngxa07!tkmsftngp01!tkmsftngp03
| Xref: cpmsftngxa07 microsoft.public.sqlserver.programming:228993
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| carlos,
|
| assuming your access linked server is srv...
|
| insert into srv...tbname(a,b,c)
| select a,b,c from sqlservr_tablename
|
| --
| -oj
| http://rac4sql.home.attbi.com
|
|


| > hi, I'm using sqlserver 2000 and i need to insert records in a access
file
| > linked in sql server.
| > I have an on insert trigger in a sqlserver table and I need to insert
the
| > same records in a linked .mdb file.
| > thanks.
| > carlos
| >
| >
|
|
|

 
 
 

:: how do I insert records in a .mdb linked server ::

Post by Carlos Rodrigue » Thu, 07 Feb 2002 13:14:51


cool! didn't try it but the following code worked too!! but thanks!! :O)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into myaccesslinkedfile...tablename(field1, field2)
values (values1, values2)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

and the only reason why it wasn't working the way it should as a normal
linked server
is cuz i was using
Microsoft OLE DB Provider for ODBC Driver
insted of
Microsoft.Jet.OLEDB.4.0



> (This is assuming that you can query the linked server to the mdb file
> properly).

> Or you could use the OPENQUERY syntax as follows:

> insert into openquery (srv, 'select a,b,c from tbname where 1=0')
> select a,b,c from sqlservr_tablename

> Refer the following article for more details:
> http://support.microsoft.com/support/kb/articles/q270/1/19.asp

> Hope that helps.

> Regards,
> Rishi M. Agrawal, MCDBA

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure?  For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> --------------------


> | Subject: Re: :: how do I insert records in a .mdb linked server ::
> | Date: Mon, 4 Feb 2002 11:40:48 -0800
> | Lines: 24
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
> | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400

> | Newsgroups: microsoft.public.sqlserver.programming
> | NNTP-Posting-Host: 32.97.239.32
> | Path: cpmsftngxa07!tkmsftngp01!tkmsftngp03
> | Xref: cpmsftngxa07 microsoft.public.sqlserver.programming:228993
> | X-Tomcat-NG: microsoft.public.sqlserver.programming
> |
> | carlos,
> |
> | assuming your access linked server is srv...
> |
> | insert into srv...tbname(a,b,c)
> | select a,b,c from sqlservr_tablename
> |
> | --
> | -oj
> | http://rac4sql.home.attbi.com
> |
> |


> | > hi, I'm using sqlserver 2000 and i need to insert records in a access
> file
> | > linked in sql server.
> | > I have an on insert trigger in a sqlserver table and I need to insert
> the
> | > same records in a linked .mdb file.
> | > thanks.
> | > carlos
> | >
> | >
> |
> |
> |