Is there a better why to quickly add a new record ?

Access 2010 vba
sql server linked  tables

Trying to add a new record...taking a very long time to with code

Set R = CurrentDb.OpenRecordset("dbo_t_nsc_trackcode_trans_time_entry", dbOpenDynaset, dbSeeChanges) ''' CLOCKED HERE FOR  15 SECS  03/06/2018
           '          Set R = CurrentDb.OpenRecordset("SELECT * FROM [dbo_t_nsc_trackcode_trans_time_entry]", dbOpenDynaset, dbSeeChanges)
                     R.AddNew
                    R![NSC_ID_Ref] = Me.NSC_Id
                    R![InProgress_or_Closed_at] = Now()
             R.Update
             R.Close
             Set R = Nothing

Open in new window



Is there a better why to quickly add a new record ?

Thanks fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Dale FyeCommented:
you could execute an append query:

strSQL = "INSERT INTO [dbo_t_NSC_trackcode_trans_time_entry] (NSC_ID_Ref, InProgress_or_Closed_at]) " +
       & "Values( " & me.NSC_ID & ", #" & Now() & "#)"
currentdb.execute strsql, dbSeeChanges

Open in new window

0
 
Fabrice LambertFabrice LambertCommented:
Hi,

just execute a query:
dim SQL as string
SQL = "INSERT INTO [dbo_t_nsc_trackcode_trans_time_entry](NSC_ID_Ref, InProgress_or_Closed_at)" & vbcrlf
SQL= SQL & "VALUES ("  & Me.NSC_Id & ", GETDATE());"

dim db as dao.database
set db = currentdb
db.execute sql, dbFailOnError

Open in new window

PS: If the InProgress_or_Closed_at column always represent the date and time when the record was added, a trigger or  a default value should handle this in your database design, this way, you won't have to worry about it in your queries.
0
 
FordraidersAuthor Commented:
Thanks all,
I forgot to add one more field that may be a problem ?  getting a value from main form

'R![opened_at] = Forms!nsc_dataentry.txtReOpened

I cant get the syntax to take
errors below

Dim ttt As date
ttt = Forms!nsc_dataentry.txtReOpened

' trying this 

             If txtCid_Form = Me.NSC_Id Then
          
                 
                 If Forms!nsc_dataentry.txtReOpened.Value <> "" Then
                    strSQL_sql = "INSERT INTO [dbo_t_nsc_trackcode_trans_time_entry](NSC_ID_Ref, [opened_at], InProgress_or_Closed_at)" & vbCrLf
                    strSQL_sql = strSQL_sql & "VALUES (" & Me.NSC_Id & "," & ttt & " , now());"

Open in new window



Thanks !
 variable value
and error messageerror message
0
 
Gustav BrockCIOCommented:
You don't need to open the full table. Try this:

Set R = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM [dbo_t_nsc_trackcode_trans_time_entry]", dbOpenDynaset, dbSeeChanges)

Open in new window

or:

Set R = CurrentDb.OpenRecordset("SELECT * FROM [dbo_t_nsc_trackcode_trans_time_entry] WHERE False", dbOpenDynaset, dbSeeChanges)

Open in new window

or, if that fails:

Set R = CurrentDb.OpenRecordset("SELECT * FROM [dbo_t_nsc_trackcode_trans_time_entry] WHERE (1=0)", dbOpenDynaset, dbSeeChanges)

Open in new window

0
 
FordraidersAuthor Commented:
Thanks all,
The insert statement was 15 secs...faster..

fordraiders
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.