Connection through Access with ORACLE and MS SQL SERVER 2000

[lazaridisaris]

Hi to All,

My problem is
1. I have an Oracle database which has some tables.
2.I have an SQL 2000 database that has some other tables
3.I have an Access 2000 database

The goal is to read a table from Oracle (which I do with ODBC connection) to SELECT some records from there with specific criteria and write them to a Table in MS SQL server.

The problem is that with ODBC connection this thing takes too long to complete.

My question is can I use an ADODB connection to run a stored procedure or Function in SQL server without changing to ADP access project (I thing that if I do that then I will loose the connection to ORACLE)

Or more simply how can I create a connection string to connect to the SQL Server ?

Thanks in advance.


Aris

 

[cmmrfrds]

Here is a connection to sql server that sends a query with the oledb connection information to an Access MDB. You should be able to do the same thing with Oracle. Replace the Access connection stuff with Oracle. The OPENROWSET function in sql server provided the connection information is supplied can cross join servers.

Public Function rowsetInsert()
'-- set reference to ADO library
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cn As New ADODB.Connection, sqlstring As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=yoursqlserverhere;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlstring = "insert Into "
sqlstring = sqlstring & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlstring = sqlstring & "'C:\aemptydir\employee2.mdb';'admin';, NewShippers) "
sqlstring = sqlstring & "(CompanyName, Phone) "
sqlstring = sqlstring & "Select CompanyName, Phone From Shippers"

rs.Open sqlstring, cn, adOpenForwardOnly, adLockReadOnly

End Function

See this site for Oracle OLEDB connection examples.

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForOracleFromOracle

 

[lazaridisaris]

Thanks for your time
I will try this and let you know what happend

Regrads

Aris