Updating an open recordset in Access + Separate Array Question

[Kosmos]

I am opening the following connections as such although I am only referring to the first connection for this question:

Dim con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim recSet1 As ADODB.Recordset
Dim recSet2 As ADODB.Recordset
Set con1 = CurrentProject.Connection
Set con2 = CurrentProject.Connection
Set recSet1 = New ADODB.Recordset
Set recSet2 = New ADODB.Recordset
 
recSet1.CursorLocation = adUseClient
recSet1.Open "tblElements", con1, adOpenKeyset, adLockOptimistic

The program uses some vbscript functions to read a file and then uses some vbscript functions to write to a file based on certain conditions. I can't post most of my code because technically I don't own it but for the purpose of giving back here are the vbscript functions I'm using to write to a file:

Function FILESYS_GetObject()
 
    Set FILESYS_GetObject = CreateObject("Scripting.FileSystemObject")
 
End Function
 
        Function File_FileCreate(filePath3)
            Set FileSys = FILESYS_GetObject()
            Create = True
            ForWriting = 2
            Set FileStream = FileSys.CreateTextFile(filePath3, 2, False)
        End Function
        Function FILE_WriteAll(filePath3, ElementC)
 
            ForAppending = 8
            Create = True
 
            Set FileSys = FILESYS_GetObject()
 
                If FILESYS_GetObject.FileExists(filePath3) = False Then
                    File_FileCreate (filePath3)
                End If
 
                Set FileStream = FileSys.OpenTextFile(filePath3, ForAppending, False)
                    FileStream.Write (ElementC)
                FileStream.Close
 
        End Function

After writing to the file I run the following to clear recordset1:

recSet1.AddNew
DoCmd.OpenQuery ("ClearTableElements")
recSet1.Update

Can I do this on an open recordset connection?

If not...can I close the recordset and then re-open it to run this query?

Also - the data I'm recording is separated into segments and elements. The elements are the different parts of the segments...but I am running into character limitations with using text fields in access.

The truth is that it would be easier and faster for the program to read by the elements, but write by the segment - the problem is that I have to store a lot of segments and I was not sure on what limitations exist for arrays.... I was hoping someone could provide me with information on the limitations of what I can store in each value of an array and any other limitations that exist.

Any help would be greatly appreciated. Thanks!

[PianoMan64]

I am opening the following connections as such although I am only referring to the first connection for this question:

Dim con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim recSet1 As ADODB.Recordset
Dim recSet2 As ADODB.Recordset
Set con1 = CurrentProject.Connection
Set con2 = CurrentProject.Connection
Set recSet1 = New ADODB.Recordset
Set recSet2 = New ADODB.Recordset
 
recSet1.CursorLocation = adUseClient
recSet1.Open "tblElements", con1, adOpenKeyset, adLockOptimistic
 

The program uses some vbscript functions to read a file and then uses some vbscript functions to write to a file based on certain conditions. I can't post most of my code because technically I don't own it but for the purpose of giving back here are the vbscript functions I'm using to write to a file:

 
Function FILESYS_GetObject()
 
    Set FILESYS_GetObject = CreateObject("Scripting.FileSystemObject")
 
End Function
 
        Function File_FileCreate(filePath3)
            Set FileSys = FILESYS_GetObject()
            Create = True
            ForWriting = 2
            Set FileStream = FileSys.CreateTextFile(filePath3, 2, False)
        End Function
        Function FILE_WriteAll(filePath3, ElementC)
 
            ForAppending = 8
            Create = True
 
            Set FileSys = FILESYS_GetObject()
 
                If FILESYS_GetObject.FileExists(filePath3) = False Then
                    File_FileCreate (filePath3)
                End If
 
                Set FileStream = FileSys.OpenTextFile(filePath3, ForAppending, False)
                    FileStream.Write (ElementC)
                FileStream.Close
 
        End Function
 

After writing to the file I run the following to clear recordset1:

recSet1.AddNew
DoCmd.OpenQuery ("ClearTableElements")
recSet1.Update
 

Can I do this on an open recordset connection?

If not...can I close the recordset and then re-open it to run this query?

Also - the data I'm recording is separated into segments and elements. The elements are the different parts of the segments...but I am running into character limitations with using text fields in access.

The truth is that it would be easier and faster for the program to read by the elements, but write by the segment - the problem is that I have to store a lot of segments and I was not sure on what limitations exist for arrays.... I was hoping someone could provide me with information on the limitations of what I can store in each value of an array and any other limitations that exist.

Any help would be greatly appreciated. Thanks!

In answering your question, the answer would be a resounding NO.

What I would do is simply create the connection as you've done, and when you go to open the file, you use the ADO functions that are available to you to manipulate the data that is in the table structure.

You have RecSet1, you need to define a command to the ADO connection. This way you can issue SQL commands against the table.

Example:

 
Dim con1 as ADODB.Connection
Dim cmd1 as ADODB.Command
Dim RecSet1 as ADODB.Recordset
 
Set con1 = CurrentProject.Connection
 
With cmd1
 
Set .Activeconnection = con1
.CommandText = "<Your SQL Statement that you want to do>"
.CommandType = adCmdText
End with
 
Set RecSet1 = cmd.Execute
 
 

This will accomplish that it is that you're wanting to do.

[Kosmos]

Thanks for the reply I eventually realized that rs.sort would carry out what I was attempting to achieve....but I think this will be useful to others who are attempting to do what I was trying to do. Thanks!