checking to see if row exist
-
Hello all, i am a newbie so this i know someone out there can hopefully give me some guidance.
I have a large amount of access '97 DB's and i am converting them to SQL 05. The conversion goes through fine and well, but until my data entry people get their new software they still data entry through the access DB's. With that being said when my data entry personal enters new data i need to smash this button again, updating the SQL, but i don't want the same row twice, so my point is how do i check to see if it is SQL and if it is skip the insert, if not then insert.
if anyone can help, thanks in advance!!
-
Is the data structure identical on both ends? If so, then use your primary key (pk) to test - if the recordcount >0 on a select where pk = value, then you don't want to insert. OR, to make it even more elegant, if 0 = select count(pk) where pk = value, INSERT INTO...
-
Well i am taking some data (just certain columns) from access' also, i am also adding more to the sql, (my mapping between the two works fine) but until i get my software to the data entry people i must press this button to convert the data i want over into sql...
But every time i press this button i don't want it to insert the records already there, (like i said i am a newbie) and i am guessing the best way to or possibly the only way to do is a stored sprocedure... unfortunely i am not very skilled in this art either.
thanks again
-
my suggestion would be
- to halt any development on your access dbs,
- get the sql db to work with your apps
- then do the access db upgrade again, and
- just point your app to the new db.
- and of course do the testing.
The way you are proposing to do it will be a very difficult process, i think
-
A couple of things :
1. have access Link directly to your SQL Server tables. (File - Get External -> Link Table) This now looks like a local table to access
2. setup the primary key in SQL Server and then check for error on insert (I think you mean to issue an update instead of insert)
3. Upload all your Access data to SQL Server and stop storing the data locally
4. Stop developing in Access
5. Tell your friends to stop developing in Access
6. don't develop in Access anymore
Hope this helps.
Live to Throw
Throw to Live
Will Summers -
problem solved, i went about the hard way but i got it.... o yeah bye bye access very soon, just trying to please big boss man for now, thanks a million for all ur input
pease love and chicken grease!
-
Realize it has been solved, but very simply.. If you know what the row should look like if it is already there, then I am sure you can create a primary key. And if you do that, it can't get put in. You just ignore the error when failing to insert a row that throws that exception.
Second point. Or I guess Question... "pease love and chicken grease!" ????? I know I will regret asking this but what??? I'm not sure if I am too old, or two young to get that one. The best I can come of with is a fallback to the 60's and KFC.
-
jus a lil sayin round my way..
ya know diff strokes for diff folks, like that
-
a former au pair in my neighborhood used to say 'Peace, love and vegetables'. But she was Finnish, not US resident, over here for one year.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply