Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Unanswered: .OpenRecordSet for currentdb

    Hi there. I need to just insert a new record into my table. I don't want the system to retrieve all records that currently exist in this table because it's pretty big. But I think that's what my code is doing right now - actually, it was a freebee code snippet that I got from another forum to log all changes made to a form / record in ms access. Can I pass a select statement that doesn't return anything, just to get the recordset set up? How would I do this?
    The code looks like this:

    Public Function basAddHist(Hist As String, frm As Form, MyKeyName As String, MyCtrl As Control, uid as string)

    Dim dbs As DAO.Database
    Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

    With tblHistTable
    .AddNew
    !mykey = frm.Controls(MyKeyName)
    !MyKeyName = MyKeyName
    !frmName = frm.Name

    !FldName = MyCtrl.ControlSource
    !dtChg = Now()

    !UserId = uid
    !OldVal = MyCtrl.OldValue
    !NewVal = MyCtrl
    .Update
    End With
    End Function

  2. #2
    Join Date
    Feb 2003
    Posts
    107

    REsolved??

    This is what I'm doing and it seems to work... although if someone can verify that I'm not doing anything "bad", that'd be great,

    Set tblHistTable = dbs.OpenRecordset("Select * from tblhist where dtchg=1/15/1975;", dbOpenDynaset)

    There will never be any records with this timestamp...

    Thanks.
    It seems to work although the whole point of this change was to speed things up and I can't tell if that's happended because I'm connected via remote desktop and everything seems to be running slow today!

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    couldn't you use a CurrentDb.Execute and an INSERT sql statement?
    as in:
    Code:
    CurrentDb.Execute "INSERT INTO tblTable (field1, field2) VALUES (value1,value2)"
    That way you're not opening a recordset just to insert. it seems like what you have done would indeed be quicker than the original way of doing things, but the insert may even be a better option.

  4. #4
    Join Date
    Feb 2003
    Posts
    107

    tried that but...

    this function passes an entire form to be "audited" .
    i believe i need to specify data types for each control in order for the insert to work.

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    why specify data types? i mean, i'm definitely not a SQL expert, but as long as you enclose string values with '' and dates with ##, you should be alright.
    something like:
    Code:
    Dim strSQL as String
    
    strSQL =  "INSERT INTO " & Hist & " (MyKeyName,dtChg) VALUES ('" & MyKeyName & "',#" & Now() & "#)"
    
    CurrentDb.Execute strSQL
    just finish it out with the rest of the fields you need to update.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would make the following change:

    Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

    To

    Set tblHistTable = dbs.OpenRecordset("SELECT * FROM " & Hist & " WHERE 0", dbOpenDynaset)

    That should return no records and allow you to add a new record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •