Question Access 2010 Unexpectantly Stops Working

[rjp99]

My office recently transitioned from Windows XP and Office 2007 to Windows 7 and Office 2010.

I built a couple of new Access databases using Access 2010 and with both of them, I've experienced situations where the program abruptly stops working - I get a message boxing saying something like "The program has stopped working - do you want shutdown or attempt to fix the problem".

Regardless of the option I select, the program shuts down.

Has anybody else experienced this problem? Is this a known bug in Windows 7/Access 2010 environment? Is there a standard fix that I can implement to stop this from happening? Thanks.

 

[pr2-eugin]

Hello rjp99, Welcome to AWF..

Well it is a bit too early to investigate the problem with Win7/A2010.. Do you have any VBA code in your DB? If so try Compiling the DB.. If not try a Compact and Repair..

This sometime is the result of some bizarre or improperly structured coding.. Check at what point the Error occurs and take it from there..

 

[rjp99]

Thanks Paul for the response and the welcome.
I will give your suggestions a try. I have a follow up question – in your note, you said this problem sometimes is the result of some bizarre or improperly structured coding. Can me a couple of examples of ‘bad coding’?
I’m new to Access (the program itself) as well as to programming (vba or otherwise). The vast majority of coding I have is designed to execute ‘Make Table’ queries. I create the coding by using QBE to build the query I want, switch to ‘SQL’ view when I’m done and copy/paste the results into a vba module.
Can/does this practice lead to bad coding? The only coding I’ve created from scratch is coding to build a new, empty table on the fly. Thanks, Ron

 

[pr2-eugin]

Hello Ron, When I say improperly structured Code.. Some statements need to have a closing statement.. Like If...End IF, For...Next, Sub...End Sub.. If for example you have a very large Nested If's you might have lost track of one End If somewhere in the middle.. Which sometime would not be immediately highlighted, but will put the compiler in a puzzled state.. Copying SQL code from QBE, is not a bad practice at all.. That actually is good.. Because it will avoid any spelling errors or ambiguous naming errors.. But just make sure they are properly concatenated..

Say if your SQL code is..

SELECT BREACHES_Tbl.B_ID, BREACHES_Tbl.DateOfBreach, BREACHES_Tbl.DetailsOfBreach
FROM BREACHES_Tbl;

When you paste it.. Either join them in one single line or properly concatenate, with proper spacing..

strSQL = "SELECT BREACHES_Tbl.B_ID, BREACHES_Tbl.DateOfBreach, BREACHES_Tbl.DetailsOfBreach FROM BREACHES_Tbl;"
[COLOR=Green][B]'OR[/B][/COLOR]
strSQL = "SELECT BREACHES_Tbl.B_ID, BREACHES_Tbl.DateOfBreach, BREACHES_Tbl.DetailsOfBreach " [B][COLOR=Red]& _[/COLOR]
[/B] "FROM BREACHES_Tbl;"

 

[rjp99]

Hello Paul,

Work prevented me from being able to get back to you yesterday but thank you again for your help; I did a compact & repair and everything seems to be working fine.

If I may, can I bother you with 3 more questions?
1. what exactly does compact & repair do and should I do this frequently?

2. I noticed that you use ' & _ ' to concatenate. I've been approaching concatenation as follows:
strSQL = “SELECT BREACHES_Tbl.B_ID, BREACHES_Tbl.DateOfBreach,”
strSQL =strSQL & “ BREACHES_Tbl.DetailsOfBreach ”
strSQL = strSQL & “ FROM BREACHES_Tbl; “

Does doing it my way have a negative impact on performance/code stability.

3. I’ve been trying to figure out how to copy a recordset (from a Select query vs. an Action query) directly into an Access table and not had any success. Is this because it’s impossible. If so, why is Access designed like this? I mean you can copy the recordset directly into Excel. Thanks a million!

 

[pr2-eugin]

Good to hear.. And am glad to answer your Queries..

1. what exactly does compact & repair do and should I do this frequently?

Compact and Repair, solves various problems.. Some Compiler error that has not been resolved, refresh/reseed auto number in tables, saves a lot of space.. It is highly recommended a Compact & Repair to be performed.. It can be set to do as "Compact on Close" in your File->Options menu..

2. I noticed that you use ' & _ ' to concatenate. I've been approaching concatenation as follows:
strSQL = “SELECT BREACHES_Tbl.B_ID, BREACHES_Tbl.DateOfBreach,”
strSQL =strSQL & “ BREACHES_Tbl.DetailsOfBreach ”
strSQL = strSQL & “ FROM BREACHES_Tbl; “

Does doing it my way have a negative impact on performance/code stability.

No you can carry on the way you are doing.. I only expanded a suggestion.. IMO, I would be doing the way you have done, as it not allow any small loop holes for improper concatenation..

3. I’ve been trying to figure out how to copy a recordset (from a Select query vs. an Action query) directly into an Access table and not had any success. Is this because it’s impossible. If so, why is Access designed like this? I mean you can copy the recordset directly into Excel. Thanks a million!

Action Query requires Execute method.. Select Queries require OpenRecordset.. There are some difference.. To get you started on using Recordset try the following links.. Post back if you need more help..
http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners
http://allenbrowne.com/ser-29.html