Search results

Just to be clear on the terminology: that is not two keys, it's one key consisting of two attributes AKA a composite key. I suggest you avoid the term compound key because in some contexts that means something different. Two keys would mean two attributes (or more generally two sets of...

However, if you replace every composite foreign key in a database with a synthetic single attribute then you will be forced to do a join every time you need to retrieve the meaningful key values that really matter. If you use composite keys you may not need to do a join at all or you will need...

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...