ADO Problem with unbound form in Access 2007
I asked a question about this topic recently under a different title (https://www.experts-exchange.com/questions/23212306/Code-Works-in-Access-2003-but-not-in-Access-2007.html), got a quick response, but have since not seen any activity on the question. I posted a followup, but have not gotten any further responses, so I am reposting it here with more information:
Can anyone tell me what is causing the error described below?
The attached code uses ActiveX Data Object, ADO, and is working in Microsoft Access 2003, but fails to work some of the time in Access 2007.
A function (DataSelect) sends a query to the SQL server (2005), using cursortype property adOpenForwardOnly and LockType property of adLockReadOnly.
A successful result of 1 expected record is returned to an ADODB recordset. The connection is set to nothing, however, resulting in a disconnected ADO recordset.
The disconnected ADO recordset is then passed to a clone function (CloseRS). The passed ADO recordset is saved to disk using ADO's stream object in a persistent recordset in XML format.
The stream recordset is read into a string (str = oStream.ReadText) and searched for various values, which are replaced with other values (see the attached code - lines 67 through 75).
The stream recordset is then replaced by the str, which is then set to a new ADODB recordset and returned to the DataSelect function.
The DataSelect function then returns the ADODB recordset to the calling routine, and that is where the error occurs.
So far, I have determined that this process does not work for some Forms' Control Sources, but does work for others, and seems to work on combo boxes, text boxes, etc. The problem does NOT occur for ALL forms. I have not been able to find a pattern in the failures based on the types of data being retrieved, nor have I found anything consistent between the forms where this fails.
Can anyone tell me what is causing the error described below?
The attached code uses ActiveX Data Object, ADO, and is working in Microsoft Access 2003, but fails to work some of the time in Access 2007.
A function (DataSelect) sends a query to the SQL server (2005), using cursortype property adOpenForwardOnly and LockType property of adLockReadOnly.
A successful result of 1 expected record is returned to an ADODB recordset. The connection is set to nothing, however, resulting in a disconnected ADO recordset.
The disconnected ADO recordset is then passed to a clone function (CloseRS). The passed ADO recordset is saved to disk using ADO's stream object in a persistent recordset in XML format.
The stream recordset is read into a string (str = oStream.ReadText) and searched for various values, which are replaced with other values (see the attached code - lines 67 through 75).
The stream recordset is then replaced by the str, which is then set to a new ADODB recordset and returned to the DataSelect function.
The DataSelect function then returns the ADODB recordset to the calling routine, and that is where the error occurs.
So far, I have determined that this process does not work for some Forms' Control Sources, but does work for others, and seems to work on combo boxes, text boxes, etc. The problem does NOT occur for ALL forms. I have not been able to find a pattern in the failures based on the types of data being retrieved, nor have I found anything consistent between the forms where this fails.
Public Function DataSelect(strQuery As String, Optional params As Collection, Optional isSQLQuery As Boolean, Optional replaceAsIs As Boolean) As ADODB.Recordset
On Error GoTo Err_DS
Dim count As Integer
If IsNull(isSQLQuery) Or Not isSQLQuery Then strQuery = CodeDb.QueryDefs(strQuery).SQL
If m_adoCnn.State <> adStateOpen Then
OpenConnection g_strConnectionString
End If
Dim adoCmd As New ADODB.Command
Dim adors As New ADODB.Recordset
If Not (params Is Nothing) Then
If IsNull(replaceAsIs) Or Not replaceAsIs Then
For count = 1 To params.count
strQuery = Replace$(strQuery, params.Item(count).Name, "'" & params.Item(count).value & "'", , , vbTextCompare)
Next count
Else
For count = 1 To params.count
strQuery = Replace$(strQuery, params.Item(count).Name, params.Item(count).value, , , vbTextCompare)
Next count
End If
End If
Debug.Print strQuery
Set adoCmd.ActiveConnection = m_adoCnn
adoCmd.CommandType = adCmdText
adoCmd.CommandText = strQuery
adors.Open adoCmd, , adOpenForwardOnly, adLockReadOnly
Set adors.ActiveConnection = Nothing
Dim rs As ADODB.Recordset
Set rs = CloneRS(adors)
Set DataSelect = rs
adors.Close
Set adors = Nothing
Set adoCmd = Nothing
Exit_DS:
Exit Function
Err_DS:
HandleSecurityProblem err.Number, err.Description
MsgBox ("Error in DataSelect" & Chr(13) & "Error # " & str(err.Number) & " was generated by " & err.Source & Chr(13) & err.Description)
Set DataSelect = Null
Resume Exit_DS
End Function
'-------------------------------------------------------------------------
Public Function CloneRS(ByVal oRs As ADODB.Recordset) As ADODB.Recordset
Dim oStream As ADODB.Stream
Dim oRsClone As ADODB.Recordset
Dim str As String
Dim intIdIndex As Integer
Dim intAttrEndIndex As Integer
Dim intAttrWriteIndex As Integer
'save the recordset to the stream object
Set oStream = New ADODB.Stream
oRs.Save oStream, adPersistXML
str = oStream.ReadText
intIdIndex = InStr(1, str, "s:AttributeType name='ID'", vbTextCompare)
If (intIdIndex > 0) Then
intAttrEndIndex = InStr(intIdIndex, str, ">", vbTextCompare)
intAttrWriteIndex = InStr(intIdIndex, str, "rs:writeunknown='true'", vbTextCompare)
If ((intAttrEndIndex > intAttrWriteIndex) And (intAttrWriteIndex > intIdIndex)) Then
str = Replace$(oStream.ReadText, "rs:writeunknown='true'", "", , 1, vbTextCompare)
End If
str = Replace$(str, "s:AttributeType name='id'", "s:AttributeType name='id' rs:writeunknown='true'", , , vbTextCompare)
End If
oStream.position = 0
oStream.WriteText str
oStream.position = 0
'and now open the stream object into a new recordset
Set oRsClone = New ADODB.Recordset
oRsClone.Open oStream, , adOpenKeyset, adLockPessimistic
'return the cloned recordset
Set CloneRS = oRsClone
'release the reference
oStream.Close
Set oStream = Nothing
Set oRsClone = Nothing
End Function
ASKER
I will be grateful for any assistance as I am getting quite desperate!
The normal usages of DataSelect are like this:
Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
Set Me.Recordset = DataSelect("My_Query", params.colParameters)
Set Me.Recordset = DataSelect("SELECT * FROM My_Table", , True)
I have also tried:
Dim rs As New ADODB.Recordset
Set rs = gDAL.DataSelect("My_Query"
Set Me.Recordset = rs
I have also placed error handling around the set me.recordset:
On Error GoTo HandleErr
Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
HandleErr:
MsgBox err.Description, vbOKOnly, err.Source & ":" & err.Number
In EVERY case, if the problem occurs, it always happens at exactly the same place -- line 40 of the code in the attached example.
The normal usages of DataSelect are like this:
Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
Set Me.Recordset = DataSelect("My_Query", params.colParameters)
Set Me.Recordset = DataSelect("SELECT * FROM My_Table", , True)
I have also tried:
Dim rs As New ADODB.Recordset
Set rs = gDAL.DataSelect("My_Query"
Set Me.Recordset = rs
I have also placed error handling around the set me.recordset:
On Error GoTo HandleErr
Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
HandleErr:
MsgBox err.Description, vbOKOnly, err.Source & ":" & err.Number
In EVERY case, if the problem occurs, it always happens at exactly the same place -- line 40 of the code in the attached example.
(Answering on the train - so limited bandwidth and time...)
Did you acquire this function and object code from any specific location?
(A book of some sort perhaps? A derivation of something?)
What is the ultimate purpose of the change to the recordset?
Updatability? (If so why was the recordset not updatable in the first place?)
It might be that, since your issue is inconsistent, I won't be able to reproduce it failing.
You say it fails on line 40...
Exit Function??? :-s
Did you acquire this function and object code from any specific location?
(A book of some sort perhaps? A derivation of something?)
What is the ultimate purpose of the change to the recordset?
Updatability? (If so why was the recordset not updatable in the first place?)
It might be that, since your issue is inconsistent, I won't be able to reproduce it failing.
You say it fails on line 40...
Exit Function??? :-s
OK - playing as I can while on the train still...
Have you missed out any code from the functions?
Where do you set the recordsets as Client Side cursors?
You, naturally, must have them set as such in order to successfully disconnect them.
(A server side cursor is an impossible concept - as the whole intent is that you're severing the connection to the server!)
So immediately *prior* to your line
adors.Open ...
you should insert
adors.CursorLocation = adUseClient
That, obviously, shouldn't be a reason why the procedure fails for some forms and not others on a 2007 instance...
But I need to ask about it.
Have you missed out any code from the functions?
Where do you set the recordsets as Client Side cursors?
You, naturally, must have them set as such in order to successfully disconnect them.
(A server side cursor is an impossible concept - as the whole intent is that you're severing the connection to the server!)
So immediately *prior* to your line
adors.Open ...
you should insert
adors.CursorLocation = adUseClient
That, obviously, shouldn't be a reason why the procedure fails for some forms and not others on a 2007 instance...
But I need to ask about it.
Of course, that said, I can't see your OpenConnection procedure - so it's possible that you open your connection as Client Side instead.
(But this is again down to me not seeing everything you have in use...)
(But this is again down to me not seeing everything you have in use...)
ASKER
In reverse order:
3. Yes, it fails on the Exit Function. I can see the XML code that is in "oStream" in the CloneRS function. It is identical to the XML code produced under the exact same conditions as in Access 2003. The issue is "inconsistent" in that it does not seem to occur under all conditions, but does always occur when retrieving the data it fails on. E.G. it never occurs when retrieving record X, but when using the exact same query to receive record Y, it fails. I will do my best to set up a test case to send to you, including SQL tables, sample data and Access code.
2. I started with an Access front end and an Access back end. Because I had to expand it to numerous users, and because I had no shared drive, I converted the back end to SQL Server. At first I used linked tables, but the time necessary to link all the tables when opening the front end was too long (almost 2 minutes), and when going from the main screen to the first operating screen, retrieving records took almost 45 seconds.
Working with consulting assistance directly from the people who supplied the functions in question, as well as many others, I converted the linked tables to the scheme I am using today. All forms are unbound. I retrieve data using the functions attached here, and attach the results as recordsets to the unbound forms. A similar set of functions is used to control updating and inserting, essentially running SQL statements in those functions.
The upshot is that no linking is needed, and going from the main screen to the first operating screen now occurs extremely fast.
1. I got the code from an outfit that was expert at working with Access 2003 and SQL Server 2005, but which is no longer available to me. The agreement under which I obtained the code was not renewed and I have no funds or permission to go back to them. I am pursuing obtaining some back door guidance, but it does not look good.
3. Yes, it fails on the Exit Function. I can see the XML code that is in "oStream" in the CloneRS function. It is identical to the XML code produced under the exact same conditions as in Access 2003. The issue is "inconsistent" in that it does not seem to occur under all conditions, but does always occur when retrieving the data it fails on. E.G. it never occurs when retrieving record X, but when using the exact same query to receive record Y, it fails. I will do my best to set up a test case to send to you, including SQL tables, sample data and Access code.
2. I started with an Access front end and an Access back end. Because I had to expand it to numerous users, and because I had no shared drive, I converted the back end to SQL Server. At first I used linked tables, but the time necessary to link all the tables when opening the front end was too long (almost 2 minutes), and when going from the main screen to the first operating screen, retrieving records took almost 45 seconds.
Working with consulting assistance directly from the people who supplied the functions in question, as well as many others, I converted the linked tables to the scheme I am using today. All forms are unbound. I retrieve data using the functions attached here, and attach the results as recordsets to the unbound forms. A similar set of functions is used to control updating and inserting, essentially running SQL statements in those functions.
The upshot is that no linking is needed, and going from the main screen to the first operating screen now occurs extremely fast.
1. I got the code from an outfit that was expert at working with Access 2003 and SQL Server 2005, but which is no longer available to me. The agreement under which I obtained the code was not renewed and I have no funds or permission to go back to them. I am pursuing obtaining some back door guidance, but it does not look good.
You might have to send me an example of a form or two that doesn't work (in an MDB) - and the SQL DDL statements to recreate the data objects on the server... to emulate your situation...
ASKER
The code is exactly what I use in the MDB. I have not altered it in any way when displaying it in the attachment.
ASKER
<So immediately *prior* to your line
adors.Open ...
you should insert
adors.CursorLocation = adUseClient>
I have attached the OpenConnection code.
A typical value of "strConnectionString" is:
Provider=sqloledb;Data Source=myServer;Initial Catalog=myDB;UID=myUID;PWD
adors.Open ...
you should insert
adors.CursorLocation = adUseClient>
I have attached the OpenConnection code.
A typical value of "strConnectionString" is:
Provider=sqloledb;Data Source=myServer;Initial Catalog=myDB;UID=myUID;PWD
Public Function OpenConnection(strConnectionString) As Long
On Error GoTo Err_OpenConnection
Dim lngReturnCode As Long
lngReturnCode = 0
'-- open the connection if its not already open
Debug.Print m_adoCnn.ConnectionString
If m_adoCnn.State <> adStateOpen Or m_adoCnn.ConnectionString <> strConnectionString Then ' Refresh on combobox-toggle
If m_adoCnn.State = adStateOpen Then CloseConnection ' Refresh on combobox-toggle
m_adoCnn.ConnectionTimeout = 5
m_adoCnn.ConnectionString = strConnectionString
m_adoCnn.CursorLocation = adUseClient
m_adoCnn.Open
End If
OpenConnection = lngReturnCode
Exit_OpenConnection:
Exit Function
Err_OpenConnection:
HandleSecurityProblem err.Number, err.Description
lngReturnCode = err.Number
Debug.Print err.Number
Debug.Print err.Description
g_strConnectionError = err.Description
OpenConnection = lngReturnCode
Resume Exit_OpenConnection
End Function
Yep - that's all as I'd surmised it must be.
Can you get me those examples?
Cheers.
Can you get me those examples?
Cheers.
There are some definite problems with Access 2k7. Here's one of the best places to learn about how good a job MS did on 2k7.
http://allenbrowne.com/Access2007.html#Bugs
There's a lot of good information in there. Maybe you'll see something that rings a bell.
http://allenbrowne.com/Access2007.html#Bugs
There's a lot of good information in there. Maybe you'll see something that rings a bell.
ASKER
FYI - I have been working on a cutdown MDB and a cut down SQL Server 2005 database and set of tables with cleansed data. I will finish it sometime during the weekend and post it.
Cool, I'll look at it then.
If I get the chance I'll test under 2007 too in the mean time. As the above works under 2003 (as I'd expect it to do).
(But, of course, when you can provide definite examples of forms and records that do fail - that'll help).
If I get the chance I'll test under 2007 too in the mean time. As the above works under 2003 (as I'd expect it to do).
(But, of course, when you can provide definite examples of forms and records that do fail - that'll help).
ASKER
I worked all weekend on cutting down a very complex MDB to send you as an example, along with a very cut down version of the SQL Server DB.
I got them partially cut down so the error occurred, but when I took steps to cut out the final unnecessary parts, the error disappeared. Now, I have to go back to my intermediate step and start over.
Very frustrating.
I got them partially cut down so the error occurred, but when I took steps to cut out the final unnecessary parts, the error disappeared. Now, I have to go back to my intermediate step and start over.
Very frustrating.
Make you wonder how unnecessary the "final unnecessary parts" were? ;-)
Or perhaps - what other cutting down actions you'd performed in the mean time.
When you do get to post it - please also post what steps those were you'd taken to cut down (so I can reproduce them).
(It's sounding slightly worryingly flakey at present though).
Or perhaps - what other cutting down actions you'd performed in the mean time.
When you do get to post it - please also post what steps those were you'd taken to cut down (so I can reproduce them).
(It's sounding slightly worryingly flakey at present though).
ASKER
Yes, it has been a very worrying weekend!!!
What I am more carefully going to do now is start with my intermediate cut-down and begin carefully cutting out, then testing, then cutting out, then testing, until it goes from failure to success, then put the last cut-down back in and post notes inside the code (and here) as to what the last cut-down consists of.
Obviously at this point I may have learned that the error does not happen because of some self-contained issues, but because of the interaction of what has occurs just before the error occurs!
The answer here is likely to generate one of two reactions. Either "Aha!" or "You stupid idiot, why didn't you see that before!!".
What I am more carefully going to do now is start with my intermediate cut-down and begin carefully cutting out, then testing, then cutting out, then testing, until it goes from failure to success, then put the last cut-down back in and post notes inside the code (and here) as to what the last cut-down consists of.
Obviously at this point I may have learned that the error does not happen because of some self-contained issues, but because of the interaction of what has occurs just before the error occurs!
The answer here is likely to generate one of two reactions. Either "Aha!" or "You stupid idiot, why didn't you see that before!!".
True perhaps - but the fact remains that you say this works in all scenarios under 2003.
I've certainly not found it to fail since I've been manipulating recordsets like this (well - not as long as Acc2002 or later).
Will be interesting to see what comes of it Re 2007.
If it's consistently reproducable I'll be very interested (in that if it's a new flaw/limitation compared to previous versions).
I've certainly not found it to fail since I've been manipulating recordsets like this (well - not as long as Acc2002 or later).
Will be interesting to see what comes of it Re 2007.
If it's consistently reproducable I'll be very interested (in that if it's a new flaw/limitation compared to previous versions).
ASKER
OK, I finally have a cut down version of the MDB, and a script to generate a DB with tables and data. Do I attach it here (Attach File) or is there another mechanism you want me to use?
ASKER
I am posting a ZIP file with 3 items in it:
1. An MDB "Database1 Access 2003 2008-03-17 13 .mdb" that "works" in 2003 (look in the code for notes with a long string of asterisks that explain the situation -- the code gets past the error location, but then goes into a loop if the ADO call in question is successful -- I cut it down a bit too much, but at least the loop will show you the problem code succeeded).
2. An exact copy of that MDB, but called "Database1 Access 2007 2008-03-17 13.mdb", but with References changed for 2007.
3. A SQL script that will generate a cut down database and add a few appropriate records to tables that will simulate my full DB, and which will show the test failing in 2007
NOTES:
1. I hard coded the userID (Tester1) and password ($Tester1) in the code. When the DB (TesterDB) is generated by the SQL script, it will include the Tester1 login and give it the proper password. HOWEVER, the one thing I did not know how to script, and therefore you will have to do manually, is to go into SQL Management Studio and check "dbowner" for Tester1 on the TesterDB database.
2. When you open either of the MDBs, there are 2 forms. The one with the login is Main Apps Cat Screen. Open that and click Login to start the logic.
3. Look in the code of the second form "Edit Apps Cat Records" for long strings of asterisks that pinpoint the code that fails.
1. An MDB "Database1 Access 2003 2008-03-17 13 .mdb" that "works" in 2003 (look in the code for notes with a long string of asterisks that explain the situation -- the code gets past the error location, but then goes into a loop if the ADO call in question is successful -- I cut it down a bit too much, but at least the loop will show you the problem code succeeded).
2. An exact copy of that MDB, but called "Database1 Access 2007 2008-03-17 13.mdb", but with References changed for 2007.
3. A SQL script that will generate a cut down database and add a few appropriate records to tables that will simulate my full DB, and which will show the test failing in 2007
NOTES:
1. I hard coded the userID (Tester1) and password ($Tester1) in the code. When the DB (TesterDB) is generated by the SQL script, it will include the Tester1 login and give it the proper password. HOWEVER, the one thing I did not know how to script, and therefore you will have to do manually, is to go into SQL Management Studio and check "dbowner" for Tester1 on the TesterDB database.
2. When you open either of the MDBs, there are 2 forms. The one with the login is Main Apps Cat Screen. Open that and click Login to start the logic.
3. Look in the code of the second form "Edit Apps Cat Records" for long strings of asterisks that pinpoint the code that fails.
OK. Where's it posted?
ASKER
Sorry - I did not see the prohibition against .zip files. I have changed the extension to .txt. When you get it, please change it back to .zip.
ADO-Works-in-2003-but-not-in-200.txt
ADO-Works-in-2003-but-not-in-200.txt
ASKER
From an earlier posting of mine:
<the code gets past the error location, but then goes into a loop if the ADO call in question is successful -- I cut it down a bit too much, but at least the loop will show you the problem code succeeded>
Actually, I did not cut the code down too much -- this exercise has uncovered a bug in the code, totally unrelated to the problem, but related to a new user with no records assigned trying to log in... So, if nothing else, your efforts led me to finding that problem!
Rather than post all new MDBs, I have posted the code below. Please replace the "AddNewApplication" in both the 2003 and 2007 MDBs with this one.
<the code gets past the error location, but then goes into a loop if the ADO call in question is successful -- I cut it down a bit too much, but at least the loop will show you the problem code succeeded>
Actually, I did not cut the code down too much -- this exercise has uncovered a bug in the code, totally unrelated to the problem, but related to a new user with no records assigned trying to log in... So, if nothing else, your efforts led me to finding that problem!
Rather than post all new MDBs, I have posted the code below. Please replace the "AddNewApplication" in both the 2003 and 2007 MDBs with this one.
Private Sub AddNewApplication()
'INCLUDE IN TEST
DoCmd.Echo False
If Me.Dirty Then
SaveForm
End If
'-- adds a new blank record
gDAL.SaveEditAppsCatRecords Null, Generate_New_Apps_Cat_ID, 0, Isolate_View_By_Analyst.Column(0), _
Null, Null, Null, Null, Null
'-- form_current occurs as soon as this is set
'**************************************
'Inserted the following instruction to stop a loop when adding a first record for
'an analyst that has no records previously assigned.
'**************************************
m_bInitialLoadComplete = False
'**************************************
Dim params As New DBParameters
Set params = New DBParameters
params.AddNamedQuotedParameter "@KT_Only_Clause", "(tg.Entry_Type <> 'KT Only') AND "
params.AddNamedQuotedParameter "@analyst_name", "'" & Isolate_View_By_Analyst.Column(0) & "'"
'**********************************************************
'The next instruction will fail in 2007 but not in 2003.
'
'In 2003, you will get past the next instruction and go to Form_Current,
'then back to the MsgBox statement below where the "Success" message will display
'
'In 2007, you will not even get to Form_Current, but will abend with the ubiquitous
'"sorry but we found an error and have to quit" message
'**********************************************************
Set Me.Recordset = gDAL.DataSelect("Qry_General_Info_S", params.colParameters, , True)
MsgBox "I got past the error!!!' "
DoCmd.GoToRecord , , acFirst
DoCmd.Echo True
End SubASKER
Leigh -- Did you get the material I posted and have you had a chance to look at it? I expect that you are very busy, and it may take a while, but just wanted to be sure I posted the material acceptably.
Bill
Bill
ASKER
Leigh -- a colleague assisted me in doing some research and testing on this issue and found something very interesting...
As a reminder, the code fails on the Exit Function statement of the DataSelect Function (FYI -- line 40 of the original piece of code I posted). This is the point at which the Form_Current appears to be triggered.
My colleague found various discussions in various books about "Asynchronous Execution" in Access. Essentially, there are times when Access runs two sections of code, potentially from different events, simultaneously. He surmised that somehow the Form_Current was executing before the DataSelect had really finished processing.
Remember that the DataSelect is always used like this:
Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
This statement triggers the Form_Current.
So, as a test, we modified the code as follows:
1. Removed the Form_Current event handler.
2. Placed all of the original code from Form_Current into a new routine called "My_Form_Current"
3. Modified the usage of the DataSelect as follows:
a. Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
b. My_Form_Current
The problem disappeared!!!
I am not satisfied with this "solution". Questions still remain...
1. Why should it be necessary to modify the code in this way?
2. Is something about Access 2007 slower than Access 2003 that this would be an issue?
3. Is it something about the amount of data being retrieved that is causing the problem? The failure occurs in low-data situations, but in other high-data situations, the problem does not happen.
4. Is it something about the access method? I am using sqloledb:
Provider=sqloledb;Data Source=myServer;Initial Catalog=myDB;UID=myUID;PWD
5. Should I be using something else? If so, what is more proper/faster?
6. How about:
ODBC;Driver={SQL Server};SERVER="myServer";
As a reminder, the code fails on the Exit Function statement of the DataSelect Function (FYI -- line 40 of the original piece of code I posted). This is the point at which the Form_Current appears to be triggered.
My colleague found various discussions in various books about "Asynchronous Execution" in Access. Essentially, there are times when Access runs two sections of code, potentially from different events, simultaneously. He surmised that somehow the Form_Current was executing before the DataSelect had really finished processing.
Remember that the DataSelect is always used like this:
Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
This statement triggers the Form_Current.
So, as a test, we modified the code as follows:
1. Removed the Form_Current event handler.
2. Placed all of the original code from Form_Current into a new routine called "My_Form_Current"
3. Modified the usage of the DataSelect as follows:
a. Set Me.Recordset = DataSelect("My_Query", params.colParameters, , True)
b. My_Form_Current
The problem disappeared!!!
I am not satisfied with this "solution". Questions still remain...
1. Why should it be necessary to modify the code in this way?
2. Is something about Access 2007 slower than Access 2003 that this would be an issue?
3. Is it something about the amount of data being retrieved that is causing the problem? The failure occurs in low-data situations, but in other high-data situations, the problem does not happen.
4. Is it something about the access method? I am using sqloledb:
Provider=sqloledb;Data Source=myServer;Initial Catalog=myDB;UID=myUID;PWD
5. Should I be using something else? If so, what is more proper/faster?
6. How about:
ODBC;Driver={SQL Server};SERVER="myServer";
ASKER
PROBLEM NOT SOLVED...
In my previous post I indicated I thought we could just get rid of the Form_Current and avoid what we think is Asynchronous Execution, but that is not an acceptable solution, because when you click a record navigator button, the only Event that knows you did that is Form_Current.
In my MDB, I have unbound Forms and unbound Sub Forms on those Forms. When the record navigator buttons are clicked on the Form, the Form_Current of the Form tells the Sub Form to refill itself with data based on the new values of the Form. This cannot happen if there is no Form Current.
In my previous post I indicated I thought we could just get rid of the Form_Current and avoid what we think is Asynchronous Execution, but that is not an acceptable solution, because when you click a record navigator button, the only Event that knows you did that is Form_Current.
In my MDB, I have unbound Forms and unbound Sub Forms on those Forms. When the record navigator buttons are clicked on the Form, the Form_Current of the Form tells the Sub Form to refill itself with data based on the new values of the Form. This cannot happen if there is no Form Current.
ASKER
How ironic I should see a notification about your post in my email today!!
I had just started up my work computer this morning (Sunday morning) to post a note to the moderators, again asking if they could redirect this post to someone else who could help, because I am now quite desperate for that assistance!
Let me explain...
I think I can reconstruct the stream of events: as you can see from the first post of this thread, I had posted this question earlier, but the trail ran cold.
I asked for help from a moderator, who recommended I post it again and they would see if they could draw in an expert. Leigh Purvis, apparently at the behest of the moderator, then joined the thread. We had a bit of exchange after that, as you can see by the posts in the thread.
On March 25, because I had seen no further posts from Leigh, I sent a direct email to him inquiring whether he had seen my last post, and was still looking into this for me and he indicated he was extremely busy, but would be able to pursue it.
I know that all posts regarding a solution must be part of the thread, so my personal email was just for the purpose of ascertaining whether he still considered this something he could help with, and he indicated he could. Other than trying to ascertain whether he could help, we have had no discussion outside the thread.
Then on March 28, I thought I had found the answer, as you can see in the thread, but within 3 hours, I admitted it was a premature diagnosis. I waited to see if Leigh would respond. I know he is very busy!
Unfortunately, my last personal emails to Leigh of 4/23 and 6/8/2008 asking if he had been able to look at it have, as of this morning, gone unanswered.
I have been able to stall this issue by working on other issues on my plate that are just as important, hoping I would get assistance, but now I am done with those. The bottom line for me is, I am still very much in need of assistance from an expert on this issue. Is there anyone else you could draw into this thread who could help. Do I need to repost? What should I do? Please help!
I had just started up my work computer this morning (Sunday morning) to post a note to the moderators, again asking if they could redirect this post to someone else who could help, because I am now quite desperate for that assistance!
Let me explain...
I think I can reconstruct the stream of events: as you can see from the first post of this thread, I had posted this question earlier, but the trail ran cold.
I asked for help from a moderator, who recommended I post it again and they would see if they could draw in an expert. Leigh Purvis, apparently at the behest of the moderator, then joined the thread. We had a bit of exchange after that, as you can see by the posts in the thread.
On March 25, because I had seen no further posts from Leigh, I sent a direct email to him inquiring whether he had seen my last post, and was still looking into this for me and he indicated he was extremely busy, but would be able to pursue it.
I know that all posts regarding a solution must be part of the thread, so my personal email was just for the purpose of ascertaining whether he still considered this something he could help with, and he indicated he could. Other than trying to ascertain whether he could help, we have had no discussion outside the thread.
Then on March 28, I thought I had found the answer, as you can see in the thread, but within 3 hours, I admitted it was a premature diagnosis. I waited to see if Leigh would respond. I know he is very busy!
Unfortunately, my last personal emails to Leigh of 4/23 and 6/8/2008 asking if he had been able to look at it have, as of this morning, gone unanswered.
I have been able to stall this issue by working on other issues on my plate that are just as important, hoping I would get assistance, but now I am done with those. The bottom line for me is, I am still very much in need of assistance from an expert on this issue. Is there anyone else you could draw into this thread who could help. Do I need to repost? What should I do? Please help!
My bad. Sorry about it getting past me.
Just poor luck you've caught me at very busy moments - as I *hate* to ever let a thread slip by.
Have such a moment right now - but I'll look at this after working hours here tonight (BST - GMT+1)
Just poor luck you've caught me at very busy moments - as I *hate* to ever let a thread slip by.
Have such a moment right now - but I'll look at this after working hours here tonight (BST - GMT+1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interesting you should mention the other code. For me, I just quickly created a new DB, new table and two new forms. I started clean and now I more fully understand why I got it to work. I did notice the "Save Record" code but since that wasn't the topic I ignored it. The other point I wanted to make was that F1 in the sample database did have "tblcountry" as it's recordsource where an unbound shouldn't have any recordsource. My thoughts were that since the field did exist within the main form's recordset(tblcountry) and the subform recordset, Access tried to create a Master/Child link relationship. Without the table present as the recordsource, I saw no issue with the link fields being auto-filled by Access.
Recordset bound forms don't adhere to Master/Child Link Fields.
We have to implement that functionality ourselves - filtering (/requerying) the subform as the parent navigates.
I'm not totally clear on what you did by way of a test Arji? Used Recordset binding - based on data where?
We have to implement that functionality ourselves - filtering (/requerying) the subform as the parent navigates.
I'm not totally clear on what you did by way of a test Arji? Used Recordset binding - based on data where?
ASKER
Leigh -- I GREATLY appreciate the time you took to look at this!!!
Moderator -- Please do not, under any circumstances, refund any points or delete this issue!!! I will go ahead and award the points to Leigh. If it were allowable, I would award substantially more than is possible.
Leigh, even though awarding points will effectively close this question, I would like to be able to continue conversation about it. I will download what you posted and begin looking at it.
A couple of quick points/questions:
I wonder why that is... I ran it under 2003 and it worked fine. I will need to look at what you changed.
The professionally written code was written by Microsoft technicians. But, while I readily confess I am nowhere near their level of expertise, and could be accused of writing the rest of the code, much of it was also written by those same Microsoft technicians.
Early on we (I and the Microsoft technicians) discussed whether I should use a button to generate a new record. I was interested, if at all possible, in trying to make the application work like it would have with bound forms, in that a new record placeholder would always be available after the last record, because most users at the time were familiar with the standard Access look and feel. In addition, screen real estate is a major problem.
Their response was, "don't worry, we can do it this way..."
The bottom line here seems to be that the code as written got away with something under 2003, but that loophole seems to have been closed in 2007.
Nevertheless, I will dig into this and get back to you as quickly as possible with either a report of success, or with further questions.
Again, thanks so much for your efforts!
Moderator -- Please do not, under any circumstances, refund any points or delete this issue!!! I will go ahead and award the points to Leigh. If it were allowable, I would award substantially more than is possible.
Leigh, even though awarding points will effectively close this question, I would like to be able to continue conversation about it. I will download what you posted and begin looking at it.
A couple of quick points/questions:
I wonder why that is... I ran it under 2003 and it worked fine. I will need to look at what you changed.
The professionally written code was written by Microsoft technicians. But, while I readily confess I am nowhere near their level of expertise, and could be accused of writing the rest of the code, much of it was also written by those same Microsoft technicians.
Early on we (I and the Microsoft technicians) discussed whether I should use a button to generate a new record. I was interested, if at all possible, in trying to make the application work like it would have with bound forms, in that a new record placeholder would always be available after the last record, because most users at the time were familiar with the standard Access look and feel. In addition, screen real estate is a major problem.
Their response was, "don't worry, we can do it this way..."
The bottom line here seems to be that the code as written got away with something under 2003, but that loophole seems to have been closed in 2007.
Nevertheless, I will dig into this and get back to you as quickly as possible with either a report of success, or with further questions.
Again, thanks so much for your efforts!
Oh there's almost always a way to implement desired functionality.
But, to my mind, it's about implementing good practices.
The coding is fine - but IMO the decision isn't necessarily a good one - even when it worked in 2003.
No probs about it being ongoing. (And apparently we get nice formatting options once we're into Discussion mode now? Cool. ;-)
Though as far as I'm concerned there's no rush to close unless the shutters are coming down.
But, to my mind, it's about implementing good practices.
The coding is fine - but IMO the decision isn't necessarily a good one - even when it worked in 2003.
No probs about it being ongoing. (And apparently we get nice formatting options once we're into Discussion mode now? Cool. ;-)
Though as far as I'm concerned there's no rush to close unless the shutters are coming down.
ASKER
What misplaced comments and off-topic comments?
ASKER
Leigh -- Sorry for the late post here. I have been struggling with a major number of items, as I have indicated in a personal email I sent to you a few minutes ago, but finally got some significant time to get back to work on this problem.
Your solution did not quite do the trick. But, I have finally been able to spend some significant time working through this and am posting here my best understanding and description of what is happening and what I figured out I needed to do. Your work on my behalf went a long way towards helping me develop my understanding, and for that I am grateful! The solution I am now implementing is working!
The problem I had with using ADO recordsets on unbound forms turns out to be a bit different than I first thought I understood. Your solution works for stand-alone forms (which is what I presented to you as a sample), but when you bring sub forms into the picture it falls apart.
Access 2007 seems to be different than 2003 in that if you try to run a Form_Current before you have run a Form_Open with unbound forms, it gets quite upset because of some sort of out of sync condition.
The way Access handles forms and subforms has always seemed to be backward to me, and it is especially troublesome in 2007 when using unbound forms.
The simplest explanation I can give for what happens in 2003 is:
When you open a parent form, the Form_Open of the sub form runs first, followed by the Form_Load of the sub form, followed by the Form_Current of the sub form. This is repeated for all Sub Forms. When the last one is processed, the Form Open, Form Load and Form Current of the parent form are run, unless one or more of the Sub Forms references data on the parent form, in which case the parent form routines are mixed in. This frequently results in multiple Form Currents being run on the parent form.
The code I was using somehow worked in 2003, but in 2007, some sort of out of sync condition occurs, causing the crash.
The only way I have gotten this to run in 2007 is to force the order of processing in what seems to me to be a more straight forward method:
On the Parent form, use a Form Open, Form Load and Form Current. Make sure the Form Current does not run until the Form Open, which has the assignment of the ADO database (Set me.recordset = ...), completes.
In the Sub Forms, eliminate all Form Open, Form Current and Form Load routines in order to control the exact order of processing to make sure the parent form is completely loaded first.
Since there is no Form Open, Form Current or Form Load on the sub form, once the ADO recordset is assigned on the parent form, the only way to get the sub form loaded with data is to execute the "Public" LoadFormData for each sub form.
If the sub form needs record navigation, then use a Form Current there, but use a Module variable (m_bLoad) to ensure its code does not run until after the LoadFormData is run, at which time you set the m_bLoad to True, so that any Form Current code can process.
In actuality, for every form, you can divide the functionality: there can be a LoadFormData which sets the recordset and loads any dropdowns that will be constant for every record presented. Then a RequeryFormData can contain logic to handle those things that change because movement was made from one record to another.
When the record is changed on the parent form by a Form Current, you must reload the recordsets on the sub forms based on values on the parent form. This may seem like a lot of processing, but it results in only the absolutely necessary data being retrieved.
If you do this, then the order of processing will be:
Load Parent Form
Load Sub Form
Move between records on the Parent form
ReLoad Sub Form
This can, of course, be propagated for any amount of nesting of sub forms.
Access's "backwards" management of sub forms and parent forms, which works fine for bound forms and DAO recordsets, is out of the picture and we are controlling loading of forms more precisely.
Your solution did not quite do the trick. But, I have finally been able to spend some significant time working through this and am posting here my best understanding and description of what is happening and what I figured out I needed to do. Your work on my behalf went a long way towards helping me develop my understanding, and for that I am grateful! The solution I am now implementing is working!
The problem I had with using ADO recordsets on unbound forms turns out to be a bit different than I first thought I understood. Your solution works for stand-alone forms (which is what I presented to you as a sample), but when you bring sub forms into the picture it falls apart.
Access 2007 seems to be different than 2003 in that if you try to run a Form_Current before you have run a Form_Open with unbound forms, it gets quite upset because of some sort of out of sync condition.
The way Access handles forms and subforms has always seemed to be backward to me, and it is especially troublesome in 2007 when using unbound forms.
The simplest explanation I can give for what happens in 2003 is:
When you open a parent form, the Form_Open of the sub form runs first, followed by the Form_Load of the sub form, followed by the Form_Current of the sub form. This is repeated for all Sub Forms. When the last one is processed, the Form Open, Form Load and Form Current of the parent form are run, unless one or more of the Sub Forms references data on the parent form, in which case the parent form routines are mixed in. This frequently results in multiple Form Currents being run on the parent form.
The code I was using somehow worked in 2003, but in 2007, some sort of out of sync condition occurs, causing the crash.
The only way I have gotten this to run in 2007 is to force the order of processing in what seems to me to be a more straight forward method:
On the Parent form, use a Form Open, Form Load and Form Current. Make sure the Form Current does not run until the Form Open, which has the assignment of the ADO database (Set me.recordset = ...), completes.
In the Sub Forms, eliminate all Form Open, Form Current and Form Load routines in order to control the exact order of processing to make sure the parent form is completely loaded first.
Since there is no Form Open, Form Current or Form Load on the sub form, once the ADO recordset is assigned on the parent form, the only way to get the sub form loaded with data is to execute the "Public" LoadFormData for each sub form.
If the sub form needs record navigation, then use a Form Current there, but use a Module variable (m_bLoad) to ensure its code does not run until after the LoadFormData is run, at which time you set the m_bLoad to True, so that any Form Current code can process.
In actuality, for every form, you can divide the functionality: there can be a LoadFormData which sets the recordset and loads any dropdowns that will be constant for every record presented. Then a RequeryFormData can contain logic to handle those things that change because movement was made from one record to another.
When the record is changed on the parent form by a Form Current, you must reload the recordsets on the sub forms based on values on the parent form. This may seem like a lot of processing, but it results in only the absolutely necessary data being retrieved.
If you do this, then the order of processing will be:
Load Parent Form
Load Sub Form
Move between records on the Parent form
ReLoad Sub Form
This can, of course, be propagated for any amount of nesting of sub forms.
Access's "backwards" management of sub forms and parent forms, which works fine for bound forms and DAO recordsets, is out of the picture and we are controlling loading of forms more precisely.
Cheers.