Re: Connection between MDB and SQL Server

From: Kate (anonymous_at_discussions.microsoft.com)
Date: 04/12/04 

Date: Mon, 12 Apr 2004 04:29:49 -0700

It didn't work.
I made the program like below by using the provider of
Microsoft OLE DB Provider for SQL Server.
But do I need to use the provider of Microsoft OLE DB
Provider for Microsoft Jet in order to make a connection
between Access and SQL?
The error message saying "The name of table2 is invalid"
was shown.
What's wrong ?

strCn = "Provider=SQLOLEDB;Data Source=Server;Initial
Catalog=Database;User ID=ID;Password=PWD;"
strSql = "INSERT INTO table1(field1,field2) SELECT
afield1,afield2 from table2"

Set cn = New ADODB.Connection
cn.ConnectionString = strCn
cn.Open
・
・

Kate
>-----Original Message-----
>Kate,
>i think i missed about SQL server table, sorry.
>so what you can do then:
>link your sql table to access mdb where you have first
table
>(make sure it has priamry key, else it will be read-only
in access)
>open connection to access mdb
>execute sql i wrote
>this should work
>
>--
>Alex Dybenko (MVP)
>http://Alex.Dybenko.com
>http://www.PointLtd.com
>
>
>"Kate" <anonymous@discussions.microsoft.com> wrote in
message
>news:1827d01c41f72$31ffff90$a601280a@phx.gbl...
>> Alex
>>
>> Thank you for your post.
>> According to your code,table2 is on Access database
but I
>> don't need to specify that it is Access table.
>> Is that accurate ? Why I don't need it ?
>> How can ADO judge this is not a SQL table but Access
>> table ?
>>
>> I am a quite beginner of ADO.
>> I will try with your code tomorrow at my office.
>> Before doing it, I am curious.
>>
>> Thanks,
>> Kate
>>
>>
>> >-----Original Message-----
>> >then SQL will be:
>> >strSql="INSERT INTO table(field1,field2) Select
fileld1,
>> field2 from table2"
>> >
>> >--
>> >Alex Dybenko (MVP)
>> >http://Alex.Dybenko.com
>> >http://www.PointLtd.com
>> >
>> >
>> >"Kate" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:1afe401c41ecd$a8e538a0$a101280a@phx.gbl...
>> >> I am trying to make a program with ADO. I want to
add
>> >> data from Access into the table on SQL Server.
>> >> I wrote the code like the below.
>> >>
>> >> strCn="Provider=SQLOLEDB;Data Source=Server;Initial
>> >> Catalog=database;User ID=userid;Password=pwd;"
>> >> strSql="INSERT INTO table(field1,field2) VALUES
>> >> ('access','u.s.') "
>> >>
>> >> Set cn = New ADODB.Connection
>> >> cn.ConnectionString = strCn
>> >> cn.open
>> >> &#12539;
>> >> &#12539;
>> >>
>> >> It worked fine.
>> >> But what I want to do is to add part of the data in
the
>> >> table of Access into the table of SQL Server.
>> >> How should I write INSERT statment ? I need to use
ADO.
>> >>
>> >> Can anyone help ?
>> >>
>> >> Kate
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages