William Vaughn
9/8/2007 10:14:00 PM
That's okay--we get all skill levels here and people doing everything from
simple to complex stuff. When it comes time to choose it's probably best to
use the free stuff first--especially when you're on a budget. Access is an
entirely separate and distinct way of handling data. It's just fine for home
and (very) small business use. It has a number of limitations that don't
make it particularly suitable where data has to be secure or shared beyond a
few casual users.
Keep asking questions--just tell them the whole story when you do.
As to the "test to see if it's already there" issue, put a unique primary
key on the Identity column. Next, when it's time to add a row, it does not
make any difference if it's there or not--it's always added. If you want to
make sure there aren't duplicate rows (for example, by Name or StoreName,
put a unique index on those columns which make up the unique key of the
table. Then JET (the database engine) prevents duplicates from being added.
In this case, just do your INSERT. If the row is already there (there is
already someone with the same name in the database), the INSERT will fail.
If it does, turn around and execute the UPDATE.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"Jeff Gaines" <whitedragon@newsgroups.nospam> wrote in message
news:xn0fayq3qzkwrr001@msnews.microsoft.com...
> On 08/09/2007 in message <OVFJEAk8HHA.4612@TK2MSFTNGP03.phx.gbl> William
> Vaughn wrote:
>
>>Thanks Cor.
>>
>>Actually, Jeff, since you're re-writing, I might consider another DBMS
>>engine--one that's not being phased out by Microsoft. SQL Server Compact
>>Edition or Express Edition come to mind. This gives you the flexibility to
>>write more sophisticated SQL statements that include logic to execute the
>>appropriate command in a single round-trip.
>>Consider that if JET says the row is not present, you might still get an
>>concurrency collision when another application is sharing the database and
>>inserts a row after your SELECT but before your INSERT. I might try to run
>>this in a transaction or simply try the INSERT first. In this case you'll
>>have to consider that your instance of the application is overlaying
>>someone else's data. Is your data right or is theirs?
>>
>>If this is a single-user application, then SQLCe is likely ideal for your
>>needs. It also makes the logic simpler. In this case you can create an ADO
>>classic-like Recordset object that can be updated in place.
>>
>>hth
>
> Hi William.
>
> I have just paid a small fortune for Access 2007 so I don't want to give
> up on it yet!
> I write small apps as a hobby, all single user, the dog can't use the
> computer - he doesn't know my password!
>
> As I said in my reply to Cor, I am looking for a simple way of knowing if
> a record exists or not so I can decide whether to update it or add a new
> record.
>
> --
> Jeff Gaines