Is my VBA Corrupting my databases?

[GBalcom]

I just began using VBA as a tool in my db's...I've made a half dozen db's without an issue here at work for little things without fail, usually connecting to our SQL server to pull some bit of information down for a report to two.

The last two have had issues, randomly shutting down when a button is pressed. This last one I just created an hour ago. Seemed to work great, then I deployed it to someone using Access 2010 runtime, and now it's having issues...

The issue specifically is an error popping up (see attached "capture") when I click a button to send query results to excel.

This again worked fine before deployment. When I Open the db on my computer, I can get the query results directly, but have the same issue if I click the button.

Worse yet, I can't go into the VBA at all for the form (or db). as it just shuts down access.....

Any ideas why this is happening? Please see attached jpegs...

 

[AccessMSSQL]

It sounds like your VBA modules are getting corrupted some how. How are you deploying this? Are you deploying to an ACCDR file? Do you have proper error handling set up in your OnClick event of button. Is it just one button that is producing the error or is it any button you click on?

 

[GBalcom]

The front end is just a accdb on the server, the backend is the SQL database, (no access tables)

I do not have any error handling in it....guess I should put some in...

but, I was just able to delete the event code in the properties panel and re open it, so I'm now making a macro, but I'm having issues with it not recognizing the controls on the form now....very strange.

 

[pr2-eugin]

Try compact and repair... Then try to go to the code window (ALT + F11) and try COMPILE again, it will highlight the error.. See if you can workout the error if not post back the error for us to see..

 

[GBalcom]

Pretty interesting phenomenon occurred this morning.....

I started the db and used the Alt + F11 to get into the code (Thanks for the tip Paul!), and noticed I could get in it okay...then I ran the code and it worked fine...

Next I went into the backup file that Access created yesterday on the first crash, and that ran fine (This definitely did not run OK yesterday!)

So.....Now I'm stumped...apparently it's something with our network?!? or some computer setting, or something with our SQL server that hiccups occasionally with the vba code?!? I'm totally lost, so any help would be greatly appreciated. I will talk with our part time IT guy this morning, to see if he has any ideas...

 

[pr2-eugin]

Sometime copying the file (accdb file) to a new location will help as it normally asks you to enable content when you do, it performs a compact and repair.. which is what I suggested you to do.. Now, did you try Compiling the code again? (under Debug menu) When you compile the error will be highlighted.. based on the error you have posted earlier, I believe your code has a missing statement, which should be addressed..

 

[GBalcom]

Thanks Paul,
I did compile and it seemed to work fine..But please see the code below:

Private Sub cmdExport_Click()
DoCmd.OutputTo acOutputQuery, "qryApPoReport", "Excel97-Excel2003Workbook(*.xls)", "AP to PO Report.xls", True, "", , acExportQualityPrint
MsgBox "Your report has been exported. Have a good day Al "

End Sub

Let me know if there is anything missing...

Thanks,
Gary

 

[pr2-eugin]

Hello Gary, I do not see anything that is going wrong, as mentioned some errors will be corrected by simple Compact and Repair.. Which is why it is highly recommended to do so.. Code looks fine, might help if you had a small error handling method.. atleast by which it will be able to bypass rather than crash..

Private Sub cmdExport_Click()
On Error GoTo errHandle
    DoCmd.OutputTo acOutputQuery, "qryApPoReport", "Excel97-Excel2003Workbook(*.xls)", "AP to PO Report.xls", True, "", , acExportQualityPrint
    MsgBox "Your report has been exported. Have a good day Al :-)"
    exitOnErr:
        Exit Sub
    errHandle:
        Call MsgBox("ERROR OCCURED :( Cannot Export the Query" & Chr(10) & Err.Description ,vbCritical)
        Resume exitOnErr
End Sub

 

[GBalcom]

thanks for the error handling!

 

[pr2-eugin]

You are welcome Gary.. Good luck..

 

[PeterF]

The front end is just a accdb on the server, the backend is the SQL database, (no access tables).

Are multiple users using the front-end?
The whole purpose of a front-end is that it should be on the local computer of every user to avoid corruption. As a bonus it makes easier to do the development.

 

[MSAccessRookie]

I did not notice if anyone had asked you to look at the top of each of your VB Modules and check to see that two Database related commands were there. If the second of these, (Option Explicit) is missing, MS Access will Declare undefined variables as Type Variant, which may or may not work for you. Adding Option Explicit will force you to identify all variables as the Type you intend them to be.

[B][COLOR=red]Option Compare Database[/COLOR][/B]
[B][COLOR=red]Option Explicit[/COLOR][/B]

 

[GBalcom]

Hi guys,
I don't believe this issue is solved after all. I was able to corrupt yet another database I've been working in. I open it up this morning and everything is OK....very, very strange.....

The only thing I can go on is that it's happening in the early afternoon, and it seems fine again in the morning....and this didn't start happening at all until I started using VBA in my db's...

I am using Option Explicit, and these are for 1 concurrent user.

The SQL database that these db's are looking to is for our Visual Manufacturing system. One thing I noticed is that there are references for this under tools => References (See attached snipped). Should I have these turned on?!?

Thanks for any insight,
Gary

 

[RainLover]

You do not have a corrupt Database. What you have is some incorrect Code. This is only appearing (at a guess) after a sequence of events.

You need to document what is happening when. This way your error can be repeated. Then you will need to post a copy of your DB so that someone can locate the problem.

If you do post then please use Access 2003 or or earlier. Not everyone has 2007 or 2010.

Also you will need to fill some tables with a half a dozed records so we can test.

 

[GBalcom]

Ok, I believe I finally found the culprit...

http://answers.microsoft.com/en-us/...n/d2bf6175-075a-4a12-a2b1-f55d40af271b?auth=1

 

[RainLover]

Good ole 2010 problem again.

 

[GBalcom]

yeah, it gets even more strange.....I tried to download and install the hotfix for this, but it won't install because it says there is no applicable product....even though I have Access 2010 SP1 on my computer.....with the bad version of the Eve7 (?) dll file.