J
Jay Chan
I kept getting the following error when I used
CurrentProject.Connection to add a record to a table that happens to
be in one of the table in a two-tables joined data source in a
not-quite-related list-box:
-2147217887
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available.
No work was done.
What I am trying to do is trying to add a new entry to a list box
using code. The database server will automatically assigns a ID to the
new entry. I want to get the value of the new entry ID.
The ways that I go about doing this are:
- The form has a unbound list box, let's call the form frmItemInfo,
and call the list box lstItemTypes.
- The lstItemTypes list box should show a list of item-types that
has any item belongs to that item type and those items have not been
fabricated.
- I use code to populate the unbound list box using something like:
sRowSrc = "select distinct tb1.TypeID, tb1.TypeName " & _
"from ( tbItemTypes as tb1 " & _
"left outer join tbItems as tb2 on " & _
" tb1.TypeID = tb2.TypeID ) " & _
"where tb2.IsFabricated is null or " & _
" tb2.IsFabricated = 0 "
Me("lstItemTypes").RowSource = sRowSrc
Me("lstItemTypes").Requery
Please note that the query is a two-tables join.
This seems to have a lot to do with the error.
- I use code to add a new item to the table "tbItemTypes", like
this:
'Create a record-set to the table.
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseServer
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Source = "tbItemTypes"
'Add a new entry to the table.
'The database server will automatically assign
'a ID to the new entry using newid(). Therefore,
'we don't need to explicitly assign the ID here.
rst.Open
rst.AddNew
rst.Fields("TypeName") = "Type 123"
rst.Update
rst.Close
'Find the ID of the newly added item-type that the
'database server has assigned for us.
rst.Open
rst.Filter = "TypeName = 'Type 123' and <..other condition> "
vNewID = rst.Fields("TypeID")
rst.Close
Set rst = Nothing
- The program will be able to add a new entry to the table. But I
got the error message when I try to use the Filter function to
retrieve the ID of the new entry. Strangely, this error doesn't seem
to affect the operation of the program. The program will work as
normal except for getting the error message and not getting the ID.
Possible Causes and Workaround:
- I have kind of identified the problem has to do with the fact that
the data source of the list box is a two-tables-join. If I change the
data source to refer to one table "tbItemTypes", I will not get that
error. This is strange. I thought CurrentProject.Connection is like
CurrentDb, and it should not have been affected by the record-set
being used by another object. What is going on here? What's
CurrentProject.Connection referring to anyway?
- I get around with the problem by explicitly creating a connection
using connection string, and use that connection to create the new
entry. Why this works?
Thanks for any info on this problem.
Jay Chan
CurrentProject.Connection to add a record to a table that happens to
be in one of the table in a two-tables joined data source in a
not-quite-related list-box:
-2147217887
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available.
No work was done.
What I am trying to do is trying to add a new entry to a list box
using code. The database server will automatically assigns a ID to the
new entry. I want to get the value of the new entry ID.
The ways that I go about doing this are:
- The form has a unbound list box, let's call the form frmItemInfo,
and call the list box lstItemTypes.
- The lstItemTypes list box should show a list of item-types that
has any item belongs to that item type and those items have not been
fabricated.
- I use code to populate the unbound list box using something like:
sRowSrc = "select distinct tb1.TypeID, tb1.TypeName " & _
"from ( tbItemTypes as tb1 " & _
"left outer join tbItems as tb2 on " & _
" tb1.TypeID = tb2.TypeID ) " & _
"where tb2.IsFabricated is null or " & _
" tb2.IsFabricated = 0 "
Me("lstItemTypes").RowSource = sRowSrc
Me("lstItemTypes").Requery
Please note that the query is a two-tables join.
This seems to have a lot to do with the error.
- I use code to add a new item to the table "tbItemTypes", like
this:
'Create a record-set to the table.
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseServer
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Source = "tbItemTypes"
'Add a new entry to the table.
'The database server will automatically assign
'a ID to the new entry using newid(). Therefore,
'we don't need to explicitly assign the ID here.
rst.Open
rst.AddNew
rst.Fields("TypeName") = "Type 123"
rst.Update
rst.Close
'Find the ID of the newly added item-type that the
'database server has assigned for us.
rst.Open
rst.Filter = "TypeName = 'Type 123' and <..other condition> "
vNewID = rst.Fields("TypeID")
rst.Close
Set rst = Nothing
- The program will be able to add a new entry to the table. But I
got the error message when I try to use the Filter function to
retrieve the ID of the new entry. Strangely, this error doesn't seem
to affect the operation of the program. The program will work as
normal except for getting the error message and not getting the ID.
Possible Causes and Workaround:
- I have kind of identified the problem has to do with the fact that
the data source of the list box is a two-tables-join. If I change the
data source to refer to one table "tbItemTypes", I will not get that
error. This is strange. I thought CurrentProject.Connection is like
CurrentDb, and it should not have been affected by the record-set
being used by another object. What is going on here? What's
CurrentProject.Connection referring to anyway?
- I get around with the problem by explicitly creating a connection
using connection string, and use that connection to create the new
entry. Why this works?
Thanks for any info on this problem.
Jay Chan