Connection between MDB and SQL Server

[Kate]

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
・
・

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

 

[Alex Dybenko]

then SQL will be:
strSql="INSERT INTO table(field1,field2) Select fileld1, field2 from table2"

 

[Kate]

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

 

[Alex Dybenko]

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

 

[Kate]

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

 

[Alex Dybenko]

Hi Kate,
ok, then try another way

leave connection as you have

SQL should be as:

INSERT INTO table1(field1,field2)
SELECT afield1,afield2 from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=<path to mdb>;User ID=Admin;Password=')...table2


for example, in you want to select customers for northwind sample database
you can run in query analizer:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Program
Files\Office2003\OFFICE11\SAMPLES\Northwind.mdb";User
ID=Admin;Password=')...Customers

 

[Kate]

Hi Alex,

Thank you for your big help ! It worked !
But I still have a problem... If the sql server and the
mdb file are exisitng on the same computer. It worked.
But if they are exisiting on the different computer, it
didn't work.
The error -2147217900(80040e14) had been returned.

The message saying:
OLE/DB provider returned message PATH C:\test\test.mdb is
not correct.
Make sure whether the Path is accurate and connect to the
server.

How should I do? Is this a problem on the access right ?

Kate

 

[Alex Dybenko]

Hi Kate,
eiither you place test.mdb on some server's share and then use
\\server\share\test.mdb instead of C:\test\test.mdb
or like i worte at the beginnig - you can link sql table to access database
and then run query based on connection to access database. also depends on
your final goal