New to ADO...please help 1

[itchyII]

Hello everyone,
I have to write some code to fix a few problems with some data in one of our databases. The database is a DB2 database that I have connected to in Access 2000(through ODBC linked tables). I am very familiar with DAO, and could probably accomplish what I need with it, but I thought, heck, its summer, no one's around and I have some time on this one, so why not start to learn ADO? I did a little research but my Access help files aren't working (that's another issue!). I searched around on this forum but I think that I am missing one basic concept. What I want to do is simply open an access query that already exists and read data from it, open one of the linked tables, search through it to find the matching records, update a few fields, and then close everything.
Now I've seen alot about how to connect to tables in other db's but the table I want to open is already linked in my db, so do I still have to 'connect' to it? If so, how?

Your guidance is appreciated!

ItchyII

 

[RDWilson2]

Think about this one for a minute in a more generic form.

If you had ONLY Access tables that you were working with, would you be able to accomplish your task?

If so, then ignore the origin of the linked tables and do what you would do if this were a pure Access issue.

If not, then THAT is where the problem lies, not in the origin of the linked tables.

 

[cmmrfrds]

If you are going to use ADO, you might as well get accustomed to the ADOX library too. With ADOX you can manipulate the Catalog (queries etc..) and Security.

Dim cn As New ADODB.Connection, rs as New ADODB.Recordset
''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim vn As View
Set vn = cg.Views("query1")
Set cn = CurrentProject.Connection
rs.Open vn.Name, cn, adOpenForwardOnly, adLockReadOnly
Set cn = Nothing

To Open a linked table you can use the currentproject.connection. Although a direct OLEDB Connection through ADO will be more efficient since you only need to retrieve the data that you are interested in.

 

[PHV]

my Access help files aren't working
Search your computer for ado*.chm.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[itchyII]

Thanks for the information!

PHV, I found the files, thank you.

Here is where I am. I started a little function just to test out my connections and verify that I am pulling the right data. So far, it looks good, but I am having a problem sorting my recordset. I am getting an error that says

Current provider does not support the necessary interfaces for sorting or filtering

. Here is my code:

Dim cn As New ADODB.Connection
Dim hist As New ADODB.Recordset
Dim query As New ADODB.Recordset
Dim cg As New ADOX.Catalog
Dim vn As View
Dim x As Integer

Set cg.ActiveConnection = CurrentProject.Connection
Set vn = cg.Views("qryImportedClosed_with_cust_descr")
Set cn = CurrentProject.Connection
query.Open vn.Name, cn, adOpenDynamic, adLockPessimistic

query.MoveFirst
Do While Not query.EOF
    MsgBox query!C5707_ISS_ID
    query.MoveNext
Loop

hist.Open "SUAMIL_T5708_ISS_HISTORY", cn, adOpenDynamic, adLockPessimistic
hist.MoveFirst

hist.Sort = "C5707_ISS_ID DESC"

x = 0
Do While Not x = 5
    MsgBox hist!C5707_ISS_ID
    x = x + 1
    hist.MoveNext
Loop

Set cn = Nothing
Set cg = Nothing
Set query = Nothing
Set hist = Nothing
Set vn = Nothing

Thanks for the help!

ItchyII

 

[cmmrfrds]

What version of ADO is referenced? Should be 2.6 or above.

Another thing is to try a static cursor, I don't see the need to a dynamic cursor in this instance, or make the cursor location client side and this will coerce a static cursor. I think the filter and sort only apply to static cursors.
hist.CursorLocation = adUseClient

hist.Open "SUAMIL_T5708_ISS_HISTORY", cn, adOpenStatic, adLockPessimistic
hist.MoveFirst

hist.Sort = "C5707_ISS_ID DESC"

 

[itchyII]

Thanks cmmrfrds! That did the trick.

Well, I sucessfully completed three functions using ADO and they work perfectly! Will be running them in production next week!

Woo hoo!

ItchyII

 

[cmmrfrds]

Congratulations! It is always fun to get something new working.