Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    1

    Unanswered: transferring data from oracle to access

    I am reading data from oracle and updating access db. Here is the code. I get error in updating date.

    set AccDb=server.CreateObject("Adodb.connection")
    set Oradb=server.CreateObject("Adodb.connection")
    set AccRec=server.createobject("ADODB.Recordset")
    set OraRec=server.createobject("ADODB.Recordset")
    if Request.QueryString("Query")="T" then
    Application("EmpMasterAccessDB")="Provider=Microso ft.Jet.OLEDB.4.0;Data Source=\DBProjects\EmpMaster\EmpMasterCopy.mdb;Per sist Security Info=False"
    Application("Oracle_HR")= "DSN=ora_hr;User ID=hr;Password=hr;"

    Oradb.Open Application("Oracle_HR")
    OraRec.Open "select * from EMPMASTER", Oradb
    AccDb.Open Application("EmpMasterAccessDB")

    Do While Not OraRec.EOF
    sqlopen="select * from TblEmployee where empid="& Tmpid
    AccRec.Open sqlopen, AccDb,adOpenStatic

    sqlupdate = sqlupdate & "CurrentHiredate="
    sqlupdate = sqlupdate & "(#" & OraRec("CURHIREDATE") & "#)" &", "

    sqlupdate = sqlupdate & "WHERE EMPID="& Tmpid
    Accdb.Execute sqlupdate,c
    AccRec.Close
    orarec.MoveNext
    loop

    Also I am opening and closing access db connection for each sql update. Is there a way I can open only once and insert all the rows and then close access db.

    Please help!

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try leaving out the Brackets:

    sqlupdate = sqlupdate & "CurrentHiredate="
    sqlupdate = sqlupdate & "(#" & OraRec("CURHIREDATE") & "#)" &", "

    so these two line look like this:

    sqlupdate = sqlupdate & "CurrentHiredate=#"
    sqlupdate = sqlupdate & OraRec("CURHIREDATE") & "#, "

    or better yet, make it one line:

    sqlupdate = sqlupdate & "CurrentHiredate=#" & OraRec("CURHIREDATE") & "#, "

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •