Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HOW TO GET DATA IN ORACLE TABLE FROM ACCESS TABLE
- Thread starter taqmeena
- Start date
CassidyHunt
IS-IT--Management
As long as you have access to run an ASP page, which fields in the Access database correspond to the oracle database, and can modify a little code you can do it this way. Its pretty straight forward.
Hope it helps.
Cassidy
Code:
<html>
<head>
</head>
<body>
<%
dim cnAccess,rsAccess,cmAccess, cnOracle,cmOracle,rsOracle, sql
set cnAccess = Server.CreateObject ("ADODB.Connection")
set rsAccess = Server.CreateObject("ADODB.Recordset")
set cmAccess = Server.CreateObject("ADODB.Command")
set cnOracle = Server.CreateObject ("ADODB.Connection")
set rsOracle = Server.CreateObject("ADODB.Recordset")
set cmOracle = Server.CreateObject("ADODB.Command")
with cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\inetpub\[URL unfurl="true"]wwwroot\sitefolder\access.mdb"[/URL]
.Open
end with
with cnOracle
.Provider = "OraOledb.oracle"
.ConnectionString = "Data Source=Database;User ID=User;Password=Password;"
.CursorLocation = 3
.Open
end with
cmAccess.CommandText = "Select id, field1, field2 from table1"
cmAccess.ActiveConnection = cnAccess
Set rsAccess = cmAccess.Execute
cmOracle.ActiveConnection = cnOracle
do until rsAccess.EOF = true
sql = "insert into mytable values('" & rsAccess("id") & "','" & rsAccess("field1") & "','" & rsAccess("field2") & "')"
cmOracle.CommandText = sql
cmOracle.Execute
rsAccess.MoveNext
loop
cmOracle.CommandText = "Select * from mytable"
Set rsOracle = cmOracle.Execute
do until rsOracle.eof = true
Response.Write rsOracle("ID") & "<BR>"
rsOracle.MoveNext
loop
set cnAccess = nothing
set cnOracle = nothing
set rsAccess = nothing
set rsOracle = nothing
set cmAccess = nothing
set cmOracle = nothing
%>
</body>
</html>Hope it helps.
Cassidy
Hi
You can download a trial version of Access to Oracle 1.3 from
the demo version transfers only 5 records but if its useful for u, u can buy the license for it, not sure about the cost.
HTH
ARif
You can download a trial version of Access to Oracle 1.3 from
the demo version transfers only 5 records but if its useful for u, u can buy the license for it, not sure about the cost.
HTH
ARif
Apprentice101
IS-IT--Management
I have actualy done it by creating an insert query from access. Access will do the code for you, its very simple, see the help file "insert queries"
the only thing you will need to have is an ODBC connection setup to your orable tables with your host string name and have the tables linked in access from ORACLE so that your query can see them.
give it a try, it may be the cheapest and easiest way.
just my two cents//
the only thing you will need to have is an ODBC connection setup to your orable tables with your host string name and have the tables linked in access from ORACLE so that your query can see them.
give it a try, it may be the cheapest and easiest way.
just my two cents//
BackStageJim
IS-IT--Management
Done it many times.
Link from Access to Oracle.
1. Create your Oracle table.
2. Create a dsn link from Oracle to A2K, under GET EXTERAL DATA.
3. In qry append mode insert into Oracle from A2K.
- Status
Similar threads
- Locked
- Question