Related questions

Use of CurrentDb.OpenRecordset in VBA

2018-10-11 19:12 Gagandeep Singh Randhawa imported from Stackoverflow

I am editing something in VBA in MS access. Can someone please tell me the difference between below two statements.

1. Set rs1 = CurrentDb.OpenRecordset("tblOPCEPLTB")
2. Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblOPCEPLTB")

What I am guessing is 2nd line fetches all rows from table tblOPCEPLTB and store in rs variable. So what does 1st do? After executing 1st what should it have?

Please help here

1 answer

  • answered 2018-10-11 19:24 Erik A

    The first statement opens a table-type recordset if that table is a local Access table (because the argument passed is a table, dbOpenTable is the default)

    The second statement opens a dynaset-type recordset (because the argument passed is a query, dbOpenDynaset is the default)

    The main difference between dynaset-type and table-type recordsets is that table-type recordsets support indexes.

    The following code will succeed and look up the entry with a primary key value of 5

    Set rs = CurrentDb.OpenRecordset("tblOPCEPLTB")
rs.Index = "PrimaryKey"
rs.Seek "=", 5

    The following will fail, since seeks are only supported on table-type recordsets:

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblOPCEPLTB")
rs.Index = "PrimaryKey"
rs.Seek "=", 5

    If you only wish to append records to a table, then specify that when opening the recordset:

    Set rs = CurrentDb.OpenRecordset("tblOPCEPLTB", Options:=dbAppendOnly)

    This way, no records will get locked or loaded, since the recordset will only support appending

See also questions close to this topic

  • How to Insert INTO a table while ignoring fields with no data on it?

    Good day Everyone!

    I'm having a problem with my coding. I wan't to INSERT data to a table but not all fields are required to be filled some are just optional. is there any way that It could be saved even if some fields have no data on it? I hope you could help me with.

    ==================================================================

                CurrentDb.Execute "INSERT INTO tbl_Records_of_Violation(Serial_Number,Badge,Rank,Lastname,Firstname,Middlename,Qualifier,Gender,Birthdate,Date_Ent_Svc,Address,MotherUnit,Unit,Date_of_Commission,Reporting_Officer,Report_to_Supervisor,Approving_Authority,Punishment,Remarks,Violation1) VALUES ('" & Me.Serial_Number & "','" & Me.Badge & "','" & Me.Rank & "','" & Me.Lastname & "','" & Me.Firstname & "','" & Me.Middlename & "','" & Me.Qualifier & "','" & Me.Gender & "','" & Me.Birthdate & "','" & Me.Date_Ent_Svc & "','" & Me.Address & "','" & Me.MotherUnit & "','" & Me.Unit & "','" & Me.Date_of_Commission & "','" & Me.Reporting_Officer & "','" & Me.Report_to_Supervisor & "','" & Me.Approving_Authority & "','" & Me.Punishment & "','" & Me.Remarks & "'," & Me.Violation1 & ")"
            MsgBox "Record has been updated", vbInformation, "Record Information"

    =====================================================================

    I want that even if one of the field is empty it would still be saved in my table. Please help me guys!

    Thanks

  • RLE an array of Text cells in Excel

    I have an Excel file that contains output from another program. It has the format of:

    Section of a larger array of values

    I would like to RLE encode this with a VBA Macro into a format similar to:

    First 3 lines RLE encoded.

    eg:
    0xff,0xff,0xff,0xff,0x00,0x00,0x00,0x00 would be encoded as 4,0xff,4,0x00 represented as one cell with the Number of occurrences of the first character, 0xff until a change of value in the row, then a new count is started with the next characters number of repeats.

    Is there a way to do this more simply that I am just not seeing?

  • How to do I use Excel to make three cells change when any of the others are changed?

    I want to make a table that can calculate the cost per week, per month and per year of any value.

    If the per week value is entered it needs to calculate the per month and per year value.

    If the per month value is entered, it needs to calculate the per week and per year value.

    If the per year value is entered, it needs to calculate the per week and per month value.

    I can only use three cells.

    I haven't a clue how to do this. I don't even know how to run the code in Excel. I heard you have to use VBA but I have never touched Visual Basic let alone, used in in Excel.

    Please can some one help. This is very important to me. Thanks in advance for any help :)

    This is the space I need to use

  • MS Word Drop down list or combo box from MS Access Database

    I'm trying to populate a combo box or drop down list from an access database. I used the following website for code template. I modified it to suit my needs. I keep getting the error: 5941 The requested member of the collection does not exist"

    Source code: http://www.fontstuff.com/mailbag/qword02.htm

    My Code:

    Private Sub Document_Open()
    On Error GoTo Document_Open_Err

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=*path to database removed for post*;"
    rst.Open "SELECT DISTINCT TOP 25 [Equipment] FROM tblEquipment ORDER BY [Equipment];", _
        cnn, adOpenStatic
    rst.MoveFirst

    With ActiveDocument.FormFields("Equipment").DropDown.ListEntries
        .Clear
        Do
            .Add rst![Equipment]
            rst.MoveNext
        Loop Until rst.EOF
    End With
Document_Open_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
Document_Open_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume Document_Open_Exit
End Sub
  • Average score for a given name within the current month

    I have a table data set of customer service reps score per call for the current year and I'm trying to build a query to calculate the average score for a given name within a given month

    Something like calculating the average score for Matt's calls for the month of January.

    I've been trying but I'm a begginer at SQL so I'm having trouble.

    So far I've got this:

    Select avg("general score") as General_Score, avg("Re_score") as Re_score 
from Voc2019 
Where [Name] ="Matt"

    This gives me a error type mismatch

    Would greatly appreciate any help you can provide to me.

  • Grouping Line Numbers in MS Access

    I am currently working with a set of data that I need to summarize in a particular way.

    The table has the following columns:

    • PO#
    • Dollar amount.

    The PO column could have multiple lines with the same PO#.

    I am trying to group PO's that have multiple lines and have MS Access generate and assign them a number. This is essentially what I am trying to achieve:

    this is essentially what I am trying to achieve

    My initial thinking was to create a query from the table in which the data is located and get MS Access to generate the numbers based on the POs, and then summarize everything in a report.

    Thoughts, comments?