EzDevInfo.com

How does one decompile and recompile a database application?

I have an Access database application and I would like to know the proper way of decompiling and recompiling it.

How to show "Open File" Dialog in Access 2007 VBA?

How would I go about showing an open file (or file select) dialog in access 2007 VBA?

I have tried using Application.GetOpenFileName as I would in Excel, but this function doesn't exist in Access.

Non-web SQL Injection

There seems to be some hysteria about SQL Injection attacks. Most recently, here

http://stackoverflow.com/questions/505838/vba-simple-database-query-from-word

If I'm creating a macro in Excel that connects to an Access database, do I really have to be concerned about SQL injection? It's not on the web, it's used in my office (you guys remember desktops right?). I'm not concerned that my co-workers are going to sabotage me. If they're smart enough to do a SQL injection, aren't they smart enough to crack my add-in password and just change the code?

Textbox null problem

I have a textbox and a button on my Access form. In the click event of the button i want to see if the textbox is empty, if it is, nothing will be executed. So i use

If Me.textbox.Value = Null Then
    Exit Sub
End if

But it doesn't work... I checked the textbox.value in the execution window and it is Null, but the if clause just doesn't work... Why?

EDIT: @Dimse, I tried "", doesn't work. And also textbox.text = Null, it pops an error telling me the textbox is not active.. Very strange.

VBA + Threads in MS Access

How can I create a process running on a separate thread in MS Access VBA? I would like to create a process that will just sit and wait for a message.

Is there an "On Close" event for MS Access VBA?

Am trying to perform some specific actions, before closing the access database (whenever the database is closed by the user).

I know we can use Auto_Close function in Excel VBA to force events before closing the book. Is there a similar function in MS Access VBA? If so, an example would be appreciated.

Please let me know if you need more information. Thank you.

How to Round in MS Access, VBA

Whats the best way to round in VBA Access?

My current method utilizes the Excel method

Excel.WorksheetFunction.Round(...

But I am looking for a means that does not rely on Excel.

Is there a need to set Objects to Nothing inside VBA Functions

I always read that it is recommended to set objects to nothing, once I am done with them. But I normally use them only in functions inside forms.

Isn't the reference lost and memory released when the function scope is left, regardless of setting objects to Nothing?

i.e. is it really necessary to do:

Set db = Nothing
Set record_set = Nothing

Is there an equivalent to Thread.Sleep() in VBA

Is there an equivalent to Thread.Sleep() in Access VBA?

Debug a bad DLL calling convention error

How do I debug a bad DLL calling convention error in MSAccess VBA code?

I made some changes to a function in a module and then got the error. How do a debug it to find the cause?

The error occurs on the Exit function statement of the function.

Escaping ' in Access SQL

I'm trying to do a domain lookup in vba with something like this:

DLookup("island", "villages", "village = '" & txtVillage & "'")

This works fine until txtVillage is something like Dillon's Bay, when the apostrophe is taken to be a single quote, and I get a run-time error.

I've written a trivial function that escapes single quotes - it replaces "'" with "''". This seems to be something that comes up fairly often, but I can't find any reference to a built-in function that does the same. Have I missed something?

Using a centralized VBA module in multiple Access databases

I've been put in charge of 50+ Access databases (.mdb's and .accdb's of varied versions) spread around an intranet, and I need to add a few VBA functions (the exact same code) to almost all of them. I'm hoping it can be accomplished by creating a reference from each database to a centralized VBA module, as I'd really like the ability to make changes down the road that are automatically replicated in every database. It would also be a bonus if any additional references in the central db could be kept intact so I wouldn't have to manage them individually as well.

I'm pretty new to VBA programming (although I've been a PHP junky for years), and have been having a hard time coming up with the right words to search for this particular issue. I think (from what I've read so far) that I might need Visual Studio to compile a DLL to reference, but I'd like to keep everything simple and contained within Access if possible, especially since we don't have a VS license.

Thanks, any pointers would be much appreciated!

Access sometimes jumps to existing record on save new record - Access2k FE/SQL2005 BE

this is my first question on SO, am really posting this out of desperation after searching around a lot for an answer and trying a few different things with no success.

I have an Access database where I have recently migrated the tables to SQL 2005, Access continues to function to the users as a front-end providing forms, reports, and queries.

However, since moving to the Access FE/SQL BE setup, the users have been reporting that sometimes, when they are entering a new record, they click into a subform (saving the record) or click save on the menu itself, it jumps to an existing record. The new record has been saved, but for some reason access switches to a different record as it refreshes. The user then has to close out, find the saved record, and continue editing it.

Scenario: A user is entering a quote and fills out all the quote details, customer, date, etc, then clicks in the line-items subform to add a product (or clicks save in the menu), and suddenly the quote form (and line-item subform) is showing the details of some random quote. The random quote could be recent, or from years ago, and has nothing in common with the quote they were entering.

This weird behavior only happens on inserting a new record, never on editing an existing record. Users tell me that it happens 'more often' when they go to add a new (quote, customer, whatever) after opening the database.

I have noticed it is only happening on forms that have subforms, so my first thought is that it had to do with Access sending through the subform data before the form data is saved, causing a PK violation. But this doesn't appear to be happening: there are no errors on the SQL server, and the record is successfully saved. Forcing the users to save the main form record before adding subform records (i.e. on a quote, forcing them to save the quote before they can add line items) didn't work, it just causes the jump (sometimes) on the save.

It isn't vba running on the save or on current, I have set breakpoints on all the event handlers as it jumps and no vba is being executed. Some of the 'jumping' forms have no vba on the form. But all have subforms. I suspect it has to do with record locking.

The server running the tables is SQL Server 2005, the users are using a mix of Access 2000 and 2003, mostly XP SP3 with a couple of old Win2k boxes. They are using Merge replication and a couple of users are running replicated SSEE2005 editions and subscribing to the main server. Most users are not replicated, just connecting directly to the server via ODBC or SQL native client connections. But I have verified that this is happening to all users, usually once or twice a day, and it has happened to me before. So it isn't a user issue.

The worst part about this behavior is that it only happens some of the time and I haven't managed to find a scenario that will always cause it to happen.

If anyone has experienced anything like this before, please let me know how you sorted it out, or even suggestions would be welcome. Thanks.

Update: (1/10/09) Problem solved, thanks to David Fenton. Setting the form to Data Entry mode (Form.DataEntry = true) before opening it to add records does indeed prevent the jumping. Client reports no issues at all since I changed this a week ago. Thanks for your help David, Phillipe and Tony!

How to monitor the values in a Dictionary in the Excel VBA watch window?

I am using dictionaries in Excel VBA via dict As New Dictionary (and adding a reference to the scripting runtime). When I try to monitor those during debugging, I can only see the keys which lie in the dictionary, but not the respective value of each key.

Is there any way to see the value as well? It would make debugging much more easy for me.

EDIT: Based on your answers, there is no easy solution, but I can do the following.

Use a global variable Dim d_obj As Object and monitor it constantly and whenever I need to look up a value of a dictionary, I type into the immediate window Set d_obj(key) = ... and I will be able to see the value in the monitor-window.

What I may do in addition is write a function which takes in a dictionary and returns the values as a list and use this function similarly at the direct window. Thx to all!

Progress bar in in MS Access

I have a query running in Microsoft Access 2010 and it takes over 30 min to run normally. I would like to present the end user with some status of the query. A progress bar would be nice but not required. Access seems to be poorly threaded and locks up tight during the execution of the query negating any updates I try. While I'd rather whip out VS and write my own app to do this I'm forced to use Access.

Any ideas?

EDIT

I used to run this from a batch script which populated the database but I'd like to have it all self contained in Access. To be specific the "query" is really a VBA script that pings a series of hosts. So I'm not to concerned about optimizing the time per se but simply to let the end user know it hasn't locked up.