-
Jul 18th, 2005, 01:37 PM
#1
Thread Starter
Frenzied Member
ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset [RESOLVED]
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.
Last edited by Webtest; Jul 22nd, 2005 at 11:52 AM.
Reason: Title Clarification
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
-
Jul 20th, 2005, 03:58 AM
#2
Re: ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset ???
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
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jul 20th, 2005, 07:40 AM
#3
Thread Starter
Frenzied Member
Re: ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset ???
Vince:
Thanks for your comments.
Originally Posted by Ecniv
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)
Originally Posted by Ecniv
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)!
Originally Posted by Ecniv
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.
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
-
Jul 20th, 2005, 12:50 PM
#4
Thread Starter
Frenzied Member
Re: ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset ???
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:
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!
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
-
Jul 22nd, 2005, 04:18 AM
#5
Re: ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset ???
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:
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.
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jul 22nd, 2005, 11:35 AM
#6
Thread Starter
Frenzied Member
Re: ACCESS: Newbie confusion - db/table vs. workspace/connect/recordset ???
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.
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|