469,913 Members | 2,017 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

Updating an open recordset in Access + Separate Array Question

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

Expand|Select|Wrap|Line Numbers
  1. Dim con1 As ADODB.Connection
  2. Dim con2 As ADODB.Connection
  3. Dim recSet1 As ADODB.Recordset
  4. Dim recSet2 As ADODB.Recordset
  5. Set con1 = CurrentProject.Connection
  6. Set con2 = CurrentProject.Connection
  7. Set recSet1 = New ADODB.Recordset
  8. Set recSet2 = New ADODB.Recordset
  9.  
  10. recSet1.CursorLocation = adUseClient
  11. 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:

Expand|Select|Wrap|Line Numbers
  1. Function FILESYS_GetObject()
  2.  
  3.     Set FILESYS_GetObject = CreateObject("Scripting.FileSystemObject")
  4.  
  5. End Function
  6.  
  7.         Function File_FileCreate(filePath3)
  8.             Set FileSys = FILESYS_GetObject()
  9.             Create = True
  10.             ForWriting = 2
  11.             Set FileStream = FileSys.CreateTextFile(filePath3, 2, False)
  12.         End Function
  13.         Function FILE_WriteAll(filePath3, ElementC)
  14.  
  15.             ForAppending = 8
  16.             Create = True
  17.  
  18.             Set FileSys = FILESYS_GetObject()
  19.  
  20.                 If FILESYS_GetObject.FileExists(filePath3) = False Then
  21.                     File_FileCreate (filePath3)
  22.                 End If
  23.  
  24.                 Set FileStream = FileSys.OpenTextFile(filePath3, ForAppending, False)
  25.                     FileStream.Write (ElementC)
  26.                 FileStream.Close
  27.  
  28.         End Function

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

Expand|Select|Wrap|Line Numbers
  1. recSet1.AddNew
  2. DoCmd.OpenQuery ("ClearTableElements")
  3. 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!
Feb 13 '08 #1
2 2513
PianoMan64
374 Expert 256MB
I am opening the following connections as such although I am only referring to the first connection for this question:


Expand|Select|Wrap|Line Numbers
  1. Dim con1 As ADODB.Connection
  2. Dim con2 As ADODB.Connection
  3. Dim recSet1 As ADODB.Recordset
  4. Dim recSet2 As ADODB.Recordset
  5. Set con1 = CurrentProject.Connection
  6. Set con2 = CurrentProject.Connection
  7. Set recSet1 = New ADODB.Recordset
  8. Set recSet2 = New ADODB.Recordset
  9.  
  10. recSet1.CursorLocation = adUseClient
  11. recSet1.Open "tblElements", con1, adOpenKeyset, adLockOptimistic
  12.  
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:


Expand|Select|Wrap|Line Numbers
  1.  
  2. Function FILESYS_GetObject()
  3.  
  4.     Set FILESYS_GetObject = CreateObject("Scripting.FileSystemObject")
  5.  
  6. End Function
  7.  
  8.         Function File_FileCreate(filePath3)
  9.             Set FileSys = FILESYS_GetObject()
  10.             Create = True
  11.             ForWriting = 2
  12.             Set FileStream = FileSys.CreateTextFile(filePath3, 2, False)
  13.         End Function
  14.         Function FILE_WriteAll(filePath3, ElementC)
  15.  
  16.             ForAppending = 8
  17.             Create = True
  18.  
  19.             Set FileSys = FILESYS_GetObject()
  20.  
  21.                 If FILESYS_GetObject.FileExists(filePath3) = False Then
  22.                     File_FileCreate (filePath3)
  23.                 End If
  24.  
  25.                 Set FileStream = FileSys.OpenTextFile(filePath3, ForAppending, False)
  26.                     FileStream.Write (ElementC)
  27.                 FileStream.Close
  28.  
  29.         End Function
  30.  

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


Expand|Select|Wrap|Line Numbers
  1. recSet1.AddNew
  2. DoCmd.OpenQuery ("ClearTableElements")
  3. recSet1.Update
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim con1 as ADODB.Connection
  3. Dim cmd1 as ADODB.Command
  4. Dim RecSet1 as ADODB.Recordset
  5.  
  6. Set con1 = CurrentProject.Connection
  7.  
  8. With cmd1
  9.  
  10. Set .Activeconnection = con1
  11. .CommandText = "<Your SQL Statement that you want to do>"
  12. .CommandType = adCmdText
  13. End with
  14.  
  15. Set RecSet1 = cmd.Execute
  16.  
  17.  
This will accomplish that it is that you're wanting to do.
Feb 26 '08 #2
Kosmos
153 100+
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!
Mar 31 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by barret bonden | last post: by
5 posts views Thread by zMisc | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.