Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JoeJaz
Starting Member
5 Posts |
 Posted - 2004-07-08 : 11:48:14
|
| Hi, I hope that this is the right place to ask. I have a situation where I am using VBA to execute a SQL string and INSERT an entry into an Access database. Sometimes, when I insert an entry into the database but an entry already exists with the same key, the INSERT fails and returns no response (I have "DoCmd.SetWarnings = False" set since I don't want the user to be notified of the failure). However, I would like a failure to INSERT to be returned to the program so I can modify execution appropriately. Is there a way to pass a success or failure to INSERT an entry into the database from SQL to VBA? Thank you for even taking the time to read this question; I hope it is clear enough. Have a nice day,Joe |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-07-08 : 11:51:47
|
| Why not check and see if a record exists before trying to insert it? Then you would no when it would fail without it actually failingCorey |
 |
|
|
JoeJaz
Starting Member
5 Posts |
Posted - 2004-07-08 : 12:14:54
|
| I would like to do that, but this insert occurs inside of a loop and each iteration changes the value of the key that I would be testing for. This would require me to open up a new recordset for each iteration of the loop. For example, I am making a function to duplicate a record yet increment a date field in the record for each duplicate. If the date already exists in the table, then it should report that to the program. Since for every duplicate that I make the date that I am testing for changes, I would need to open a new recordset looking for the record with each new date. Since I am new to Access, I am not sure of the computational cost of this action. I value what you have to say and am greatful for your response. I will look for other ways to test if a record exists before I execute the insert as you suggest. There is so much about VBA and SQL that I don't know; they are very interesting and extensive languages. Thanks for your time.Joe |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-08 : 19:20:31
|
| How are you running the INSERT? Are you creating a query object then executing it? In this case, the user will be displayed an error if there's a problem. This would also happen when executing a query via the DoCmd object. To avoid this, try executing the statement via ADO then using an error routine to catch the errors:dim cn as connectionset cn = new connectioncn.open "yourConnString"on error goto ADO_Errorcn.Execute "your sql statement"ADO_Error: ' Error catching code here. |
|
|
|
JoeJaz
Starting Member
5 Posts |
Posted - 2004-07-19 : 10:53:15
|
| Hi, Thank you for your response and sorry for my delay in responding. I have been using the "DoCmd.RunSQL stringname" to execute my SQL statement. Perhaps I should use the .Execute method as you suggest. Also, thanks for the idea about the error handling. It looks like it could work for what I need. Thanks again for responding. Have a nice day,Joe |
|
|
|
|
|
|
|
|
SQL Errors in Access