How to add records to a temporary table object in memory...? 3

[spinjector]

I am trying to create a temporary table in memory to manipulate some data on a form. I am trying to do it without appending it to the TableDefs collection. (Is this possible?)

When I try to open a table-type recordset on the table, I get the following error:

Run-time error '3420': Object invalid or no longer set.

Here is a simplified version of my code with comments:

Public Sub Test()
    
    Dim db As DAO.Database
    Dim t As DAO.TableDef
    Dim f As DAO.Field
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    
    Set t = db.CreateTableDef()
    
    Set f = t.CreateField("Name", dbText)
    t.Fields.Append f
                                    '<--- i am probably missing something here
                                    '     or not doing the next line correctly
    Set rs = t.OpenRecordset()      '<--- runtime error 3420 here
    
    rs.Fields("Name") = "test"
    rs.Update

End Sub

Any suggestions?

Thanks.

 

[PHV]

And what about something like this ?
DoCmd.RunSQL "CREATE TEMPORARY TABLE ...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cmmrfrds]

Do you want to create a recordset dynamically and then destroy when you are done? Does the recordset need to be set to a Form's recordset to be viewed on a Form? What is the use of this recordset?

 

[spinjector]

I am using it to sort and/or re-order the contents of a group of related listboxes on a form. I have some sorting options, and two sets of those "Add", "Remove", "Move Up", and "Move Down" buttons you see in some programs.

I have most of it worked out, I just need a way to create temporary tables or recorsets in memory to hold the items added to the lists by the user.

I had fiddled with arrays of a custom type, but the data is multi-column in nature, and its tough using multiple columns with the "Value List" setting and semi-colon delimited lists for the RowSource.

So since a listbox can be bound to a table or query, using one of those seems ideal for what I am doing. That way I can easily sort and reorder, as well as insert or delete specific rows.

 

[cmmrfrds]

A temporary table in the sense that it is create and deleted in the program. This is probably the easiest, but you should compact the program at some point to get rid of the space the temporary table used. You can use the method PHV suggests for this and then issue a Drop Table at the end of the process.

You can create temporary recordsets in ADO and these are easy enough, except there is no AddItem Method in vba as there is in Visual Basic. Now, I work with access 2000 and later versions may have the AddItem method for listboxes. With the AddItem method the rows in the recordset could be transferred to the listbox. The advantage would be since the recordset is memory only then once the memory allocated for the recordset is destroyed it goes away.

 

[spinjector]

Yes that is exactly what I want - a table in memory that goes away when the form is closed. I do not want a real table to be appended to the TableDefs collection because this will be a multiuser database and if the tables were created in the MDB, database bloat would get out of control.

I looked at CREATE TEMPORARY TABL...E, but it seems to me that it would creat a real table and that is not what I want.

And the problem with ADO is that I have already written most of my APP in DAO, so I need to continue using DAO or things will get messy.

So... By using DAO (not ADO), how can I create a temporary table in memory that I can make go away when the form is closed and does not get created on disk within the MDB file...?

 

[cmmrfrds]

I can't help with the DAO since I don't use it anymore. Although, I do have programs that use both DAO and ADO, and it does not need to be messy. Just do a one time change to explicitly define all DAO objects with the library prefix.
i.e. DAO.Recordset
DAO.Fields
etc...
Then, explicitly define any ADO object you may use.
ADODB.Recordset
ADODB.Fields
etc..
Then, in the future always explicitly define the objects.

 

[spinjector]

Thanks, but I do that already.

So how would you do a temporary table in ADO? perhaps I can adapt that to my DAO once I see how it's done...

 

[cmmrfrds]

Here is a simple example. Just paste the Function into a module and run it.

Public Function FabricatedRS()

Dim rs As ADODB.Recordset
Dim varArray As Variant
Set rs = New ADODB.Recordset

With rs.Fields
.Append "myid", adInteger
.Append "mydesc", adVarChar, 50, adFldIsNullable
End With

varArray = Array("myid", "mydesc")

With rs
.Open
.AddNew varArray, Array(1, "first desc")
.AddNew varArray, Array(2, "second desc")
.AddNew varArray, Array(3, "third desc")
.AddNew varArray, Array(4, "fourth desc")
End With
rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Sort = "myid DESC"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = "myid = 3"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = adFilterNone

rs.Close
Set rs = Nothing

End Function

 

[NewCoder2]

How can I use this same code and populate the rs with excel data?

 

[cmmrfrds]

I am not sure of the question? You can link an excel spreadsheet to access and treat it like an access table. Please elaborate on what you need.

 

[NewCoder2]

first let me apologize for my lack of detail in my question. Next let me say that I appreciate any thought's you might have. What I meant by my question was that I noticed that your creating a FabricatedRS, I'm hoping I can do the same with data from excel. I need to be able to pull data from an excel spreadsheet and then tranfer it to a txt file. I can't link the excel file, and I can't create a temp table. So really just holding the data in memory is the only option. And this thread looked like it started out that way. Is it possible?

Thanks.

 

[cmmrfrds]

Using vba code, you could read an excel sheet through a ADO recordset and then write out the records to the text file with FSO. I can give you an example of setting up a connection to an excel spreadsheet and creating an ADO recordset. I don't have the code for writing an FSO text file at home but have an example at work. It is not very difficult and I will try and remember to paste it in tomorrow.

ADO example.

Public Function UpdateExcel()
On Error GoTo ErrHandler
Dim cn As New Connection, cn2 As New Connection
Dim rs As New Recordset, rs2 As New Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ATestDir\myTest.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
cn.ConnectionString = connString
cn.Open connString
''cn2.Open connString

Set rs.ActiveConnection = cn
'-- sheet name = newcustomers
sql1 = "select * from newcustomers"
rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field value = "; rs.Fields(0).Value
End If
'--- you could insert code to loop and write text file here.
Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function

 

[cmmrfrds]

Example of writing a text file with FSO. Would need a reference to the Microsoft Runtime Scripting libaray.

Function CreateFile()
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\testdir\testout.txt", True)
' Write a line with a newline character.
tf.WriteLine ("Testing 1, 2, 3.")
' Write three newline characters to the file.
tf.WriteBlankLines (3)
' Write a line.
tf.Write ("This is a test.")
tf.Close
End Function

 

[NewCoder2]

Great, thanks, I ended figuring out the write to FSO late last night. But you have helped me a great deal. Thanks so much!!

 

[PRMiller]

Hi cmmrfrds,

I stumbled upont this post while experimenting with creating temporary tables. This did exactly what I need it to do. Have a star!

Thanks,
Paul

 

[cmmrfrds]

Thank you PerkinsSlave, I am glad it was helpful to you.