Search results

I'm talking about a SURROGATE key (ID), not a known Primary Key like CustomerID which would be indexed. I guess my real question is: can an Autonumber column ever have duplicates ?

Can anyone explain why one would want a unique index on an ID key ? It can't be effectively used in joins and is never used in a where clause. So why have one ?

This likely applies to SELECT queries. INSERT queries are a whole different story. Server-side queries are about 5x to 10x faster than local queries.

Interesting that Tempvars is recognized in a query. I wonder if your Like clause needs to be enhanced: Like "*" & [Tempvars]![varname] & "*"

Thanks much for that Pat....that's what I was looking for. The database engine can use the stats to determine the count. However, that's in the case of when there is no where clause. Also, I wonder how smart it is when there are joined tables involved in a query ?

You forgot to open it with the acTable option. THAT'S THE KEY.

Thanks Paul, I cannot believe this has not been benchmarked. Note: For Tables, MoveLast is not required...so it is lightening fast....just grab the RecordCount property. For queries, it's a bit slower.

Yeah, but then you have to open the query result to fetch the number into a return variable of a function. I know that DCount is likely inefficient, so that's why I asked. If I open a recordset Readonly and ForwardOnly I would think that an EOF and then a return of the RECORDCOUNT would be...

I'm sure this has surfaced before. For small tables (<10,000 rows), which technique is faster, more efficient ?

Yeah, using a single source with a link is the best idea. Sorry about that.

Data is inserted into this table daily and identified with a date. Of course each new set of data has a higher date than previous sets. One designer used a combination key with the date and ID (autonumber) columns. He also created a unique index on the autonumber column. The primary key combo...

Lots of good material here: http://www.techrepublic.com/search?q=access+SQL+Server&e=1

Visible=False to Visible=True does not trigger the Activate event handler....interesting. Load is triggered when going from design to form mode.

If one had to grab data from an Excel worksheet, (and it's very fast to put the entire UsedRange into an array), I would think AddNew would do the trick. I'd have to specify which sheet columns are mapped to which table columns (An array of specifiers would do fine). However, if I save the array...

Has anyone benchmarked the difference in speed for adding 1000 records to a table ? I witnessed a programmer looping over an insert statement and that got me wondering. Seems like that would be a lot of overhead, no ?

I know, I know...that's what I said to my boss. I was going to set the shortcut property to false to prevent this, but he said "no". Strange.....

We have a call to a long running procedure in both Open and Load event handlers. One reason for this is to insure it executes if the user goes into design mode and back to form view. If a form is hidden, and then made visible, does that fire the Load event handler ?

The tables are small (<500) and they are indexed properly anyway. No need for a cursor...the logic is simple: INNER JOIN productnum to productnum in the consolidation table, group by the consolidated product num; then outer join on where the consolidated product num is null and group by regular...

We have the need to add a join to a simple table that has a small set of product numbers and their corresponding consolidated product number. So the order table links to this table, and if a consolidated product number is Not Null, then it will be the GROUP BY, otherwise the regular product...

Well, let me say right off that I don't agree with you on both accounts: 1) Ignore dot-net It's impossible....it's pervasive, it's everywhere. Interestingly, I just contacted one of MSFT's Dynamics CRM partners and got a rough ballpark estimate of the rewrite that was 3x my estimate for the...