Seems to be a reference problem, but .... (1 Viewer)

[Mr. B]

First I would like to thank the entire community here on the forums for welcoming me to the forums a few weeks back. I have really enjoyed being a part of this group. I hope that I have actally help some with their problems.

Now, I have something that I just cannot seem to resolve.

For the project in question, I am using Access 2002, with SP3 (Office 2003) a on WindowsXP machine. (I do have Access 2007 installed on the same machine.)

Here is a little background:
I have been modifying an existing database appliction for a client. As part of the updating, the decision was made to change the application to a split (fron-end/backend) application. We have tested all of the the modifications that we made to the Db and all tested out with out errors and performs as expected. However, now after the splitting of the applicatino, a module that is called from a form that we did not touch as far as any changes, now will not run in the new database.

I have been thinking that this is a "references" issue, however that just does not seem to be anything that would cause the problem. There are two new references (one to Excel and the other to the Office library) but I just can't bring myself to believe that these two new references could be causing the problem.

Here is the code that runs as expected in the original database but does not run in the new Db:

Dim rs As Recordset, rsNumsReq As Recordset
Set rs = Gdb.OpenRecordset("MonthlyNumbers", dbOpenTable)
rs.Index = "UniqueIDX"

This code is in a Module and there is more to the code but this is the area were execution stops.

I have attached two screen captures from the two databases of the references in each.

Does anyone have a clue as to why this code would no longer run?

If more info is need, just let me know.

Thanks in advance for any thoughts and resolutions.

 

[pbaldy]

Try dbOpenDynaset instead of dbOpenTable.

 

[Mr. B]

Paul,

Thanks for the quick response. I have tried replacing the dbOpenTable with the dbOpenDynaset and when I do that the code will advance to the next line which is where it tries to set the index. It stops there and says that there is not a property like that.

It is beginning to look like the problem lies in the fact that originally the tables were all native to the original Db. Now they are split and the reference to opening the Table, which has the index property available, cannot be use with linked tables.

I did not write this code, but now it is beginning to look like I may have to re-write it.

 

[SOS]

Change this:
Dim rs As Recordset, rsNumsReq As Recordset

to this

Dim rs As DAO.Recordset, rsNumsReq As DAO.Recordset

 

[Mr. B]

SOS,

Thanks for the effort.

I have already tried changing over to the declaration of DAO as the recordset. However the next line is then where the code stops. It is trying to set the index which is not a property available through DAO so it errors out. The Index is needed in code below this code where a "seek" is used to look for a "No Match".

 

[pbaldy]

From help:

You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.

I'd be curious what the overall process is. Generally you'd be better off just opening your recordset on an SQL statement that only returned the needed record(s), rather than opening it on the whole table and then trying to find the needed record.

 

[SOS]

SOS,

Thanks for the effort.

I have already tried changing over to the declaration of DAO as the recordset. However the next line is then where the code stops. It is trying to set the index which is not a property available through DAO so it errors out. The Index is needed in code below this code where a "seek" is used to look for a "No Match".

Well, first of all, I can see that .INDEX is available but it is not necessarily used in the way you think. See this from the help file:

' Index Property Example
' This example uses the Index property to set different record orders for a table-type Recordset.
Sub IndexPropertyX()
    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim rstEmployees As Recordset
    Dim idxLoop As Index
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
    dbsNorthwind.OpenRecordset("Employees")
    Set tdfEmployees = dbsNorthwind.TableDefs!Employees
    With rstEmployees
        ' Enumerate Indexes collection of Employees table.
        For Each idxLoop In tdfEmployees.Indexes
            .Index = idxLoop.Name
            Debug.Print "Index = " & .Index
            Debug.Print "  EmployeeID - PostalCode - Name"
            .MoveFirst
            ' Enumerate Recordset to show the order of records.
            Do While Not .EOF
                Debug.Print "    " & !EmployeeID & " - " & _
                            !PostalCode & " - " & !FirstName & " " & _
                            !LastName
                .MoveNext
            Loop
        Next idxLoop
        .Close
    End With
    dbsNorthwind.Close
End Sub

 

[Mr. B]

SOS,

There is evidently a totally different set of rules when it comes to Linked Tables. It just plain will not set the index when the tables are linked.

 

[gemma-the-husky]

if you don;t want to open the back end directly
(which you do with the dbengine(0).workspaces syntax - i know that isnt correct without checking) heres a couple of alternative ideas

------------
use a query

prepare a query sorted in the order you want

then just iterate the query in sequence.

-------
use find methods

the other thing is, although you can't use seek methods, you CAN use find methods - these are a bit slower, but probably not an issue for smaller datasets

 

[Mr. B]

Yes, as I said earlier, I did not write this code, but I am going to have to re-write some of this because due to splitting the Db it just will not work as designed.

In this case, I plan to use the FindFirst method with my DAO recordset.

Thanks to all of you who have offered solutions and suggestions. I really appreciate you guys.

 

[SOS]

I really appreciate you guys.

As you know - we try. We aren't always successful, but we try.

 

[pbaldy]

Still wondering if this is relevant:

I'd be curious what the overall process is. Generally you'd be better off just opening your recordset on an SQL statement that only returned the needed record(s), rather than opening it on the whole table and then trying to find the needed record.

 

[Mr. B]

Paul and all,

Progress report! Bottom line is: Got it fixed. YEAH!

First, I was not aware of the restriction inposed on using "Seek" on linked tables. This situation arose when my client test a specific function (evidently and hopefully the only one in the application) after we had split the database. I could not figure out why splitting the database had just a drastic impact on this function.

Solution was to set variable refs to DAO recordsets. (The table that was being set to a recordset was opened only for adding a new record.) I replaced the "Seek" method with the FindFirst method and the used the "No Match" to check to see if a record existed and if not, write the new record. This entire process takes about 20 - 25 seconds to complete as it is iterating through a record set returned by dates criteria in a query. (Also added a progress bar to the form to help users to see that there was activity.)

I think I was just concerned about changing the "Seek" statement and making sure that I included all of the parameters that were included in the original statement. I have not used the "Seek" method before as I have always tried to use DAO and was just nervous about starting to make changes to what is a very complicated process. Time constraints are in place and I just did not want to hit the wall.

Once again I want to than all of you for your assitance. The ones that worked and the ones that didn't. I really learned something on this one.