Updating Access Tables from Oracle Tables

Results 1 to 4 of 4

Thread: Updating Access Tables from Oracle Tables

  1. #1
    Jason Guest

    Default Updating Access Tables from Oracle Tables

    I need to update an ms access tables by retrieving data from an oracle table, I&#039ve done this using DAO using tabledef and linking the oracle tables to access in VB, now I need to do it with ADO VBscript. Any suggestions?

  2. #2
    besharah Guest

    Default RE: Updating Access Tables from Oracle Tables

    I&#039m not sure how much you&#039ve used ADO before, but if you have any experience at all I think you&#039ll find using a snap. A good website for the entire API is on Microsoft&#039s website.<BR><BR>Basically you can use the CONNECTION object for all your needs.<BR>Create two DSN&#039s, one to connect to Oracle and one to Connect to ACCESS.<BR>1)Create an ADO connection object <BR>2)call the open method with the password, user and dsn as parameters<BR><BR>e.g m_DSN = "DSN=micdemo"<BR> m_user = "Demo"<BR> m_Password = "Demo"<BR> Set m_ado_connection = server.createobject ("ADODB.Connection")<BR> m_ado_connection.Open m_DSN, m_user, m_Password<BR><BR>3) that done call the execute method of the connection object passing your select statement and setting a variable equal to the return value<BR><BR>e.g. set myrecordset = m_ado_connection.execute(sqlstring)<BR><BR>4) once you have the recordset with the values you can repeat the process in the other direction passing the values in the recordset as values in your insert statement. Again, you can use the connection object&#039s execute method for this. You will, of course, have to create a new connection this time to your Access database.<BR>

  3. #3
    Jason Guest

    Default RE: Updating Access Tables from Oracle Tables

    O.K. I&#039ve gotton that far, now I need to take it another step.<BR>How do I get the record set from one to the other?<BR>Such as SQL="insert into table1 select * from table2"<BR>table2 would be my oracle recordset and one being the access table.

  4. #4
    besharah Guest

    Default RE: Updating Access Tables from Oracle Tables

    Once you have the recordset from the Oracle database, you will have to construct sqlstatements from the recordset and then call the execute method of your NEW connection object. You can refer to the fields in the recordset by accessing(you guessed it) the fields collection. <BR>e.g. myrecordset("last_name") or myrecordset(2)<BR>You can build an insert statement like:<BR><BR>sqlString = "Insert into AccessTable Values(&#039"&myrecordset("last_name")&"&#039, &#039"&"&myrecordset("first_name")&"&#039)"<BR><BR >and then---- myAccess_connection.execute sqlString <BR>This time nothing is returned, ADO will simply execute your query, inserting the values. <BR><BR>Things get messy and probably slow if you have many records to do at once. You can automate this building of sqlstrings and executing of queries by placing the commands within a loop<BR><BR>e.g Do until myrecordset.eof<BR><BR> Build sqlstring as above<BR> execute query<BR> myrecordset.movenext<BR> Loop<BR><BR>The Do unitl eof(end of file) loop is the simplest and will stop when the recordset has hit the end of the records.<BR><BR>If you have any other questions or want to see some code e-mail me at besharah@hotmail.com<BR><BR>Good Luck!<BR><BR>

Posting Permissions

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