Solved

Access VBA --> ODBC --> Oracle

Posted on 2006-06-14
2
1,767 Views
Last Modified: 2008-03-10
I previously had an MDB front end connected to an MDB back end.  Using VBA from the front end to open a recordset of a particular table and add records to that table was basically done as follows:

Dim con As ADODB.Connection
Dim rsX As New ADODB.Recordset

Set con = CurrentProject.Connection

rsX.Open "tblX", con, , adLockPessimistic

rsX.AddNew
    rsX!field1 = "xxx"
    rsX!field2 = "yyy"
    rsX!field3 = "zzz"
rsX.Update

Simple.  Worked great.  No problem...  BUT, now the MDB back-end data has been converted to Oracle and I need to change the front-end VBA to accomplish the same logic as above.  I've gotten this far:

Dim con As New ADODB.Connection
con.Open "DRIVER={ORACLE ODBC DRIVER};..." (whole connection string not shown...)

The connection opens fine, but that's where I'm stuck.  How do I open a recordset of the table to which I want to add records?  And then how do I add the records?  Am I going to have to use "INSERT..." SQL to get this done?  I've seen some references to being able to establish a recordset and then manipulate it just as I did above with Access tables, but I haven't gotten it to work...

Thanks for any tips!
0
Comment
Question by:jahooper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Accepted Solution

by:
TommyTupa earned 125 total points
ID: 16908571
Check out this great example: http://cis.csuohio.edu/~matos/notes/ist-331/NEW-Oracle-Access-Notes.doc

Also, below is VBA for opening a recordset.  If you need to insert just do a cn.execute MySQLString


Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   With cn
      .Provider = "MSDAORA"
      .Properties("Data Source").Value = "MyOracleServer"
      .Properties("User ID").Value = "username"
      .Properties("Password").Value = "password"          
      .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
   End With

   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub
0
 

Author Comment

by:jahooper
ID: 16910824
Exactly the direction I needed.  Well done!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question