Search results

You're preaching to the choir - I'm in the same boat. The question is how often it happens and how devastating it really is. I say this because neither Windows nor any other operating system will ever be perfectly stable. We've learned to live with a number of errors that occur intermittently...

I don't recall using a DDL statement but it was a long time ago so perhaps I did. Oddly the following didn't work (though it seemed to be what Allen Browne suggested) Dim tDef As DAO.TableDef Set tDef = CurrentDb.TableDefs("Posted") Dim fld As DAO.Field Set fld = tDef.Fields("Account")...

Apparently there is a design issue which I don't understand. I have a table with an Account column (account number). It's of type TEXT and length 50. For some reason Jet has it as a fixed-length column. How do I undo that? That is to say, even if I insert a 10-digit account number, Jet is...

There's probably no way to do this without a temp table because the Jet engine doesn't allow you to run aggregate commands (such as Count or Group By) in an UPDATE query. Also, if you have over, say, ten thousand records, the temp table approach will give better performance when deleting dups...

Thanks for the feedback. Honestly I'm more optimistic about the disconnected recordset approach, versus the dictionary, for reasons stated above. But I'm glad you got the standard table-method working.

Generally, if you have two or more separate tables, you can, if the tables are related (i.e. a relationship pointer-line is drawn in Relationships View between the tables), use the wizards and graphical tools to produce a report showing desired data from each of the tables. Generally, behind...

I just thought of yet another solution. You can load the data into a disconnected recordset and then call rs.Sort = "FirstName, LastName" and then loop through the sorted recordset. Every time the FirstName+LastName combo changes, you've reached a new Customer, and thereby simulates a group...

I've done this using .Net dictionaries which are fast because, unlike VBA dictionaries, they are strongly typed. And, as I said, I'm not even sure that VBA dictionaries can hold a Customer Type. On the other hand I'm fairly certain they can hold a Customer Class, so maybe I'll create a sample...

Well yes - you have to decide where you want to pull the data from. Do you want to pull it from the form? Then yes, you would probably want to use AcNext to move the form to the next record. Do you want to pull it from the recordset? You use MoveNext as though yuo want to pull it from the...

I'm no good with Access forms, but as I recall, the gurus on this forum (like Bob) say that the BeforeUpdate event is used to cancel (or permit) a user to edit a record shown on an Access form. If you agree with the user's suggested edits, then I suppose you can fire a messagebox in that event...

To be more clear, a Dictionary can be used to simulate a Group-By. Here's some pseduo-code (it's not real syntax) to give you the idea. Suppose you wanted to sum the orders for each customer (so you decide to group by FirstName, LastName Dim dic as Dictionary Foreach C as Customer in Customers...

Why don't you just use VBA to insert the 120,000 records into a table and then run a group-by query on the table? (VBA can do all this pretty fast). Another option is to use a Dictionary (with a Type-object, i.e.,a Customer obect/type), but I'm not sure it would be much faster (depends on how...

BTW, if my suggestion doesn't work you might want to keep your own code but with this modification (replace carriage returns and empty spaces) strNumber = Replace(strNumber, vbCR, "") strNumber = Replace(StrNumber, vbLF, "") strNumber = Trim(StrNumber) or maybe something like this: if not...

The other day someone raised a question about XML so I took a stab at it - this was my first try so I can't say I learned much. But your XML file looks similar to the other guy's file, in the sense that it amounts to one simple table. Your table currently only has one row, but lots of columns...

Well I did run that experiment - turns out the query runs fast from Access VBA even though it is slow from the object pane. But my real issue on this thread was running it from .Net - which has been running fast since I started using DocMan's suggestion. This whole week I've been inserting...

Maybe what you had in mind is this (the following code is untested) and it assumes you are pulling the records in the desired order (you have an "Order by OpportunityID" which I can only assume succeeds. Dim rst As Recordset Dim lastamount As Variant Set rst = CurrentDb.OpenRecordset("select *...

When "search all fields" I tell the code to use the original SQL (saved in the listbox's properties window) strSaveOriginalSQL = Me.SearchList.RowSource and that SQL is: SELECT tblSales.CustID, tblSales.ClientName AS [Client Name], [InstallAddress] & ", " & [InstallTown] AS...

I think your assessment is correct. That is to say, when running an SQL command from VBA, you cannot use the name of the cbo directly in the SQL (and likewise you cannot run a saved query that does so). Thus if the saved query is: SELECT....WHERE frmCustomer!cboCustomer = .... it won't work...

See attached. I added a combobox to display the field names, plus the following code: Private strSaveOriginalSQL As String Private Sub Form_Open(Cancel As Integer) strSaveOriginalSQL = Me.SearchList.RowSource Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM...

BTW, there's another performance question in my mind. As stated, the search takes about one second per database when run from C#.Net using the .Net OleDB provider. However, if I run the same query from Access SQL view, it takes about 10 seconds for one database. This is not a problem for me...