Phantom Table

[Waterman]

Hi!

I would like to create a table that exists only in memory
while the subroutine is running. I get hung up when it
comes time to refresh the table defs. What I've written
so far only works if I append the table. What am I
missing?

Sub PhantomTable()

Dim dbs As Database
Dim rst, rst1, rst2 As Recordset
Dim tdf As TableDef
Dim strSQL, txtFirst, txtSecond As String


Set dbs = OpenDatabase("C:\Working
Models\PhantomTable.mdb")


Set tdf = dbs.CreateTableDef("Phantom")


With tdf

.Fields.Append .CreateField("FirstField", dbText)
.Fields.Append .CreateField("SecondField", dbText)

End With

dbs.TableDefs.Append tdf 'remember, I don't want to
append
dbs.TableDefs.Refresh

Set rst1 = dbs.OpenRecordset(tdf, dbOpenDynaset)


With rst1
.AddNew
!FirstField = "black"
!SecondField = "dog"
.Update
.AddNew
!FirstField = "blue"
!SecondField = "bird"
.Update
End With

Set rst2 = dbs.OpenRecordset(tdf, dbOpenTable)

With rst2
.MoveLast
.MoveFirst
Do While Not .EOF

strSQL = "Select Phantom.FirstField,
Phantom.SecondField from Phantom;"
txtFirst = rst2!FirstField
txtSecond = rst2!SecondField

MsgBox txtFirst & " " & txtSecond
.MoveNext

Loop
.Close

End With
End Sub

 

[Tim Ferguson]

What I've written
so far only works if I append the table.

Well, of course it does: the table only exists once it is part of the
database. What is it that you are trying to achieve?

Oh, and a separate point: would it not be quicker and easier to do all this
in three SQL commands?

B Wishes


Tim F

 

[Waterman]

Thank you, Tim, for your prompt response!

My primary goal is to create this table purely in memory,
without creating the actual 'physical' table (please
forgive me; I may not be using the correct terminology).

It's an experiment to see if I can use VBA as a means to
prevent some of the 'database bloating' I encounter. I
do have situations where a temporary table comes in
handy. However, though I can delete a 'physical'
temporary table after it's served its purpose, my size of
my database still reflects its presence. In a multi-user
environment, it's not always feasible to compact the
database immediately.

My thought was that if the temporary table only existed
in the confines of a VBA module in an 'ethereal' state, I
might avoid the bloating altogether.

Your point is well taken about the SQL. However, I don't
know that I can achieve the results described above using
SQL.

I appreciate your feedback and welcome your additional
thoughts on the matter.

 

[John Vinson]

It's an experiment to see if I can use VBA as a means to
prevent some of the 'database bloating' I encounter. I
do have situations where a temporary table comes in
handy. However, though I can delete a 'physical'
temporary table after it's served its purpose, my size of
my database still reflects its presence. In a multi-user
environment, it's not always feasible to compact the
database immediately.

One way to deal with this problem is to use the CreateDatabase method
to create a new .mdb file on the user's disk, just to hold a linked
temp table (or tables). Use the KILL command to delete the temporary
database when you're done with it.

 

[Waterman]

Thank you, John! That is a great suggestion!