Error finding index with ADO connection

[rbain]

I have a form which I am using to move some records from the office inventory
to the warehouse inventory. Each inventory has two tables (tblAuditBox,
tblAuditRecords) and (tblAuditOfficeBox, tblAuditOfficeRecords).

My form is designed to allow the user to select records from a combo box,
supply a new box number, specify a record type and then to create the new box
and fill it with the selected records.

When I click the btn_Move I get a run time error: -2147217867 (80040e35)
'RecordID' is not an index in this table. Debug highligts the line that
creates the index:
rstAuditOffice.Index = "RecordID"

I read kba: 287459, but I'm still having the same error message. I have
verified ADOX 2.1 is checked in references.

The function ValidateBoxNum() is a custom function that checks to see if the
new box number already exists in the tblAuditBox and returns a Boolean value.

Would appreciate any help. Code for btn_Move is listed below:

Private Sub btnMove_Click()
Dim rstAuditOffice As New ADODB.Recordset
Dim rstNewBox As New ADODB.Recordset
Dim intNumSelected As Integer
Dim conn As ADODB.Connection
Dim varItm As Variant
Dim strSQLInsertBox As String
Dim intRecordType As Integer
Dim intBoxID As Integer
Dim intBoxAutoID As Integer

intBoxID = CInt(Me!txtNewBoxNum.Value)
intRecordType = Me!intRecordType.Value

'Define connection
Set conn = CurrentProject.Connection
'Debug.Print conn.ConnectionString

If ValidateBoxNum(txtNewBoxNum.Value) Then
'Build SQL statement to add a new box
strSQLInsertBox = "INSERT INTO tblAuditBox (BoxID,DepartmentID,TypeID)"
strSQLInsertBox = strSQLInsertBox & " VALUES (" & intBoxID & ",6," &
intRecordType & ");"

'Add new box
conn.Execute strSQLInsertBox

rstNewBox.Open "Select Max(BoxAuto) as MaxAuto from tblAuditBox", conn
intBoxAutoID = rstNewBox("MaxAuto")
Set rstNewBox = Nothing

'Verify that items are selected
intNumSelected = Me.list_office_records.ItemsSelected.Count
If Nz(intNumSelected) = 0 Then
MsgBox ("You must select at least one record.")
Exit Sub
End If

'Open tblAuditRecordsOffice
rstAuditOffice.Open "tblAuditRecordsOffice", conn, adOpenKeyset,
adLockOptimistic, _
adCmdTableDirect

'Begin transaction
conn.BeginTrans
'******
'
rstAuditOffice.Index = "RecordID"
'
'******

For Each varItm In Me.list_office_records.ItemsSelected
'Find record in tblAuditRecordsOffice
rstAuditOffice.Seek list_office_records.ItemData(varItm)

'Update it with new box number and auto box number
rstAuditOffice!BoxID = intBoxID
rstAuditOffice!AutoBox = intBoxAutoID
rstAuditOffice.Update

Next varItm

conn.CommitTrans

Else
txtNewBoxNum.SetFocus
btnMove.Enabled = False
End If
End Sub

Thanks,
RCB

 

[Dirk Goldgar]

I have a form which I am using to move some records from the office
inventory to the warehouse inventory. Each inventory has two tables
(tblAuditBox, tblAuditRecords) and (tblAuditOfficeBox,
tblAuditOfficeRecords).

My form is designed to allow the user to select records from a combo
box, supply a new box number, specify a record type and then to
create the new box and fill it with the selected records.

When I click the btn_Move I get a run time error: -2147217867
(80040e35) 'RecordID' is not an index in this table. Debug highligts
the line that creates the index:
rstAuditOffice.Index = "RecordID"

I read kba: 287459, but I'm still having the same error message. I
have verified ADOX 2.1 is checked in references.

The function ValidateBoxNum() is a custom function that checks to see
if the new box number already exists in the tblAuditBox and returns a
Boolean value.

Would appreciate any help. Code for btn_Move is listed below:

Private Sub btnMove_Click()
Dim rstAuditOffice As New ADODB.Recordset
Dim rstNewBox As New ADODB.Recordset
Dim intNumSelected As Integer
Dim conn As ADODB.Connection
Dim varItm As Variant
Dim strSQLInsertBox As String
Dim intRecordType As Integer
Dim intBoxID As Integer
Dim intBoxAutoID As Integer

intBoxID = CInt(Me!txtNewBoxNum.Value)
intRecordType = Me!intRecordType.Value

'Define connection
Set conn = CurrentProject.Connection
'Debug.Print conn.ConnectionString

If ValidateBoxNum(txtNewBoxNum.Value) Then
'Build SQL statement to add a new box
strSQLInsertBox = "INSERT INTO tblAuditBox
(BoxID,DepartmentID,TypeID)" strSQLInsertBox = strSQLInsertBox &
" VALUES (" & intBoxID & ",6," & intRecordType & ");"

'Add new box
conn.Execute strSQLInsertBox

rstNewBox.Open "Select Max(BoxAuto) as MaxAuto from tblAuditBox",
conn intBoxAutoID = rstNewBox("MaxAuto")
Set rstNewBox = Nothing

'Verify that items are selected
intNumSelected = Me.list_office_records.ItemsSelected.Count
If Nz(intNumSelected) = 0 Then
MsgBox ("You must select at least one record.")
Exit Sub
End If

'Open tblAuditRecordsOffice
rstAuditOffice.Open "tblAuditRecordsOffice", conn, adOpenKeyset,
adLockOptimistic, _
adCmdTableDirect

'Begin transaction
conn.BeginTrans
'******
'
rstAuditOffice.Index = "RecordID"
'
'******

For Each varItm In Me.list_office_records.ItemsSelected
'Find record in tblAuditRecordsOffice
rstAuditOffice.Seek list_office_records.ItemData(varItm)

'Update it with new box number and auto box number
rstAuditOffice!BoxID = intBoxID
rstAuditOffice!AutoBox = intBoxAutoID
rstAuditOffice.Update

Next varItm

conn.CommitTrans

Else
txtNewBoxNum.SetFocus
btnMove.Enabled = False
End If
End Sub

Thanks,
RCB

Have you actually verified, by examining the indexes defined for the
table rstAuditOffice, that there is really an index named "RecordID"?
If, when you designed the table, you created the index by selecting the
field and clicking the "key" toolbar button to make that field the
primary key, then the index on that field is probably actually named
"PrimaryKey".

 

[rbain]

Dirk,

You're a genuis! Sure enough, my index was named PrimaryKey. I changed the
name and that portion of my code worked like a charm. Then I found out there
was another little twist I hadn't thought of. I've got to delete the existing
relationship between the office inventory set of tables (tblAuditOfficeBox,
tblAuditOfficeRecords) before I can update the rstAuditOffice recordset. And
then, I'll need to rebuild it. Looks like I'll have to turn to DAO for that,
n'cest pas? (I've got the relationship deleted , but I'm struggling a bit
with rebuilding it. But I had to leave before I could work out that little
kink.) Maybe I can go in early this morning and figure it out.

Hopefully, thanks to your kind help, my code is entering its final stages.

Thanks again,
Robert Bain

 

[Dirk Goldgar]

Dirk,

You're a genuis! Sure enough, my index was named PrimaryKey. I
changed the name and that portion of my code worked like a charm.
Then I found out there was another little twist I hadn't thought of.
I've got to delete the existing relationship between the office
inventory set of tables (tblAuditOfficeBox, tblAuditOfficeRecords)
before I can update the rstAuditOffice recordset. And then, I'll need
to rebuild it. Looks like I'll have to turn to DAO for that, n'cest
pas? (I've got the relationship deleted , but I'm struggling a bit
with rebuilding it. But I had to leave before I could work out that
little kink.) Maybe I can go in early this morning and figure it out.

Hopefully, thanks to your kind help, my code is entering its final
stages.

Thanks again,
Robert Bain

I'm very suspicious of any solution that requires you to delete and
recreate relationships on the fly like that. Would you be interested in
explaining why you need to do this?

 

[rbain]

Dirk,

I'm not sure I have a good reason to do it, I'm sure there's a better way,
and I'm willing to try it.

As I mentioned in my first post, I'm dealing with 4 tables. The tables
contain information about files for our audit department. The warehouse
tables (tblAuditBox and tblAuditRecords) have a parent-child relationship
with referential integrity enforced. The office tables (tblAuditBoxOffice and
tblAuditRecordsOffice) also have a parent child relationship. The records in
the office are eventually boxed up and shipped to the warehouse. I want to
take the information from the tblAuditRecordsOffice table, select some
records, put 'em in a box and then move them to the warehouse (tblAuditBox
and tblAuditRecords). There is no particular criteria by which a selection
can be made of the office records (whatever will fit in the box, sometimes).

The box table truthfully has no purpose in the Office set of tables. I just
copied the table structure and the forms over and added a field or two to the
records table so that they could keep track of who had the file.

The steps I was trying at first were to
Select the records to move
Get a new box number
Check to see if the box number already exists
Add the new box to the warehouse box table
Append selected records to warehouse record table
Delete selected records from office record table
Open the warehouse form on the new record (there is function built into that
frm that will print the box labels)

I'm open to your suggestions.

My goals are to make this easy for the user to do, avoid any rekeying and
ensure that the box is properly labeled with what's in the box.

Thanks for your help.

RCB

 

[Dirk Goldgar]

Dirk,

I'm not sure I have a good reason to do it, I'm sure there's a better
way, and I'm willing to try it.

As I mentioned in my first post, I'm dealing with 4 tables. The tables
contain information about files for our audit department. The
warehouse tables (tblAuditBox and tblAuditRecords) have a
parent-child relationship with referential integrity enforced. The
office tables (tblAuditBoxOffice and tblAuditRecordsOffice) also have
a parent child relationship. The records in the office are eventually
boxed up and shipped to the warehouse. I want to take the information
from the tblAuditRecordsOffice table, select some records, put 'em in
a box and then move them to the warehouse (tblAuditBox and
tblAuditRecords). There is no particular criteria by which a
selection can be made of the office records (whatever will fit in the
box, sometimes).

The box table truthfully has no purpose in the Office set of tables.
I just copied the table structure and the forms over and added a
field or two to the records table so that they could keep track of
who had the file.

The steps I was trying at first were to
Select the records to move
Get a new box number
Check to see if the box number already exists
Add the new box to the warehouse box table
Append selected records to warehouse record table
Delete selected records from office record table
Open the warehouse form on the new record (there is function built
into that frm that will print the box labels)

I'm open to your suggestions.

My goals are to make this easy for the user to do, avoid any rekeying
and ensure that the box is properly labeled with what's in the box.

I don't see anything in the process you describe that would require you
to delete and recreate a relationship. If you actually carry out the
operations in the sequence you gave, referential integrity will never be
violated, except that you don't mention creating a record in
tblAuditBoxOffice, which you would have to do in order to create a
record in tblAuditBoxRecords in the first place. However, you also say
"The box table truthfully has no purpose in the Office set of tables,"
so I'm not sure that this table needs to exist at all.

It seems to me that the data model may bear further examination, though.
If you don't mind, I'd like to come to a clearer understanding of what
it is you are trying to do with these tables. This could be a little
confusing because one of the entities being modeled is called a
"Record", and yet we also talk about records in tables. For the
purposes of the following discussion, I'll refer to the entity being
modeled as an AuditRecord, so if I just use the term "record" I'm
referring to a record in a table.

It seems to me -- and please correct me if I'm wrong -- that you are
trying to track the location of certain physical AuditRecords. If I
understand what you've posted, either an AuditRecord is in the office
(and not in a "Box"), or it is in the warehouse in a particular Box, or
it is in the process of being placed in a particular Box to be sent to
the warehouse. So then, an AuditRecord may or may not be in a Box, and
a Box may or may not be in the warehouse. If a Box is not in the
warehouse, then I assume it's currently in the office being filled, or
else en route to the warehouse. The record representing a Box would
presumably have some sort of status field to indicate this.

Functionally, I'd think you would want to be able to look up an
AuditRecord and find out (a) if it's in a Box or not, and (b) if it *is*
in a Box, where that Box currently is. I understand that you also want
to be able to select AuditRecords and mark them as being in a particular
Box, thereby modelling the process of physically pulling files and
putting them into a box, preparatory to sending the box off to the
warehouse.

If all this that I've speculated so far is true, then I think you really
only need two tables: AuditRecords and Boxes. AuditRecords would have
a many-to-one relationship with Boxes, using a BoxNumber field as the
foreign key in AuditRecords related to the primary key BoxNumber in
Boxes. BUT -- and this is essential to the scheme -- you would allow
the foreign key field BoxNumber in table AuditRecords to be Null. It
would not be a required field. That way, any AuditRecord that doesn't
have a BoxNumber assigned to it would by implication not be in a box --
thus, it's in the office files. Any AuditRecord that does have a
non-Null BoxNumber is in that box (and therefore the corresponding Box
record must exist in table Boxes). The Status field in the
corresponding Box record indicates where that box is at the moment.

Your process of "putting AuditRecords into a new box" would then have
the following steps:

1. Select a subset of the AuditRecords that are not currently in any box
(i.e., they have a Null BoxNumber).

2. Get a new box number (by some process I don't know) and create a new
Box record with that BoxNumber and its Status field set to indicate
"loading" or something along those lines.

3. Put that BoxNumber into the BoxNumber field in all the AuditRecords
that are supposed to be going into that box. This would probably be
done by an update query.

4. Call a function to print the box labels, or open a Boxes form (your
"warehouse form"?) that contains a function to print the box labels.

And that's it. Note that we are never moving records from one table to
another, just modifying a field in the AuditRecords table.

Presumably you'd have a form based on table Boxes with a subform based
on table AuditRecords, so that you can see at a glance what AuditRecords
are in any given Box, and your form for viewing AuditRecords would show
you the BoxNumber for any AuditRecord that is in a box. You could set
that up so that double-clicking on the BoxNumber opens the Boxes form
for the corresponding box, enabling you to see just where that box is.
Or, if you wanted the AuditRecords form *could* be based on a query that
left-joins AuditRecords to Boxes so that you can see on that form not
only whether an AuditRecord is in a box, and if so what box, but also
what the status of that box is.

All of the above, of course, is based solely on my interpretation of
what it is you are trying to do. So I could be wildly wrong about the
whole thing. If so, I'm sorry, but I hope that at least it gives you
some ideas.

 

[Robert Bain]

Dirk,

You've been a great deal of help so far. Thanks for taking the time to help
me through this.

After your last post, I went back and made some changes based on your
recommendations. I deleted the tblAuditBoxOffice since it served no real
purpose. I did keep the table that stores information on the audit files
(tblAuditRecordsOffice) because the security requirements for it are
different than for the warehouse table (tblAuditRecords).

I was able to get my coding to work in a test environment, but when I moved
it into production I encountered some errors.

My test environment has the same references, but the forms and tables were
in the same .mdb file. Also, the production environment is secured using user
level security.

Initially I received an error that indicated that my provider did not
support using an index (run-time error '3251'). I researched that error and
then tried establishing my ADO connection directly to the back-end.

Now I'm encountering a different run-time error: '-2147217843 (80040e4d)'.
Cannot start your application. The workgroup information file is missing or
opened exclusively by another user.

Here is my code as it stands now:

Private Sub btnMove_Click()
Dim rstAuditOffice As New ADODB.Recordset
Dim rstNewBox As New ADODB.Recordset
Dim intNumSelected As Integer
Dim conn As New ADODB.Connection
Dim strUsr As String
Dim strPwd As String
Dim strDBName As String
Dim varItm As Variant
Dim strSQLInsertBox As String
Dim strSQLInsertRecords As String
Dim strSQLDeleteRecords As String
Dim intRecordType As Integer
Dim intBoxID As Integer
Dim intBoxAutoID As Integer
Dim strRecordFilter As String

intBoxID = CInt(Me!txtNewBoxNum.Value)
intRecordType = Me!intRecordType.Value

'Define connection
strUsr = "rbain"
strPwd = "fire&ice4u"
strDBName =
"\\Blue7\SYS1\SHARE\MEDICARE\Medshare\TRAINING\RCRDRET\recret_be.mdb"
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=strDBName; User ID=strUsr;
Password=strPwd"
.Open
End With

'Set conn = CurrentProject.Connection


If ValidateBoxNum(txtNewBoxNum.Value) Then
'Build SQL statement to add a new box
strSQLInsertBox = "INSERT INTO tblAuditBox (BoxID,DepartmentID,TypeID)"
strSQLInsertBox = strSQLInsertBox & " VALUES (" & intBoxID & ",6," &
intRecordType & ");"

'Add new box
conn.Execute strSQLInsertBox

rstNewBox.Open "Select Max(BoxAuto) as MaxAuto from tblAuditBox", conn
intBoxAutoID = rstNewBox("MaxAuto")
Set rstNewBox = Nothing

'Verify that items are selected
intNumSelected = Me.list_office_records.ItemsSelected.Count
If Nz(intNumSelected) = 0 Then
MsgBox ("You must select at least one record.")
Exit Sub
End If

'Open tblAuditRecordsOffice
rstAuditOffice.Open "tblAuditRecordsOffice", conn, adOpenKeyset,
adLockOptimistic, _
adCmdTableDirect


'Begin transaction
conn.BeginTrans
'******
'
rstAuditOffice.Index = "RecordID"
'
'******

For Each varItm In Me.list_office_records.ItemsSelected
'Find record in tblAuditRecordsOffice
rstAuditOffice.Seek list_office_records.ItemData(varItm)

'Update it with new box number and auto box number
rstAuditOffice!BoxID = intBoxID
rstAuditOffice!BoxAuto = intBoxAutoID
' rstAuditOffice!Row = ""
' rstAuditOffice!rack = ""
' rstAuditOffice!Shelf = ""
rstAuditOffice.Update

Next varItm

conn.CommitTrans
rstAuditOffice.Close


'Insert new records into the tblAuditRecords
strSQLInsertRecords = "Insert into tblAuditRecords Select "
strSQLInsertRecords = strSQLInsertRecords & "tblAuditRecordsOffice.BoxAuto,"
strSQLInsertRecords = strSQLInsertRecords & " tblAuditRecordsOffice.BoxID, "
strSQLInsertRecords = strSQLInsertRecords & "
tblAuditRecordsOffice.ProviderName,"
strSQLInsertRecords = strSQLInsertRecords & " tblAuditRecordsOffice.FYE, "
strSQLInsertRecords = strSQLInsertRecords & "
tblAuditRecordsOffice.DestroyDate, "
strSQLInsertRecords = strSQLInsertRecords & "
tblAuditRecordsOffice.AuditSubTypeNumber"
strSQLInsertRecords = strSQLInsertRecords & " FROM tblAuditRecordsOffice"
strSQLInsertRecords = strSQLInsertRecords & " WHERE
tblAuditRecordsOffice.BoxID= "
strSQLInsertRecords = strSQLInsertRecords & intBoxID
conn.Execute strSQLInsertRecords

'Delete records from tblAuditRecords
strSQLDeleteRecords = "DELETE * FROM tblAuditRecordsOffice WHERE BoxID = "
strSQLDeleteRecords = strSQLDeleteRecords & intBoxID
conn.Execute strSQLDeleteRecords

'Clear list box and other controls
For Each varItm In Me.list_office_records.ItemsSelected
list_office_records.Selected(varItm) = False
Next varItm
Me!intRecordType.Value = Null
Me!txtNewBoxNum.Value = Null

'Requery combo box
Me!list_office_records.Requery
Me.list_office_records.SetFocus

'Open report
strRecordFilter = "tblAuditBox!BoxID = " & intBoxID
DoCmd.OpenReport "rptAuditSingleWarehouse-Move", acViewPreview, ,
strRecordFilter

Else
txtNewBoxNum.SetFocus
btnMove.Enabled = False
End If
End Sub

 

[Robert Bain]

A few developments since my last post.

I modified my test database by splitting it into a front-end/back-end
database.

I will still have to resolve the security piece. For now I've simplified the
problem to see if I can use the index in a split environment. I'm able to
connect to the back end (I'm using a UNC path in the code, because I'll need
it in the production environment. The code works in that all of the updates
take place. The problem comes in requerying the control
(list_office_records). When I close the report, the records remain in the
form except they are marked Deleted. If I close the form and reopen the form
(X out) the records are gone. It seems that since I updated the back-end
table, the form which is based on the linked table in the front end is not
out of sequence with the back-end. Is there a way to bring the front end and
back end into sync, so that when I requery the control, it will reflect the
changes.

Thanks,
Robert Bain

 

[Dirk Goldgar]

A few developments since my last post.

I modified my test database by splitting it into a front-end/back-end
database.

I will still have to resolve the security piece. For now I've
simplified the problem to see if I can use the index in a split
environment. I'm able to connect to the back end (I'm using a UNC
path in the code, because I'll need it in the production environment.
The code works in that all of the updates take place. The problem
comes in requerying the control (list_office_records). When I close
the report, the records remain in the form except they are marked
Deleted. If I close the form and reopen the form (X out) the records
are gone. It seems that since I updated the back-end table, the form
which is based on the linked table in the front end is not out of
sequence with the back-end. Is there a way to bring the front end and
back end into sync, so that when I requery the control, it will
reflect the changes.

Sorry about the delay in getting back to you on this. I've been busy.

I've seen problems with latency such as you describe, when you use an
independent connection to update the data that Access is also
displaying. Presumably due to caching, it seems to take a while for
Access to become aware that the data has been updated. I don't think I
ever found a good way to get around that, but maybe someone else will
chime in with an answer if you need one.

I don't see, though, why you need to open an independent connection to
accomplish your purpose. The only reason I can see at the moment is
that you want to use the Index property and the Seek method on your
recordset, and there are lots of good ways to avoid doing that. In
fact, it looks to me like building and executing a single update
query -- with a WHERE clause that selects only the records that are
selected in your list box -- would be more efficient than the recordset,
Seek or no Seek.

I don't know if you've found an answer to your security problem or not,
but I also suspect that using CurrentProject.Connection instead of
opening a second connection to the data would also eliminate that.

 

[Robert Bain]

Dirk,

I did effect a solution, although it ended up a little clutzy. (I present
the user with three buttons in turn, one to add the selected records, one to
print the report, and one to refresh the form). It works.

It sounds as if I worked a little to hard at this and overengineered my
solution. I suppose as you suggest a simple update query would have been
sufficient. I guess I could have built the WHERE clause by looping through
the itemsselected collection and then append it to an update and delete
query.

I guess I gained some valuable experience in this, so maybe, I'll be more
efficient next time.

Thanks for all of your help.

RCB