[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - check whether data added to a SQL database

Posted on 2011-09-11
2
Medium Priority
?
220 Views
Last Modified: 2012-05-12
Hi

In VBA I have written code to add data to an Access database.
I have code that checks the highest ID before and after the Insert Statement
and if this has changed returns a successful entry.
With multiple users however, this might cause problems.
Should I maybe also use a select statement to see if that exact info is there?
0
Comment
Question by:Murray Brown
2 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36518433
If you cannot use an alternative like method I showed with SCOPE_IDENTITY(), then you may need to do something like that to be absolutely certain. For example, you may check for MAX(ID) and 100, then execute an insert that fails, but check for MAX(ID) again which returns 101 as another process successfully inserted a new row. As an alternative, you could run a scalar query that tests EXISTS() or COUNT() ... WHERE based on your criteria; however, if the insert failed because there was already a row with your new data then this method may also be flawed.

You may have to use a stored procedure to do inserts where you can return true or false to indicate success or failure.
0
 

Author Closing Comment

by:Murray Brown
ID: 36518880
Thanks very much
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question