Adding a Record using VBA 1

[jmgaddis]

Mine works fine... but possibly should be slightly different.

I just simply need to ADD a record to this table... so currently I have it SELECT * from the Table and the WHERE clause will never be found so no records are contained in the recordset. Then I add the Record.

Is there a better way to simply add the record without it having to attempt to add records into a recordset.

Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb

strSQL = "SELECT * FROM [J-H_DockDatabaseHistory] WHERE [DOCKNUMBER] = '1000000'"

Set rst = dbs.OpenRecordset(strSQL)

rst.AddNew
blah,blah,
rst.Update

Thanks,
John

 

[SkipVought]

Hi,

You could use a TABLE rather than a COMMAND in you open statement. Check out the FIFTH argument, I believe, something like adTable rather than adCmdText.

Skip,
[sub]
[red][/red]
[/sub]

 

[jmgaddis]

I think DAO only has 4 args
ADO has 5.

DAO: Set Variable = Database.OpenRecordset(Source[, Type [, Options [, Lockedits ]]])

ADO: recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Found article on Google:

http://www.functionx.com/vbaccess/Lesson22c.htm

I believe either way.. if open by Table, I believe it will pull all records into the Recordset... so maybe using a "fake" WHERE clause is the best way to add a record.

I am assuming it is faster if it doesn't find any records to include into a recordset... versus including all the records to the recordset and then I add my record.

200,000 records in table, sort of an audit trail table.

Thanks for the reply.

John

 

[TheAceMan1]

[blue]Is there a better way to simply add the record without it having to attempt to add records into a recordset.[/blue]

It seems this thread ia a carryover from somewhere. In any case how about an [blue]Append Query/SQL?[/blue] . . .

See Ya! . . . . . .

 

[jmgaddis]

Thanks Ace... I believe the Append Query/SQL is the answer... just seemed a waste to create a recordset just to add 1 record. (although the recordset way appears fast enough, was just curious if there was a more optimal way).

I will change it over.

Thanks again.
John

 

[TheAceMan1]

jmgaddis . . .

[blue]You'll never get more faster or optimal than query![/blue] If your thinking speed always think query first!

However since your only adding one record realize the speed difference will never be preceptable to you. So in this instance nothing wrong with your SQL/recordset method.

Note: your SQL would run alot faster if you used the [blue]Top[/blue] predicate instead of the where clause:

[blue]strSQL = "SELECT TOP 1 * FROM [J-H_DockDatabaseHistory][/blue]

See Ya! . . . . . .

 

[jmgaddis]

Thanks again Ace,

I like the "top" method... will change to that.. easier then rewriting the already existing code to SQL (or creating the appendQry). The Top 1 logically seems that it would be quicker than analyzing my table for my "fake" WHERE clause.

Learned something new today.

Have a great new year!

John

 

[danvlas]

Well, Top 1 will return one existing record... and I'm really not sure about any speed improvement in Access.

My favourite flavour of a 'fake record' is Where 0=1 which never fails, at least as long as arithmaetics remain the same...

If you merely want to add values or simple calculations, use Append queries. If not, a recordset is the way to go, be it DAO or ADO...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

Howdy danvlas! . . .

You can easily check out the difference on a DB with a table of 10K records or greater.

[blue]Happy New Year . . . and you take care . . . Ya Hear![/blue]

See Ya! . . . . . .

 

[danvlas]

Hey AceMan1, thanks a lot and A Happy New Year to you too.

Will 27K records suffice? As there is no speed difference between 'Top 1' and 'Where 0 = 1'.

However, 'Where [ExistingField] = NotExistingValue' is indeed much slower then both variants above. But that's because a field is evaluated.
In 0=1 there is NOTHING to evaluate.


Cheers



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[JerryKlmns]

Just for fun..

On a 632544 records table, elapsed time to open the recordset (adUseClient + adOpenStatic + adLockReadOnly)

Top 1 --> 3,47222012351267E-05
0=1 --> 3,47222958225757E-05

on a 100Mbps network.

 

[jmgaddis]

Hey Jerry,

What about using a "fake" where clause with an actual Fieldname but the other equation can never be found.

Where [fieldname] = 'can never be found'

Appears that Top 1 beat it... not that I have time to go to lunch between the two while waiting.

Thanks,
John

 

[barny2006]

if you're adding a record into a database, why not simply do this:

strsql = "INSERT INTO my_tbl(field1, field2) values("a", "b");

and then executing strsql in vba?

 

[danvlas]

Appears that Top 1 beat it...



Yep...sure...11-th decimal point makes the huge difference...

Actually, I have to apologize to AceMan1, as the dispute was between Top 1 and WHERE [DOCKNUMBER] = '1000000' and in this case, he was right (huge bow here, no emoticons for it)

I was thinking of MY favourite flavour, so my reply should have read differently:
"Yes, Top 1 is much better than WHERE [DOCKNUMBER] = '1000000', but my opinion is that one should also try Where 0=1, which is just as fast as Top 1, but has a greater flexibility in dynamic SQL and ad-hoc filters"

Which opinion still stands...


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

much appreciated danvlas! . . .

I intended to back myself up in the same way.

However,I stick to my main point: Since this is one record or the equavilent of a one time affair . . . [blue]any differences in processing time will not be preceived![/blue]

[purple]Of course if a loop were involved I would recommend otherwise! . . .[/purple]

See Ya! . . . . . .