Search results

In response: Firstly >>Option Explicit was not turned on behind the Form<< >>That does not stand in good stead of an experienced programmer.<< Actually the opposite it true - it is always best to not assume that this is turned on at the database level in the envioment that I work. Secondly...

What is it the dispose does again? Public Sub Dispose() Set remember1backer = Nothing Set remember2backer = Nothing End Sub As you can plainly see from the few lines of code, all the Dispose() method does is sets the values to Nothing at the level that they were set in the class...

Chris, I have attached a 2003 version for you to test.

Firstly, using MS Access as a front end to SQL Server databases it common for many professional developers. I made reference to most of my VBA work was not against local tables in an earlier post. Secondly, I have to admit guilt to your correct assumption that I did not run any of Chris' code...

As I said in an earlier post, you will not change my mind and I will not change yours but I guess this may be entertaining for some readers. By SQL tables, most database developers would know that I mean tables in a Microsoft SQL Server database. I appologise for making the assumption that you...

I optimized it for you :) Public Sub test() Dim X As Variant Dim y As Variant y = "Fred" X = y ' To be sure, to be sure, to be sure. X = y X = y X = y ' Clean up. Set X = Nothing Set y = Nothing End Sub Obviously this is a silly example...

Hi Galaxiom. Thanks for jumping in and while the .NET is not the same as VBA you raise a good analogy. Are you familiar with the iDisposable interface in .NET? this should be implemented by every class that uses system resources such as a recordset. If you are familiar with it then I am...

Times up? Grow up! This is a point that polarises the VBA development community and most programmers assume that they know better than their peers. There is no point arguing with someone who is blinkered in their opinion - I was trying not to be and to try to understand your point of view...

For a more realistic test this would have five people sharing an access database open a linked table to a sql server database with 10 million rows and move a few thousand rows in and then close the form without closing the recordset. I have seen this leave active sessions and locks open in SQL...

I agree that there is an element of the "5 monkeys in a cage" experiment" about this but I also know that it does no harm to close and set them to null which is why I said that it is "best practice". I am sure that the number of people who do it is much higher than the number of people who...

No, you should not close the database with myDatabase.Close unless it is a reference to an external database. If it is the current database the close command will be ignored and yes, you should set to Nothing as best practice. Recordsets on the otherhand should be closed and then set to Nothing...

It is probably just another hole in my VBA knowledge. I know that [qry x main table next mth] is the query and I assume that Tnumber is a field but what is stafpay? I didn't realize how many holes there were until I discovered this site! What do you get if you run the following as a query...

The Key is very powerful when you use a collection as a dictionary for fast lookups. Obviously, the key acts as a PK and must be unique. Public Sub ctest() Dim c As New Collection c.Add "Item 1", "a" c.Add "Item 2", "b" c.Add "Item 3", "c" Debug.Print c("c") Debug.Print c("b") Debug.Print...

I would still like to know how you can have : [qry x main table next mth].[stafpay].[Tnumber] as a field in a subquery. Am I missing something?

This is useful to know - Thanks!

Firstly, I don't have the book, sorry. You can enumerate collections with For Each but you have to treat the items as an object or variant. You can move up and dows with indexers assuming the collection is in the correct order. The problem is that you can't insert into the middle of a...

You could use: WHERE tableB.ColumnA IN (100,101,102,103) or WHERE tableB.ColumnA IN (SELECT ColumnA FROM tableC) or did you specifically need to use VBA?

Sorry, I don't have the time or inclination to convert this code. If you can't find it on Google, try looking here: http://tinyurl.com/8khab2h As I said earlier, download and use at your own risk because I can think of no legitimate use for this (which is not to say that there is not one!)...

It would be much easier to read if you alias your tables. I would guess that the problem is with the following in your second subquery: [qry x main table next mth].[stafpay].[Tnumber]

I think that you are at the limits of what can be done using relationships and you need to do this with SQL and/or programming. Having said that, I almost never use relationships since most of my tables are linked to SQL Server so others may be able to help take relationships further than I can.