Search results

Rain, I'm happy to use your own definitions to make this easier. Access is a "development tool" (your words) that can be used for creating applications and databases. Access is therefore no more a database than Microsoft Word is a document or Microsoft Excel is a spreadsheet. Roughly...

Access places some restrictions on what you can do in an ON clause and requires you to add brackets around JOINs. You may find it easier to use the alternative and more "standard" SQL comma/WHERE syntax, which doesn't have the same restrictions. I have never understood quite why Access requires...

Use COUNT(*) to return the number of rows in a query. COUNT(*) should never return zero. It always returns a whole number greater than or equal to zero.

A view can have no predefined order. Just think of a view as working exactly like a table. Relational tables and views are logical sets, without any ordering to them. This is the principle followed by any implementation of SQL. Some possible confusion arises because Microsoft SQL Server has a...

This is incorrect, at least in Access 2007 and I'm pretty sure in later and earlier versions too. In Jet/ACE, if a field is set to Required=No then if it's a numeric column it can contain nulls; if it's a string it can be null or empty. Null is not the same thing as an empty string or zero...

Glad you solved your problem but note that you don't need to specify @TRGNUM after your RETURN statement. Return @TRGNUM The fact that you have declared the parameter as OUTPUT automatically means that it will be returned by reference. The RETURN value of a proc is something different to...

Neither of these pictures appear to make much sense. They don't use a standard ER modelling notation and the intention isn't exactly clear from the naming conventions either. If we are supposed to understand that the arrowhead points to the attribute(s) being referenced in the parent table then...

At the start of each day, run the following ALTER TABLE command: ALTER TABLE YourTable ALTER COLUMN ID AUTOINCREMENT (1,1); Note that autonumber columns can and do generate duplicate values and you obviously will get duplicates if you do the above because the counter will start from 1 each...

The irony is that one of the arguments most often advanced by people who champion Access over other products is this: that it allows users who are not necessarily data management professionals to bypass and ignore the qualified, experienced professionals in their IT Department. Some in the...

I suggest you price by the hour or day unless you already have an established relationship with the customer for that kind of work. Per day pricing normally gives you the most flexibility and the best chance of estimating and delivering to expectations.

Unfortunately that kind of argument doesn't get you far when it comes to an organization's legal (and moral) obligations to protect its systems and data. Prosecutor: "Did you follow industry best practices and take all reasonable steps to protect customers, employees and shareholders against...

I don't think that's very practical advice. Autonumber columns are generally designed to be used as surrogate keys only. I have no objection to their use as surrogate keys but autonumbers don't mean you can abandon natural keys - i.e. the external identifiers actually used to identify real...

If you already have a database that contains both data and application then split it as soon as you are able. When you are starting a new project keep the database and application separate right from the start and then you will never have a need to split it. No. It's easier to modify, test and...

Hi Dave, I've heard this security-based suggestion before but I don't get it. As far as I know, in every DBMS I can think of security can be granted or denied at column level and not just table level. In Jet/ACE there is no role or object-level security at all. Does there really exist a DBMS...

When you say "SQL" do you mean "SQL Server"? Can the query you mention be passed-through (i.e. does it use tables that reside in SQL Server)? Assuming the answer to both is yes, the form of the update might be: UPDATE tbl SET col = ... something you didn't specify WHERE EXISTS (... your...

Read it again. I said I have spent time fixing data quality issues where natural keys were not implemented, i.e. where surrogates were used without bothering to identify and enforce any natural key.

I was not and have not changed my position on anything. I was pointing the necessity and value of natural keys. But as I already said, we don't speak the same language. Enough.

When they say "Access" many people really mean "Jet/ACE", i.e. the data file sharing mechanism of MDB/ACCDB files. Jet originates from the days when SQL DBMSs were big, expensive and complex. Jet and like products provided a cheap desktop alternative to a SQL DBMS. That segment has largely...

Great example! Your code doesn't do a search. The search is what you are saying the user has already done in a combo box. That's fine if your data set is small enough to eyeball in a combo box but no good at all if you need to search for one record among thousands or millions. To do that you...

Rain, clearly we aren't speaking the same language. As an end user I don't see the autonumber column and therefore I can't and don't use it to identify things in the table - I need a natural key to do that. I can't and don't base my searches on it - I will use a natural key for that.