Need help opening ADO recordset.

[Guest]

I have been working on this for 3 days now and I have reasearched and tried
everything I could find.

Because of a bug in DAO (see http://support.microsoft.com/kb/242459/en-us) I
cannot add a foreign key to a table using a query or DAO VBA.

This is a very important part of a database I am developing and it needs to
be done without the user having to enter it.

So I have been forced to try ADO. I regularly use DAO recordsets but this
is my first attempt at ADO. I have tried all I could find on the web and in
books, but I have been unable to open an ADO recordset. Here is my
code--advice is welcomed.

Private Sub cmd1Test_Click
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSql As String
Dim lngHHID As Long
Dim lngPersonID As Long

lngHHID = Me.txtHouseholdID.Value
lngPersonID = Me.cboHHMemberName3.Value

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
' Open the connection.
' With cnn
' .Provider = "Microsoft.Jet.OLEDB.4.0"
' .Open "Data Source=C:\Program Files\CMSS\CMSS_JBC_SPLIT_DB1.mdb"
' End With


strSql = "SELECT PersonID, HouseholdID FROM tblPeople" & _
" Where PersonID = Forms!frmHouseholds!cboHHMemberName3;"

Set rst = New ADODB.Recordset
With rst
.Open Source:=strSql, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

Do While Not .EOF
If .Fields("PersonID").Value = lngPersonID Then
.Fields(HouseholdID).Value = lngHHID
' Save the changes you made to the current record in the
Recordset object.
.Update
.MoveNext
End If
Loop
' Close the Recordset object.
.Close
End With

' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub

As I stepped through the code the variables had the correct values, the
connection was open, but I could not open the recordset. There was an error
at that line every time.

Thanks in advance for your help.
Hunter57

 

[Douglas J. Steele]

Try changing

strSql = "SELECT PersonID, HouseholdID FROM tblPeople" & _
" Where PersonID = Forms!frmHouseholds!cboHHMemberName3;"

to

strSql = "SELECT PersonID, HouseholdID FROM tblPeople" & _
" Where PersonID = " & Forms!frmHouseholds!cboHHMemberName3

assuming PersonID is a numeric field. If it's text, use

strSql = "SELECT PersonID, HouseholdID FROM tblPeople" & _
" Where PersonID = """ & Forms!frmHouseholds!cboHHMemberName3 & """"

(that's three double quotes in front, and four double quotes after)

 

[Jamie Collins]

So I have been forced to try ADO. I regularly use DAO recordsets but this
is my first attempt at ADO. I have tried all I could find on the web and in
books, but I have been unable to open an ADO recordset. Here is my
code <<snipped>>

Regardless of flavour, why use a recordset at all? Why not simply
(aircode):

UPDATE tblPeople
SET HouseholdID = ?
WHERE tblPeople = ?;

You could then use an ADO Command object with Parameters to replace
the ?'s with strongly-typed values; perhaps better to have a stored
proc e.g. (ANSI-92 Query Mode SQL DDL aircode):

CREATE PROCEDURE SetPersonHousehold
(
arg_PersonID INTEGER
arg_HouseholdID INTEGER
)
AS
UPDATE tblPeople
SET HouseholdID = arg_HouseholdID
WHERE tblPeople = arg_PersonID;

Jamie.

--

 

[Guest]

That worked great! Thank you! Sometimes it is those little things that can
drive us nuts! It took me two days to discover that "silent" Access bug in
DAO. And it took me another day to try to figure a way around it.

You have saved me a lot of time and effort.

Gratefully yours,

Patrick Wood

 

[Guest]

Hi Jamie,

Thanks for replying to my post. Believe me I tried every query and SQL
combination that I could. Saved queries, SQL statements, Execute,
dbFailOnError, designating the parameters in VBA before running the
SQL--every attempt failed.

I believe the reason for the failure is there is a "silent" bug in DAO that
does not display an error message when you try to update some records. You
can find the link to the information on that in my first post.

Thanks,

Patrick Wood

 

[Guest]

Hi again,

I thought I would add this to my comments as further explanation.

Please, someone correct me if I am wrong, but I think that Access 2003 (my
version) uses DAO with forms and queries unless you specifically use ADO.
That would explain why both VBA, SQL, and Saved Queries failed--it was a DAO
bug.

My DB is split, I don't know if that had anything to do with the problem or
not. My operating system is MS XP Pro.

Thanks for your input,

Patrick Wood

 

[Jamie Collins]

I tried every query and SQL
combination that I could. Saved queries, SQL statements, Execute,
dbFailOnError, designating the parameters in VBA before running the
SQL--every attempt failed.

What about executing the sql UPDATE statement using ADO...?

Jamie.

--

 

[Guest]

Hi Jamie,

Thanks for the idea. I had not thought of that. My recordset in this case
only consists of one record because I limited it to be so with the WHERE
clause. Since I am only updating one field in one record, it should not make
that much difference in speed which method I use. Plus, I am more familiar
with recordsets than SQL statement with Parameters in VBA. But I will surely
try that method when I have more than one record to update. Thanks for the
idea.

Gratefully yours,
Patrick Wood

 

[Dirk Goldgar]

In

I have been working on this for 3 days now and I have reasearched and
tried everything I could find.

Because of a bug in DAO (see
http://support.microsoft.com/kb/242459/en-us) I cannot add a foreign
key to a table using a query or DAO VBA.

This is a very important part of a database I am developing and it
needs to be done without the user having to enter it.

So I have been forced to try ADO. I regularly use DAO recordsets but
this is my first attempt at ADO. I have tried all I could find on
the web and in books, but I have been unable to open an ADO
recordset. Here is my code--advice is welcomed.

Private Sub cmd1Test_Click
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSql As String
Dim lngHHID As Long
Dim lngPersonID As Long

lngHHID = Me.txtHouseholdID.Value
lngPersonID = Me.cboHHMemberName3.Value

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
' Open the connection.
' With cnn
' .Provider = "Microsoft.Jet.OLEDB.4.0"
' .Open "Data Source=C:\Program
Files\CMSS\CMSS_JBC_SPLIT_DB1.mdb" ' End With


strSql = "SELECT PersonID, HouseholdID FROM tblPeople" & _
" Where PersonID = Forms!frmHouseholds!cboHHMemberName3;"

Set rst = New ADODB.Recordset
With rst
.Open Source:=strSql, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

Do While Not .EOF
If .Fields("PersonID").Value = lngPersonID Then
.Fields(HouseholdID).Value = lngHHID
' Save the changes you made to the current record in
the Recordset object.
.Update
.MoveNext
End If
Loop
' Close the Recordset object.
.Close
End With

' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub

As I stepped through the code the variables had the correct values,
the connection was open, but I could not open the recordset. There
was an error at that line every time.

Thanks in advance for your help.
Hunter57

I see that you've found a resolution to the question you posted, but I'm
puzzled. I don't see how the bug referenced in the KB article you
posted is related to what you're trying to do. Would you be at all
interested in explaining, and maybe showing the DAO-based code you were
executing that was failing silently?

 

[Guest]

Hi,

Thanks for your interest in my problem. I began having problems with ADO
also, and I have been receiving an error message like this:

A problem occured while Microsoft Access was communicating with the OLE
server or ActiveX Control.

I had been having problems with DLookup as well. What had worked before now
no longer worked.

So I searched the net and found a temporary fix, which is to save a copy of
my form, delete the old form and rename the copy with the original name.

I have not yet tested my DAO proceedure to see if that is working. I will
post the results here for the benefit of others.

Here is the code I was using when I first began to have problems:

Private Sub PutHHIDIntblPeople()
If Not IsNull(Me.cboHHMemberName3.Value) Then ' Do nothing if Null
If DCount("HouseholdID", "tblPeople", "PersonID = " &
Me.cboHHMemberName3.Value) = 1 Then
With rst
' Find the record needed
.FindFirst "[PersonID] = " & Me.cboHHMemberName3.Value

' Add the HouseholdID to the appropriate record
If Not .NoMatch Then
.Edit
!HouseholdID = Me.txtHouseholdID.Value
.Update
Else
Call MsgBox("No Matching Record was Found. ", _
vbInformation, " Record Not Found")

End If
End With
End If
End If
End Sub

This procedure was called by the AfterUpdate event of the Form.

 

[Dirk Goldgar]

In

Hi,

Thanks for your interest in my problem. I began having problems with
ADO also, and I have been receiving an error message like this:

A problem occured while Microsoft Access was communicating with the
OLE server or ActiveX Control.

I had been having problems with DLookup as well. What had worked
before now no longer worked.

So I searched the net and found a temporary fix, which is to save a
copy of my form, delete the old form and rename the copy with the
original name.

That suggests that your form had become corrupted.

I have not yet tested my DAO proceedure to see if that is working. I
will post the results here for the benefit of others.

Here is the code I was using when I first began to have problems:

Private Sub PutHHIDIntblPeople()
If Not IsNull(Me.cboHHMemberName3.Value) Then ' Do nothing if
Null If DCount("HouseholdID", "tblPeople", "PersonID = " &
Me.cboHHMemberName3.Value) = 1 Then
With rst
' Find the record needed
.FindFirst "[PersonID] = " & Me.cboHHMemberName3.Value

' Add the HouseholdID to the appropriate record
If Not .NoMatch Then
.Edit
!HouseholdID = Me.txtHouseholdID.Value
.Update
Else
Call MsgBox("No Matching Record was Found. ",
_ vbInformation, " Record Not Found")

End If
End With
End If
End If
End Sub

This procedure was called by the AfterUpdate event of the Form.

Where was the (presumed) recordset rst opened? Where is it declared?
What query or table was it opened on?

 

[Guest]

Hi,

Sorry, I left out some of the code.

Private Sub PutHHIDIntblPeople

On Error GoTo PutHHIDIntblPeople_Error

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT PersonID, HouseholdID FROM
tblPeople;", dbOpenDynaset)

If Not IsNull(Me.cboHHMemberName3) Then ' Do nothing if Null
If DCount("HouseholdID", "tblPeople", "PersonID = " &
Me.cboHHMemberName1.Value) = 1 Then
With rst
' Find the record needed
.FindFirst "[PersonID] = " & Me.cboHHMemberName3.Value

' Add the HouseholdID to the appropriate record
If Not .NoMatch Then
.Edit
!HouseholdID = Me.txtHouseholdID.Value
.Update
Else
Call MsgBox("No Matching Record was Found. ", _
vbInformation, " Record Not Found")

End If
End With
End If
End If

I was having errors with a few forms but the fix I have been using has,
temporarily at least, taken care of that problem. This Database has been in
development for a long time with many additions and changes. It has many
forms and tables. It is a split DB with the front end around 23 MB and the
back end about 4 MB. Both are in the same directory. I regularly compile
the code and compact as well as decompile the Front end occasionally.

I have a number of saved queries that I need to delete. I have developed
some forms and tables with which I can find and store the names of all saved
queries that are being used, even queries only used in VBA. I can then
compare the list of queries being used with the list of queries stored in the
DB and delete the ones not being used.

With so many forms, tables, queries, and changes I have been making I am not
surprised I had some problems.

I downloaded JetComp, used that, fixed my forms and so far things have been
working fine.

The largest table is tblPeople with almost 200 rows.

Patrick Wood

In

Hi,

Thanks for your interest in my problem. I began having problems with
ADO also, and I have been receiving an error message like this:

A problem occured while Microsoft Access was communicating with the
OLE server or ActiveX Control.

I had been having problems with DLookup as well. What had worked
before now no longer worked.

So I searched the net and found a temporary fix, which is to save a
copy of my form, delete the old form and rename the copy with the
original name.

That suggests that your form had become corrupted.

I have not yet tested my DAO proceedure to see if that is working. I
will post the results here for the benefit of others.

Here is the code I was using when I first began to have problems:

Private Sub PutHHIDIntblPeople()
If Not IsNull(Me.cboHHMemberName3.Value) Then ' Do nothing if
Null If DCount("HouseholdID", "tblPeople", "PersonID = " &
Me.cboHHMemberName3.Value) = 1 Then
With rst
' Find the record needed
.FindFirst "[PersonID] = " & Me.cboHHMemberName3.Value

' Add the HouseholdID to the appropriate record
If Not .NoMatch Then
.Edit
!HouseholdID = Me.txtHouseholdID.Value
.Update
Else
Call MsgBox("No Matching Record was Found. ",
_ vbInformation, " Record Not Found")

End If
End With
End If
End If
End Sub

This procedure was called by the AfterUpdate event of the Form.

Where was the (presumed) recordset rst opened? Where is it declared?
What query or table was it opened on?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Hunter57]

In



















I see that you've found a resolution to the question you posted, but I'm
puzzled. I don't see how the bug referenced in the KB article you
posted is related to what you're trying to do. Would you be at all
interested in explaining, and maybe showing the DAO-based code you were
executing that was failing silently?

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Hi,

I no longer believe it was a bug with DAO, but rather corruption of
the form. I have already posted my code here I hope you can see it.
I don't think there is anything unusual about the code. I thought it
was the DAO bug because my code (which was virtually the same) worked
when updating two other tables, but failed when I tried to update
another table. I am sorry if I caused any confusion or
misunderstanding with my reference to a DAO bug.

Patrick Wood

 

[Dirk Goldgar]

In

Hi,

Sorry, I left out some of the code.

Private Sub PutHHIDIntblPeople

On Error GoTo PutHHIDIntblPeople_Error

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT PersonID, HouseholdID FROM
tblPeople;", dbOpenDynaset)

If Not IsNull(Me.cboHHMemberName3) Then ' Do nothing if Null
If DCount("HouseholdID", "tblPeople", "PersonID = " &
Me.cboHHMemberName1.Value) = 1 Then
With rst
' Find the record needed
.FindFirst "[PersonID] = " & Me.cboHHMemberName3.Value

' Add the HouseholdID to the appropriate record
If Not .NoMatch Then
.Edit
!HouseholdID = Me.txtHouseholdID.Value
.Update
Else
Call MsgBox("No Matching Record was Found. ",
_ vbInformation, " Record Not Found")

End If
End With
End If
End If

I don't think this code was causing your problem, but it might be
improved. Why open a recordset on the whole table, when you could just
open it on the record you want:

'----- start of example code -----
If Not IsNull(Me.cboHHMemberName3) Then ' Do nothing if Null

Set rst = dbs.OpenRecordset( _
"SELECT PersonID, HouseholdID FROM tblPeople " & _
"WHERE PersonID = " & Me.cboHHMemberName1,
dbOpenDynaset)

With rst

If .EOF Then
MsgBox _
"No Matching Record was Found.", _
vbInformation, _
"Record Not Found"
Else
If DCount( _
"HouseholdID", _
"tblPeople", _
"PersonID = " & Me.cboHHMemberName1) _
= 1 _
Then
' Add the HouseholdID to the record
.Edit
!HouseholdID = Me.txtHouseholdID
.Update
End If
End If

' Close the recordset; we're done with it.
.Close

End With

End If

'----- end of example code -----

Depending on the logic behind your using DCount to count the number of
matching records, you may be able to replace the DCount with a simple
check of the number of records in the recordset:

rst.MoveLast
If rst.RecordCount = 1 Then

However, this isn't quite the same as what the DCount does, as the
DCount (as written) will count the number of matching records with
non-Null values for HouseholdID, which the simple logic above doesn't
do.

 

[Guest]

Sorry I forgot to post this sooner.

My code works after correcting the problem with the form. Thanks for your
input.

Pat Wood