Maintaining Records - ADO - SQL Server Forums

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.

All Forums

Other Forums

MS Access

Maintaining Records - ADO

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2003-07-06 : 01:11:22

I'm trying to figure out the best method of updating a SQL database from an Access 2000 project using ADO. I edit fields entered on a Form in Visual Basic. Upon acceptance I Add, or Change the Record.
This is my code so far:

Set Comm1 = Application.CurrentProject.Connection
Set Rst1 = New ADODB.Recordset
Rst1.Open "tblTest",Comm1,adOpenKeyset,adLockOptimistic,adCmdTable
strKey = "[Fld1] = 'Test'"
Rst1.Find strKey, , adSearchForward
If Rst1.EOF Then
Rst1.AddNew
Rst1("Fld1") = "Test"
Rst1("Fld2") = ...... etc.
Rst1.Update
Else
MsgBox "Record already exists", vbInformation + vbOKOnly, "ERROR"
Rst1.Close
Comm1.Close
Set Rst1 = Nothing
Set Comm1 = Nothing
Exit Sub
End If
Rst1.Close
Comm1.Close
Set Rst1 = Nothing
Set Comm1 = Nothing

Should I rather use a Stored procedure for this action? ..
Should I execute a SQL Command? ...
Thanks.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-06 : 02:45:10

You will be better off creating a stored proc on SQL Server and invoking that from code using an ADO Command Object. For many reasons, primarily security and speed. AdoDB.Recordset.AddNew might be a convenient method but it is quite often prone to locking and concurrency problems. You could actually do all of the code in this procedure inside a stored proc. So go for it!

Owais

ingineu
Yak Posting Veteran

89 Posts

Posted - 2003-07-06 : 03:27:15

Can you point me to any available code I can look at, as I'm new to Stored Procedures and have only done some pretty basic ones so far.
Thanks.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-06 : 09:08:41

Look up in the Books-online that come with any SQL Server installation. Its got some good examples to get you started, and of course, there is no substitute for trial-and-error...absolutely the best way to learn. Invest in a good beginners book on programming with SQL Server with VB/VBA. There are some good resources on the web, but most of them are biased towards ASP programming, though any content on SQL Server still applies. Check out:

http://www.4guysfromrolla.com
http://www.15seconds.com

Try to build the proc yourself first, its not too complicated even for a beginner. If you get stuck, yell here for help

Owais

Doug G
Constraint Violating Yak Guru

331 Posts

Posted - 2003-07-08 : 02:34:49

I take it you don't want to use databound controls on your form and give your form a recordset to work with?

======
Doug G
======

ingineu
Yak Posting Veteran

89 Posts

Posted - 2003-07-08 : 03:22:13

The Data entry procedure is too complex. They can select a number of different cycles and I pickup recordsets accordingly, displaying each record in a specified sequence. I think I'm going to work with my code as is, since I do like having control in 1 spot. Should be easier for someone else to follow my code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources