Whay does an update CRASH Access?

Running Access 2003 on XP, doing a select from a table and passing the 
results to this subroutine to update another table.  When running in 
debug 
mode, it works.  When I stop it an anaylze the update statement, copy and 
paste into a new query, it works.  Running without debug turned on, 
Access 
crashes (on three different installations of Access).  

What I know to be true:
  1. the input record set contains records, all fields are non-null
  2. the datatypes are correct
  3. it crashes on the first record

Any clues?
----
Sub UpdLicenseGrantedOutput(rstOutput As Recordset)

    	Dim SQL_Upd As String
    	Dim dbs As Database
    	Set dbs = CurrentDb

   ' Enumerate the specified Recordset object.
   With rstOutput
      Dim rc
      Do While Not .EOF And Not .BOF
        rc = rc + 1
         SQL_Upd = " Update tbllicenseRightsSummary Set licensegranted = 
" 
& .Fields(4) & _
                    " where tbllicenseRightsSummary.Country = '" & 
..Fields
(1) & "' " & _
                    " and companyName = '" & .Fields(0) & "'"
        On Error GoTo updLicenseGrantedOutputError
        dbs.Execute SQL_Upd, dbFailOnError        <------ Access crashes
        .MoveNext
      Loop
   End With
    rstOutput.Close
    dbs.Close
    
    Exit Sub
updLicenseGrantedOutputError:
    MsgBox "updLicenseGrantedOutput: Update failed: " & Err.Description & 
vbNewLine & "Sql=" & SQL_Upd

End Sub

0
OceanView
10/2/2007 9:02:17 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
818 Views

Similar Articles

[PageSpeed] 44

Hmm: you're performing an update in a loop. We don't know what's in the 
rstOutput recordset, so perhaps something in there is being modified?

Is there any chance of replacing the entire thing with just an UPDATE query 
statement? Perhaps it could be done with a JOIN?

Presumably you have tried the obvious things, like ensuring that Name 
AutoCorrect is off, and doing a compact/repair.

There's a couple of things that could be done differently in the code, such 
as settup up the error handler before the loop. Also, closing the default 
database is not correct, but it does not to be de-referenced even if control 
gets passed to the error handler. We are assuming that the input recordset 
is the correct initialized (at the right record.) Since it was opened in a 
preceeding procedure, I'm not sure if you want to destroy it in this one.

So, a slightly modified version of the code is below. I doubt it will make 
the difference you need though:
Sub UpdLicenseGrantedOutput(rstOutput As DAO.Recordset)
On Error GoTo updLicenseGrantedOutputError
   Dim SQL_Upd As String
   Dim dbs As Database
   Dim rc As Long
   Set dbs = CurrentDb

   ' Enumerate the specified Recordset object.
   With rstOutput
      Do While Not .EOF
         SQL_Upd = "UPDATE tbllicenseRightsSummary " & _
            "SET licensegranted = " & .Fields(4) & _
             " WHERE ((tbllicenseRightsSummary.Country = """ & _
             .Fields(1) & """) AND (companyName = """ & _
             .Fields(0) & """));"
         dbs.Execute SQL_Upd, dbFailOnError  '<--- Access crash
         rc = rc + 1
        .MoveNext
      Loop
   End With

    rstOutput.Close     'Are you sure you want to do this?

Exit_Handler:
    Set dbs = Nothing   'Don't close! dbs.Close
    Exit Sub

updLicenseGrantedOutputError:
    MsgBox "updLicenseGrantedOutput: Update failed: " & _
        Err.Description & vbNewLine & "Sql=" & SQL_Upd
    Resume Exit_Handler
End Sub

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"OceanView" <f@chance.com> wrote in message
news:Xns99BDAD448F008oceanbaby@66.250.146.128...
> Running Access 2003 on XP, doing a select from a table and passing the
> results to this subroutine to update another table.  When running in
> debug
> mode, it works.  When I stop it an anaylze the update statement, copy and
> paste into a new query, it works.  Running without debug turned on,
> Access
> crashes (on three different installations of Access).
>
> What I know to be true:
>  1. the input record set contains records, all fields are non-null
>  2. the datatypes are correct
>  3. it crashes on the first record
>
> Any clues?
> ----
> Sub UpdLicenseGrantedOutput(rstOutput As Recordset)
>
>    Dim SQL_Upd As String
>    Dim dbs As Database
>    Set dbs = CurrentDb
>
>   ' Enumerate the specified Recordset object.
>   With rstOutput
>      Dim rc
>      Do While Not .EOF And Not .BOF
>        rc = rc + 1
>         SQL_Upd = " Update tbllicenseRightsSummary Set licensegranted =
> "
> & .Fields(4) & _
>                    " where tbllicenseRightsSummary.Country = '" &
> .Fields
> (1) & "' " & _
>                    " and companyName = '" & .Fields(0) & "'"
>        On Error GoTo updLicenseGrantedOutputError
>        dbs.Execute SQL_Upd, dbFailOnError        <------ Access crashes
>        .MoveNext
>      Loop
>   End With
>    rstOutput.Close
>    dbs.Close
>
>    Exit Sub
> updLicenseGrantedOutputError:
>    MsgBox "updLicenseGrantedOutput: Update failed: " & Err.Description &
> vbNewLine & "Sql=" & SQL_Upd
>
> End Sub
> 

0
Allen
10/3/2007 10:42:08 AM
Could it simply be matter of disambiguating dbs?

Dim dbs As DAO.Database

I could be wrong, but always start w/ simplest first...

good luck,

gary

"OceanView" wrote:
> Running Access 2003 on XP, doing a select from a table and passing the
> results to this subroutine to update another table.  When running in
> debug
> mode, it works.  When I stop it an anaylze the update statement, copy and
> paste into a new query, it works.  Running without debug turned on,
> Access
> crashes (on three different installations of Access).
>
> What I know to be true:
>  1. the input record set contains records, all fields are non-null
>  2. the datatypes are correct
>  3. it crashes on the first record
>
> Any clues?
> ----
> Sub UpdLicenseGrantedOutput(rstOutput As Recordset)
>
>    Dim SQL_Upd As String
>    Dim dbs As Database
>    Set dbs = CurrentDb
>
>   ' Enumerate the specified Recordset object.
>   With rstOutput
>      Dim rc
>      Do While Not .EOF And Not .BOF
>        rc = rc + 1
>         SQL_Upd = " Update tbllicenseRightsSummary Set licensegranted =
> "
> & .Fields(4) & _
>                    " where tbllicenseRightsSummary.Country = '" &
> .Fields
> (1) & "' " & _
>                    " and companyName = '" & .Fields(0) & "'"
>        On Error GoTo updLicenseGrantedOutputError
>        dbs.Execute SQL_Upd, dbFailOnError        <------ Access crashes
>        .MoveNext
>      Loop
>   End With
>    rstOutput.Close
>    dbs.Close
>
>    Exit Sub
> updLicenseGrantedOutputError:
>    MsgBox "updLicenseGrantedOutput: Update failed: " & Err.Description &
> vbNewLine & "Sql=" & SQL_Upd
>
> End Sub
> 


0
Gary
10/3/2007 10:43:13 AM
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:uV83NoaBIHA.4956@TK2MSFTNGP06.phx.gbl: 

Thanks, Allen

I think I found  a solution, but still not entirely sure of the cause.  
This is inherited code and, yes, does violate some conventions like 
'scope of control' by closing the recordset that were passed, though 
most of them would happen after the problem point. Because of that I'm 
reluctant to do any rewrites.  (I won't go into it, but this is a duct-
tape situation!)

What I found is that the passed query should not have been opened in 
'read-only' mode, even though it's not being updated. Removing that 
seems to fix it.  I don't know why this would cause a problem.  It's 
possible I just moved the problem rather than fix it, but for now, the 
duct tape is holding!


> Hmm: you're performing an update in a loop. We don't know what's in
> the rstOutput recordset, so perhaps something in there is being
> modified? 
> 
> Is there any chance of replacing the entire thing with just an UPDATE
> query statement? Perhaps it could be done with a JOIN?
> 
> Presumably you have tried the obvious things, like ensuring that Name 
> AutoCorrect is off, and doing a compact/repair.
> 
> There's a couple of things that could be done differently in the code,
> such as settup up the error handler before the loop. Also, closing the
> default database is not correct, but it does not to be de-referenced
> even if control gets passed to the error handler. We are assuming that
> the input recordset is the correct initialized (at the right record.)
> Since it was opened in a preceeding procedure, I'm not sure if you
> want to destroy it in this one. 
> 
> So, a slightly modified version of the code is below. I doubt it will
> make the difference you need though:
> Sub UpdLicenseGrantedOutput(rstOutput As DAO.Recordset)
> On Error GoTo updLicenseGrantedOutputError
>    Dim SQL_Upd As String
>    Dim dbs As Database
>    Dim rc As Long
>    Set dbs = CurrentDb
> 
>    ' Enumerate the specified Recordset object.
>    With rstOutput
>       Do While Not .EOF
>          SQL_Upd = "UPDATE tbllicenseRightsSummary " & _
>             "SET licensegranted = " & .Fields(4) & _
>              " WHERE ((tbllicenseRightsSummary.Country = """ & _
>              .Fields(1) & """) AND (companyName = """ & _
>              .Fields(0) & """));"
>          dbs.Execute SQL_Upd, dbFailOnError  '<--- Access crash
>          rc = rc + 1
>         .MoveNext
>       Loop
>    End With
> 
>     rstOutput.Close     'Are you sure you want to do this?
> 
> Exit_Handler:
>     Set dbs = Nothing   'Don't close! dbs.Close
>     Exit Sub
> 
> updLicenseGrantedOutputError:
>     MsgBox "updLicenseGrantedOutput: Update failed: " & _
>         Err.Description & vbNewLine & "Sql=" & SQL_Upd
>     Resume Exit_Handler
> End Sub
> 


-- 
Posted via a free Usenet account from http://www.teranews.com

0
OV
10/3/2007 1:05:48 PM
Reply:

Similar Artilces:

ACCESS on a new iBook?
Hi, Has anyone any experience on running Access under Virtual PC or similar? I have a new model iBook 1.42 Ghz, 512MB RAM (soon to be upped to 1 GB) and 10.4.6. I'm wanting it to brush up my knowledge for use at work so won't be using huge data sets, just playing with new builds & small amounts of test data. I don't really want to buy an old PC just for this so just wanted to check it wouldn't be unusably slow under virtualisation software. Any advice greatly appreciated. Cheers Jason ____ Hi Jason, Microsoft Access works fine in Virtual PC. Yes, it's slightly...

Try that update which comes from the M$ Corp.
--pxulfresdind Content-Type: multipart/related; boundary="xepmincpbkqfdfbp"; type="multipart/alternative" --xepmincpbkqfdfbp Content-Type: multipart/alternative; boundary="zhaxriplmgq" --zhaxriplmgq Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to p...

accessing ActiveX control enums in MFC
I have an ActiveX contol whose tlb (using OLE/COM viewer) shows // There are a couple of more of these, but this is typical example typedef enum { RF_MCS86 = 1, RF_BINARY = 2, RF_ASCII_BINARY = 3 } EROMFormat; This enum is accessed from another function from within the control as STDMETHOD Func(SAFEARRAY **Ptr, EROMFormat Fmt, Long * Retval) I have dropped the control onto my form and MFC has generated the wrappers for the methods except for the ones having SAFEARRAY arguments Now, not only does MFC not generate the function because of the familiar // method &...

Is Version Updated after sp-1 is updated on Money 2007
After you get the automatic software downloaded is it necessary to click onto the sp-1 to install? -- Deb No it is automatic. If I recall, after the SP1 is downloaded, you are prompted to close Money and restart it. After you restart Help > About should give you a version number ending in .1024. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Rebelleheart" <Rebelleheart@discussions.microsoft.com> wrote in messa...

Online Updates
When trying to complete online update, the computer states the Money has encountered an error and must shut down. Have upgraded to 1415. Help! BTW: I'm using Money Plus Deluxe and XP "Cheyne14" wrote: > When trying to complete online update, the computer states the Money has > encountered an error and must shut down. Have upgraded to 1415. Help! ...

PST file will not allow me access
I have a pst file from Office 2000 that I copied to cd. AS I tried to add it to my new computer and office 2003 I get the message that I do not have rights to access this file. How can I reset the permissions? There is not any password on it that I set by choice. Thanks Sal ...

VPN and Public folders access
Hi all I have 3 exchange servers that are all in the same admin and routing group, two out of hte three servers are exchagne 2003 and hte third is a 5.5 server. All serves are in different parts of the country and they are all connected by high speed links. The problem that i have been noticing lately is when a user VPN's into the network (outlook 2003) the users outlook is trying to connect the public folders to an exchagne server that is not local to there network, when this happens a dialog box pops up asking the user for there username password, this mail server is in another ...

Outlook keeps crashing #4
Apologies for x-posting. but not sure which is best forumn. Iinstalled office2003 SP1last njight, and now, every 30 seconds Outlook is trying to send an error report to MS. Can someone tell me what is likely to be going on Thanks A ...

How do I repeat my header on each page of an access form
!@#$%^&*?! I am frustrated. I want to repeat a header on each form. Help states that there is a repeat property that needs to be set to yes. I can't find it anywhere! I've double clicked, right clicked on every section and box and cannot find this property setting. Help!!! Evon wrote: > !@#$%^&*?! I am frustrated. I want to repeat a header on each form. Help > states that there is a repeat property that needs to be set to yes. I > can't > find it anywhere! I've double clicked, right clicked on every section and > box and cannot fin...

Updating Money Deluxe 2007 question
Will Microsoft keep the Money updates available permanently, as they do with updates for old operating systems like Windows 98? The reason I ask is that, at least in my experience, Money updates have never been a manually downloadable file, but rather once you install the program, Money calls out on the Internet and looks for updates. If Microsoft doesn't keep the updates online, is there any way to download the update files now for permanent keeping? I did a test. I installed Money 2007 Deluxe on my Virtual PC. As soon as I went into the program after installation, it updated ...

Upgrading to newest money money updates... errors..
I've been using Money 2005 for a month or so... I decided to switch which computer I use it at at home. Just yesterday I think was the last update that asked me to back up my file. Now, I've installed the program on a new computer we got and everytime it tries to download the latest money update it crashes when it goes to install it. It says I dont have access to the file or that it may be 'read only' or not enough disk space (needs 100 megs). I can use the file perfect on the orginal computer, it's not marked read only, and there's over 40GB of space available....

Reconcile to GL access
Can anyone tell me how to grant a user access to the reconcile to gl routine? I've marked "reconcile purchasing", but this isn't it. I can't find a "reconcile to gl"option to mark under financial or purchasing. Thank You. Tracey Hi Tracey, The "Reconcile to GL" window is under the access list of: Product: Microsoft Dynamics GP Type: Windows Series: Financial In GP 10, you need to accomplish this by providing access to the Security Tasks Setup window while in GP 9 and below this under User Security. Hope this helps! Chee...

Removing Anonymous Access on Backend Exchange Servers
Hi, We are running Backend & Front End Exchange Servers, Front End the anonymous access has been disabled. I am in the process of removing anonymous access in Backend also, but the problem is there are many applications which are using backend servers to send mails, most of it can be configured to authenticate before sending mails, but there are a quite a few legacy applications which cannot authenticate before sending mails (The code is not opensource & cannot be modified). I tried to allow relaying based on IP's, but still those legacy machines are not able to send mail...

Recordset Not Updateable
I have set up an access database to track technical support and training services. I'm pretty new to Access and Very new to VB programming. I have hit a wall with something and have found no solution in help or on these discussion groups. Here's what I'm attempting to do, from the user's perspective. This process works great up to the very end, then things fall apart. Let's say the user wants to look at all open support cases. The user presses a button called "All Open Cases." A very basic query runs, and the results are shown in a read-only (contin...

Access 03-17-08
From a menu designed using access a query was run and the response we received says the records are deleted. The query's function is to search a table which has 6,000 records. The table with the records can be seen. However We cannot cannot do sorts or run queries on it. How do we free up the data access says is deleted.? It also says unrecognized data format when we try to look at the individual from related the the query on the menu. At times like this, nothing beats a good backup. In fact make a complete backup of your database now and put it away for safe keeping. AFTER makin...

can you make one worksheet update another
I have two similar worksheets within the same workbook. One sheet is sorted by name and the other sheet is sorted by a date for a speicfic name. Is there a way to change the date on the sheet, sorted by name, and that will automatically find the name on the other sheet(sorted by date) and change the date to the second sheet? Jaime, The normal way is to have one table, and sort it as needed when needed. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jaime S." <Jaime S.@discussions.microsoft.com> wrote in mess...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

Mircosoft Money 2003 Deluxe share update
> Hi , > > I have Mircosoft Money 2003 Deluxe which I use mainly to record shares > trades in the Portfolio area. I am wondering if someone could explain me the > process to update my shares prices. i understand there is a manual way of > doing that but I would prefer having it done automatically. > > Cheers Versions that still have "online services" use Internet Update to get stock quotes for investments defined with symbols that are available online. Your version may be too old to still qualify for online services. "fab" <fab@discussio...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

threads accessing private methods
Hi! Here is a simple example on a timer accessing the private method timer_Elapsed. This works fine. In this example one might consider that the timer_Elapsed must be public because the one that is calling is not within the class. So I just wonder is it always in such a way that when the framework or the OS is calling upon a method it can be declared as private and it will work ? class Test { static void Main() { Timer timer = new Timer(); timer.Elapsed += new ElapsedEventHandler(timer_Elapsed); timer.Interval = 1000; timer.Enabl...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

update shape
I want to use a consistent shape in many pages. Is there a way to create a master copy such that when I update the text on a master copy all copies are updated. I created a new stencil and new master but updating the master doesn't seem to update the copies (after i save the file). Amos You have to update the master in the local, or "Document Stencil" Every Visio drawing contains a "hidden" Document Stencil, that carries copies of masters from "external" stencil files. When you drag a new shape into a drawing, the master gets copied from the external .v...

windows update fail...... What now?
ok so I had a problem with windows update downloading but not installing.. I did go to the windows file in the C: drive and manually installed it myself. Thinking that it would fix my install problem.. I was wrong.. I next have found out that in my event logger that my IIS6 is not working properly or something. It doesn't look right and in my event logger it says my security assessment tool 4.0 error 1303 I am the administrator and logged in already so what the heck? And I also would like to say that my computer is old and it's a IBM think pad running windows xp perfessio...

401K Mutual Fund Manual Price Updates
I have a 401K that tracks NAV of the funds differently than the prices auto-downloaded by Money for the same symbol (don't ask me why???). I have turned off Auto-Update of those funds so I can manually update the fund prices myself. The problem is that no matter how many times I try to delete previous on-line price updates, they remain with the Fund. This is kind of minor, but also kind of annoying. I've learned when I add a new fund to this account to disable price updates before my next update and this works for new fund additions, but the old funds are still maintaining t...

Access denied to event invitation
People I invite to a calendar event get an access denied error when the try to accept -- jquinter ----------------------------------------------------------------------- jquintero's Profile: http://www.msusenet.com/member.php?userid=214 View this thread: http://www.msusenet.com/t-187052813 ...