Recordset vs Sql Statement

[NigelShaw]

Hi All,

what would be better, a new record using Recordset code or code with SQL? currently, i am entering data like-

Dim db As DAO.Database
Dim rs As DAO.Recordset

set db = Currentdb
set rs = db.Openrecordset("MyTable/Query", dbopendynaset)

rs.Addnew

rs!Field1 = MyEnteredData1 ' add text string
rs!Field2 = MyEnteredData2 ' add boolean value to True

rs.update

rs.Close
db.Close

set rs = Nothing
set db = Nothing

im pretty sure there is an easier way of entering data into a table as a new record. im not entirely sure how as im not great with SQL but could the above be done without using the above?

i thought that going down the SQL route, you could also update a record rather than adding new records.

im looking up sql at the minute but how would i do the above in sql rather than my example?



many thanks,

Nigel

 

[ajetrumpet]

You can probably use the INSERT INTO statement in VB. You can also update the set with SQL. Use the UPDATE statement for that. Enclose the statements in quotes when you're writing it in Basic. Something like:

docmd.runsql "UPDATE tablename SET " & _
   "[fields] = 'your values'"

 

[WayneRyan]

Nigel,

It sounds like you're considering putting together some "unbound" forms. I'd vote
against that concept, unless you had some VERY GOOD reasons to ignore what Access
does for you in handling your forms.

But, as to your specific question.

Using a recordset to add data:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SomeTable")
rst.Addnew
rst.NumberField = Me.YourNumberField
rst.StringField = Me.YourStringField
rst.DateField = Me.YourDateField
rst.Update

That's pretty concise and you don't worry about "punctuation".

Using SQL to add data:

DoCmd.Execute ("Insert Into SomeTable (YourNumberField, YourStringField, YourDateField) " & _
               "Values (" & Me.YourNumberField & ", '" & _     <-- Note: This syntax can get a little bit trickier when
                            Me.YourStringField & "', #" & _    <--       you try to insert a name like [B][SIZE="3"]O'Hara[/SIZE][/B] that contains
                            Me.YourDateField & "#);"           <--       a single-quote.

That's just the VBA way of making a string that looks like:

Insert Into 
SomeTable (YourNumberField, YourStringField, YourDateField)
Values    (111,            'SomeString',     #1/1/2008#);  <-- The single-quotes and the "#" are what makes the VBA look "funny"

To update an existing record with a recordset:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * From SomeTable Where ThePrimaryKey = " & Me.ThePrimaryKey)
rst.Edit
rst.NumberField = Me.YourNumberField
rst.StringField = Me.YourStringField
rst.DateField = Me.YourDateField
rst.Update

To do the same thing with SQL:

DoCmd.Execute ("Update SomeTable " & _
               "Set YourNumberField  =   " & Me.YourNumberField & ", " & _
               "    YourStringField  =  '" & Me.YourStringField & ", " & _
                    YourDateField    =  #" & Me.YourDateField & "# "   & _
               "Where ThePrimaryKey = " & Me.ThePrimaryKey

That's just the VBA way of making a string that looks like:

Update SomeTable 
Set YourNumberField  =  111,
    YourStringField  =  'SomeString',
    YourDateField    =  #1/1/2008# 
Where ThePrimaryKey = 1

Either method really isn't that hard, but the real secret is to NOT have to do any of that code.
That never really happens anyway though ...

One last thought is that when you are opening/using all of those recordsets, the code may look a little
bit "cleaner", but the DB will tend to "bloat".

hth,
Wayne

 

[Banana]

One more point.

You don't really need to have a recordset or SQL statement to add a record. You can just create a action query with parameters then do this:

Dim rst As DAO.QueryDef

Set qdf= CurrentDb.QueryDefs("NameOfInsertQueryYouCreatedInQBE")

With qdf
   .Parameters("Parameter1") = Me.txtSomething
   .Parameters("Parameter2") = Me.txtOtherthing
   .Execute
End With

Set qdf= nothing

(This is an air code and may require corrections, but you should get the basic gist.)

Doing this way gives you the best of both worlds; direct reference to controls without formatting the SQL statement and the brevity of SQL.

HTH.

 

[NigelShaw]

Hi Guys,

very helpful thank you. it is good to see the difference in methods though they both do the same thing.

in regard to your point WayneRyan

"It sounds like you're considering putting together some "unbound" forms. I'd vote
against that concept, unless you had some VERY GOOD reasons to ignore what Access
does for you in handling your forms."

i have opted to have the unbound forms because,
-i do not have any pop up forms and i originally had issues with related data or data that was already in use
-when the actual data was shown directly from the table / query, there were instances that iti could be changed so taking the adivice of others from here in the past regarding recordset using, it seemed a better idea to get the data from with this method as it would not be directly changed.

the main reason though for this method is i need to update information in tables without actually having the data visible. i have a tracking log running that gets a record placed everytime something is added / removed / updated and i dont want this visible. also, there are a couple of instances where i want data to be silently updated instead of visibly updated.

regs,

Nigel