ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset [RESOLVED]

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
MS ACCESS, remote Oracle Database Newbie Alert!

I have an assignment to fetch a snapshot of some date limited data using MS Access from a large corporate Oracle database for a set of reports. I can now comfortably fetch data using ODBC static Pass-Through Queries. I need to generate my queries programatically, though, and I am quite confused, and I have virtually no reference information ...

Do "Database"/"Table" go together as "Workspace"/"Connection"/"Recordset" go together? (am I even close?)

What determines when I would use a Database/Table, as opposed to when I would use Workspace/Connection/Recordset?

Is there some template code somewhere that I can use as a tutorial for learning the basic processes of fetching data from a remote database?

Thank you for any and all comments, suggestions, and assistance.

[Ecniv]

ADO?

Open a connection to the remote db
Open a recordset (small) and loop through as though in access.

Only things to remember are
- that Orcale Sql statements are slightly different;
- huge recordsets kill access (runs out of virtual memory - I know I tried);
- You have to have at least read only rights to the tables you want to look at

[Webtest]

Vince:

Thanks for your comments.

ADO?

There are so many acronyms out there that I am totally confused: ADO, ADODB, ADOX, JRO, ODBC, DAO, OLEDB, etc, etc, etc. Gads, All I want to do is to put the results of an ODBC Pass-Through Query into a Table on my local (Client) PC! (No, SQL INTO is not applicable in this situation)

Open a connection to the remote db
Open a recordset (small) and loop through as though in access.

I think I can open a RecordSet (if I can keep the Pass-Through Query working). I have a GUARANTEED small recordset ... one Oracle Server Table only has 4 records! I use that one for testing. Can you please show me a template for the "loop" you are talking about? It would be great if the loop were to store the results of the Query in a 'Table' (an item in the Access "Tables" tab in my database) that I could save and re-open later. I am in Access ('97, 2002-XP)!

Only things to remember are
- that Orcale Sql statements are slightly different;
- huge recordsets kill access (runs out of virtual memory - I know I tried);
- You have to have at least read only rights to the tables you want to look at

I actually have some SQL help from IT Pros who know Oracle really well ... they just don't have a clue about VB or Access. I can now mung my way through the SQL as long as the Pass-Through part doesn't break. Some of the Oracle Server tables are not trivial - maybe ten thousand records - but my Queries, if they work, should only return a couple of hundred or so records. As I mentioned, I have already successfully written STATIC Pass-Through Queries for all 4 of my tables (I need to generate the Queries programmatically) and have pulled and viewed data with them, so I definitely have access to the tables.

Thank you for your gracious assistance. If you have any recommendations for a GOOD Client side book for Access '97, XP it would be very helpful. "Access 2003 Power Programming with VBA" (Taylor/Anderson) is NOT it.

[Webtest]

Vince, Esteemed Forum Participants, Lurkers:
=================================

I got my RecordSet part working ... I can retrieve data from my tables into the Immediate (Debug) Window. Here is the code:

Option Compare Database
Option Explicit
Function JUNKTEST()
    Dim myADOConnect As New Connection
    Dim myRecSet As Recordset
    
    ' Declare and Define the ODBC Connection String and the Oracle Pass-Through Query for the data
    Dim strODBC As String
    Dim strSQL1 As String
    strODBC = "ODBC;DATABASE=DER_ACCESS;UID=myUID;PWD=password;DSN=DER_database_access"
    strSQL1 = "SELECT * FROM der.form_lim_view WHERE prfl_id = 3491;"
    
    ' Open the ADO ODBC Connection into the Oracle Database
    myADOConnect.Open strODBC
    
    ' Define, configure, and open the Recordset for the snapshot of the data in the Oracle table
    Set myRecSet = New ADODB.Recordset
    myRecSet.CursorType = adOpenForwardOnly
    myRecSet.LockType = adLockReadOnly
    myRecSet.Open strSQL1, myADOConnect
    
    ' It's nice to know how many fields we are working with ...
    MsgBox ("Number of Fields: " & myRecSet.Fields.Count)
    
    ' Read all of the available data and display it in the Immediate (Debug) Window
    While myRecSet.EOF <> True
        ' Iterate through each record until EOF is reached
        With myRecSet
            Debug.Print .Fields(0).Value, .Fields(1).Value
            .MoveNext
        End With
    Wend
    
    ' Clean up everything
    myRecSet.Close
    myADOConnect.Close
    Set myRecSet = Nothing
    Set myADOConnect = Nothing
    
End Function

Now ... How do I get the RecordSet into a Table in the Access Tables Tab? I can create a table, but how do I load it? I've been playing with the DoCmd.TransferDatabase, but I can't get it to work ... it seems to require a table, but doesn't use the one I provide. It bumps my "TEST_TABLE" parameter to "TEST_TABLE1" but can't find it!

[Ecniv]

Ok... depending on the data you are transferring (I assume a sort of extract?) depends on how you can do it.

If you have tables linked in Access to Oracle, you can use the Query builder in Access to Make Table (new table) or Insert Into an existing Access table.

If you are connecting via ADO (ADODB) and it is possible you are transferring a fair amount of data, you can use the loop you built (you can always tweak and change bits once it is working to get a little more efficient or display to the user a percentage bar etc..) to loop through the Oracle tables and write into the Access (extract) table.
To do this, open a recordset to the currentproject; this is in one variable.
Open the query you made to Oracle, and get a recordset.
Loop through the returns from the oracle database, adding a new record to the Access database and putting the data into it. .Update the access record before moving to the next Oracle record.

Example in your code:

'---- declare this at the top of the sub
dim rstExtract as new ADODB.Recordset
dim strSql as string
dim lngFlds as long, lngFld as long

strsql = "Select * from tblExtract"
rstExtract.open strsql,currentproject.connection,2,3,1

lngFlds = myRecSet.fields.count
While myRecSet.EOF <> True
      rstExtract.addnew

'---- this loop assumes that the table extract fields match and that the table
'---- extracts has a primary ID field (first field) on autoincrement
'---- Recordsets act like arrays and start at position 0
      for lngFld = 1 to lngFlds
          rstExtract(lngfld) = myRecSet(lngfld-1)
      next

'---- save the extracted record into Access
      rstExtract.update

'---- Iterate through each record until EOF is reached
        myRecSet.movenext
    Wend

Something like that, but it is up to you...
Read up on various parts mentioned and you decide which you would prefer.

[Webtest]

Thanks Vince ...

I found an old thread with a message from DaveBo that was the perfect answer to my underlying problem ("Table from Recordset" http://www.vbforums.com/showthread.php?t=263113).

1) Make a connection to the Oracle data base as a RecordSet.
2) Create and append an Access Table with the correct exact field structure in the local (Client) Database.
3) Loop through the returned Oracle Records fetching the desired fields and parsing them into an Access SQL Text String "INSERT INTO test_table VALUES(val1, val2, etc.)". Do a "myDatabase.Execute SQLstring" to load the data values into the local table.
4) Close everything out and clean house.

I still don't have any significant understanding of "Workspace" vs. Database. I guess I'll get the motivation to look into it more when I find a good book or need to use it.

Thanks for your gracious assistance.