• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

How to create a recordset, not connected to database, but I can add new item to it?

Hi,

i would like to create  a new recordset, but not connecting to any database. I have a list of items which I can only add into the recordset during runtime.

I wrote the below, but was told by the system that "Operation is not allowed when the object is closed" . I tried to open it, but I receive error "The connection cannot be used to perform this operation. It is either closed or invalid in this context"

   Dim country() as string
    country = Split(gCountryFromDB, "|", , vbTextCompare)
    Dim rsCountryM As New ADODB.Recordset
    rsCountryM.Open
    For i = 0 To UBound(country)
        rsCountryM.AddNew "sCountryCode", country(i)
    Next i

Please help! Thanks!
0
sata
Asked:
sata
1 Solution
 
Stephen MandersonCommented:
Hi there you can use this connection.

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;"
conn.Open

sqlstr = "Table"

rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic

    For i = 0 To UBound(country)
        rs.AddNew
        rs.Fields("sCountryCode")  = UBound(i) .txt
        rs.Update

    Next i

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
0
 
vinnyd79Commented:
You have to add fields to the recordset.This simple example will create a recordset and add three items to it,without a database.


' add reference to Microsoft ActiveX Data Objects Library

Dim rstADO As ADODB.Recordset

Private Sub Command1_Click()
Set rstADO = New ADODB.Recordset
With rstADO.Fields
  .Append "MyTextField", adVarChar, 50 'Character field, varchar type max 50 chars
  .Append "MyNumField", adInteger
End With

rstADO.Open
rstADO.AddNew
rstADO.Fields("MyTextField") = "My Text Item 1"
rstADO.Fields("MyNumField") = 1
rstADO.Update

rstADO.AddNew
rstADO.Fields("MyTextField") = "My Text Item 2"
rstADO.Fields("MyNumField") = 2
rstADO.Update

rstADO.AddNew
rstADO.Fields("MyTextField") = "My Text Item 3"
rstADO.Fields("MyNumField") = 3
rstADO.Update


rstADO.MoveFirst
While Not rstADO.EOF = True
    MsgBox rstADO.Fields("MyTextField")
    MsgBox rstADO.Fields("MyNumField")
    rstADO.MoveNext
Wend

rstADO.Close
Set rstADO = Nothing
End Sub
0
 
Éric MoreauSenior .Net ConsultantCommented:
You need to add fields. See this sample:

Private Sub Command1_Click()
Dim i As Integer
Dim rsCountryM As New ADODB.Recordset

    With rsCountryM
        With .Fields
            .Append "Field1", adVarChar, 50
            .Append "Field2", adVarChar, 50
        End With
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .Open
    End With
   
    For i = 0 To 10
        With rsCountryM
            .AddNew
            .Fields("Field1").Value = "Line " & CStr(i)
            .Fields("Field2").Value = "Value " & CStr(i)
            .Update
        End With
    Next
   
    MsgBox rsCountryM.RecordCount
End Sub


0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now