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:


[code=vb]
Dim con1 As ADODB.Connectio n
Dim con2 As ADODB.Connectio n
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.CursorL ocation = adUseClient
recSet1.Open "tblElement s", con1, adOpenKeyset, adLockOptimisti c
[/code]

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:


[CODE=VB]

Function FILESYS_GetObje ct()

Set FILESYS_GetObje ct = CreateObject("S cripting.FileSy stemObject")

End Function

Function File_FileCreate (filePath3)
Set FileSys = FILESYS_GetObje ct()
Create = True
ForWriting = 2
Set FileStream = FileSys.CreateT extFile(filePat h3, 2, False)
End Function
Function FILE_WriteAll(f ilePath3, ElementC)

ForAppending = 8
Create = True

Set FileSys = FILESYS_GetObje ct()

If FILESYS_GetObje ct.FileExists(f ilePath3) = False Then
File_FileCreate (filePath3)
End If

Set FileStream = FileSys.OpenTex tFile(filePath3 , ForAppending, False)
FileStream.Writ e (ElementC)
FileStream.Clos e

End Function
[/code]


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


[CODE=VB]
recSet1.AddNew
DoCmd.OpenQuery ("ClearTableEle ments")
recSet1.Update
[/CODE]

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:
[CODE=VB]

Dim con1 as ADODB.Connectio n
Dim cmd1 as ADODB.Command
Dim RecSet1 as ADODB.Recordset

Set con1 = CurrentProject. Connection

With cmd1

Set .Activeconnecti on = con1
.CommandText = "<Your SQL Statement that you want to do>"
.CommandType = adCmdText
End with

Set RecSet1 = cmd.Execute

[/Code]

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!