Access VBA Defies Logic
-
Hi,
Access VBA is baffling my mind. I'll give you the full details below, but the gist of the problem is when I insert a record programetically into a table using ADO, and open a form that uses that table as its record source, the new record doesn't show up in the form until I close it and open it again. Even the record count is off: when I programetically open the form it shows 877 records, when I manually close it and open it again it shows 878.
Here's the details of my problem, read on if you care to...
I'm doing a marketing database for a consulting firm. They need to keep track of Opportunities, Proposals and Projects. Here is a simplified data schema...
As you can see, each Opportunity CAN lead to 1 or more proposals, but it doesn't have to (in reality only about 1/3 do). The same situation exists with Proposals to Projects.
Some of you who work for yourself may find this familiar. Anyway, in tbl_Opportunities, the OpportunityStatus field is a Combo box. The possible statuses are 'New Opportunity', 'Undecided', 'Passed', and 'Proposal'. The onChange event for that combo box checks to see if the new value is 'Proposal'. If it is, it
- Inserts a new record into tbl_Proposals using an ADO Insert Command.
- Opens up the Proposals form so the user can enter in the pertinent Proposal data.
Here is the VBA code that is supposed to do that...
- Private Sub OpportunityStatus_Change()
- If Me.OpportunityStatus.Value = 5 Then 'Proposal
'Check to see if there already is a proposal (omited)
cnxn.Open CurrentProject.Connection
Dim cmdCommand As New ADODB.Command
cmdCommand.ActiveConnection = cnxn
cmdCommand.CommandText = "INSERT INTO tblProposals " _
- (fkOpportunityID) VALUES (" & Me.pkOpportunityID.Value & ")"
cmdCommand.Execute
Set cmdCommand = Nothing
cnxn.Close
Set cnxn = Nothing
- 'Go to new Proposal
DoCmd.OpenForm "Proposals"
DoCmd.GoToRecord , , acLast
- DoCmd.Close acForm, "Opportunities"
- End If
- End Sub
The first part of the code works fine, the record is inserted, but 99% of the time the second part does not take the user to the record that was just inserted, as I mentioned above, the record count is off until I close the form and re-open it.
- When it does work it's always the first time I attempt to do this after opening the MDB file, that always works, subsequent attemps to not.
- I've also tried doing a search in the Proposals form, instead of going to the last record, as in...
- OID=Me.pkOpportunityID.Value
- DoCmd.OpenForm "Proposals"
- Forms!Proposals.fkOpportunityID.SetFocus
DoCmd.FindRecord OID, , , , acSearchAll, , acCurrent
DoCmd.Close acForm, "Opportunities"
with the same results.
What I'm thinking is there's some kind of time delay when I insert the record into the talbe using ADO to the time the Proposals form can see it.
Any ideas / insights / solutions? All contributions are welcome.
Thank you so much for your help, in advance.
Aaron
-
Latest update. Doesn't seem to be related to a delay, because I do a
- For i=0 to 5000000
- 'Do Nothing
- Next
Loop and same results. I also tried programetically re-closing the Proposals form and opening it up again. Same results.
Aaron
-
Newsflash!
It IS actually a timing issue. It turns out 5,000,000 is just not long enough to make a difference, or that Access sees that it's an empty loop and ignores it. I know this because I inserted a msgBox right before I requerried the Proposal Form.
How can I work around this bug in Access?
Aaron
-
You can replace the 'do nothing with DoEvents so that something gets executed. You might also want to replace the 5M with 5K
. -
Does this time delay still exist when the table is a link table, say in SQL Server, rather than a local table?
Aaron
-
Just for kicks, have you tried creating a simple (parameterised) insert query that you could call instead of using ADO to do the insert? A possible advantage to this is that you keep your SQL code external to the code (could make it easier to find for other people who have to come around later and try to *fix* things
).Also, you're not doing anything that could lock the recordset in your omitted check for existing proposal are you?
You may also want to set your command type for the command object, apparently there are perormance implications of leaving it as adCmdUnknown (the default). The following is from msdn (ado command object)
If the CommandType property value equals adCmdUnknown (the default value), you may experience diminished performance because ADO must make calls to the provider to determine if the CommandText property is an SQL statement, a stored procedure, or a table name. If you know what type of command you're using, setting the CommandType property instructs ADO to go directly to the relevant code. If the CommandType property does not match the type of command in the CommandText property, an error occurs when you call the Execute method.
Steve.
-
Actually I am using an insert query, rather than the ADO AddNew method. It's not parameterized because I'm only inserting 1 value, an integer, which is not coming from a textbox so I figured it was safe. (besides it's not a web application anyway).
I'll look into changing the CommandType property. BTW do you know what the correct enumeration of "SQL Query" is?
This is definitely a Time-delay or buffer-flush type of issue. I'm currently experimenting with different types of loops to wait the requisite 6 seconds or so before the updated recordsource shows up (you have to pause before you requery the control's record source, as in
For i = 0 To 30000000
j = i + Int(i / 5)
Next i
Forms!Proposals.Requery
Seems kind of silly actually. Since I'm ultimately going to port this thing to SQL Server, I'm hoping that that will fix the delay issues.
Thanks.
Aaron
-
Sounds v.weird.
You'll probably want 'adCmdText' for now but if you change your approach (see below) and use MSSQL stored procs, then you'll want to use the stored procedure command type, but in doing so you'll prob then need to implement Parameter objects for the parms collection too...
re; the Insert 'Query' what I meant was create an Access Query (ie what Access uses to implement views and stored procs) and then call this via the MSAccess objects rather than having to create the ADO objects etc. This will only work with your MSSQL later on *if* you use linked tables to the MSSQL server, whereas your current approach (ADO directly) will let you specify the server in the conxn string.
You may also want to catch the ExecuteComplete event that is (should be) raised after making the Execute call. This will (should) contain the status of the call etc and may give you some insight into why there is a delay.
You could also check the State property of the command object, apparently there is a 'adStateExecuting' value, so you could put a 'while' loop in to sit around and wait for it to change to closed. NB this is a bitvalue, so can be addition of bits (eg adStateOpen + adStateExecuting).
Lastly, there's couple of conxn string settings that may help, look up the 'Flush Transaction Timeout' and also the 'user commite synch', either (or both) of these may be causing waits.
HTH,
Steve.
-
Thanks for your suggestions Steve,
using
cnxn.Open CurrentProject.Connection
cnxn.Properties("Jet OLEDB:Flush Transaction Timeout") = 0
cnxn.Properties("Jet OLEDB:User Commit Sync") = True
I was able to speed things up significantly - it takes about half the time. So, thanks.
the Command's State property always returned 0, so the delay's not in the command object. I wasn't able to trap the ExecuteComplete event. Probably my syntax was off.
I'm hoping that the upsizing to MSSQL will make this a non-issue, but it's still weird, and logic-defying.
BTW what does NB mean?
Thanks.
Aaron
-
It's an acronym (for latin Nota Bene) when translated means 'Note Well' , well, at least that's what they taught us here in Aus
I googled it and found this -> http://www.physics.uiuc.edu/Education/398IPR/Lectures/LatinTerms/tsld009.htmTalking acronymns, below is a copy of part of an email I received the other day re: BTW.
oh by the way what is BTW?? no, i didn't make that upre: you're upsizing to MSSQL, are you going to go for stored procs? It could make your code a lot cleaner, plus you could snaffle (ie search on the web and find then use) someone else's code that provides helper functions for executing stored procs, especially with parameters. ie you could end up with a nice line of code in your access VA looking like ...<some_boolean> = MyHelperClass.ExecSproc "<sproc name>", Array("<parmvalue1>", "<parmvalue2>", .....)
where the helper class then takes the paramter array, creates the ADo parms for you and then executes the passed in stored proc, making it pass back a bool would let you know if it had success or failure in executing. Means you can encapsulate the sproc execution code seperately to your main Form code, also let's you isolate the DB functions (ie inserts, selects etc) to stored procedures within MSSQL.Anyways, just a thought.
Good luck with it all.Steve.
-
I've come across this exact problem in the past and i can confirm it does affect access with linked tables held on SQL server.
I can't remember how i got round it, however I do remember that on moving to SQL linked tables all of my recordsets had to have additional parameters (such as dbdynasetseechanges) can't remember if this cleared up the problem tho.....
MVDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply