Delete file but still in memory

[Nelson The Missing Lead]

Hi,

I have this code where every time i select a stock name, it will query from
the main stock table and append that particular stock's all the fields and
records into a new table created. After that, I will use this table to
perform some calculation.

However, it seems like even i have delete the existing table as in my code.
The previous stock data is still in the table which create error.

Anybody could advise me! Enclose herewith the code:

Function CreateTable()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
On Error Resume Next

'If the table already exists, delete it
DBEngine(0)(0).TableDefs.Delete "SGX Individual Historical"

On Error GoTo 0

'Create the table definition in memory
Set tdf = dbs.CreateTableDef("SGX Individual Historical")

'Specify the fields
With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'Date field
.Fields.Append .CreateField("Trade Date", dbDate)

'Stock Name field
.Fields.Append .CreateField("Stock Name", dbText, 30)

'Remarks field
.Fields.Append .CreateField("Remarks", dbText, 8)

'Currency field
.Fields.Append .CreateField("Currency", dbText, 4)

'Close of the day field
.Fields.Append .CreateField("Close", dbDouble, 3)

'Changes field
.Fields.Append .CreateField("Change", dbDouble, 3)

'Volume field
.Fields.Append .CreateField("Volume", dbLong)

'High field
.Fields.Append .CreateField("High", dbDouble, 3)

'Low field
.Fields.Append .CreateField("Low", dbDouble, 3)

'Turnover Value field
.Fields.Append .CreateField("Value", dbLong)

'14days Average Value field
.Fields.Append .CreateField("DaysAvg14", dbDouble, 3)

End With

'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf

'Refresh the TableDefs collection
dbs.TableDefs.Refresh

Application.RefreshDatabaseWindow 'Show the changes

Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Function

Function CreateIndexes()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("SGX Individual Historical")

Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ID")
.Unique = False
.Primary = True
End With

tdf.Indexes.Append ind

'Refresh the display of this collection
tdf.Indexes.Refresh

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing

End Function


Function DaysAvgs()
'Calculate the average value of a given value.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varBookmark As Variant
Dim numAve, numDaysAvg As Double
Dim intA, lngCount As Integer

Set db = CurrentDb

'Open Table
Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenDynaset)


rst.MoveFirst

Do While Not rst.EOF
intA = 1
varBookmark = rst.Bookmark
numDaysAvg = 0
numAve = 0

For intA = 1 To 14
numAve = numAve + rst.Fields!Close
rst.MoveNext
If rst.EOF Then Exit For
Next intA

rst.Bookmark = varBookmark
numDaysAvg = numAve / intA
rst.Edit
rst.Fields!DaysAvg14 = numDaysAvg
rst.Update
rst.MoveNext
Loop

rst.Close

Set rst = Nothing
Set db = Nothing


End Function

Thank you

Nelson Chou

 

[Jim Burke in Novi]

What I do in cases like that (as long as the column names are always the
same) is just create a table in the application that has all the fields
already defined and just run a query to delete all the rows, then run an
append query to append the new rows onto the now-empty table

 

[Nelson The Missing Lead]

Hi,

I run macro to run the code.......
At the start of the macro....

RunCode CreateTable
RunCode CreateIndexes
OpenForm ("Stock List") "Open Form to select stock name from the combo list
CancelEvent (If Not Isloaded("Stock List")
Close
SetValue
OpenQuery "Open a query and append the recordset into the new table
RunCode DaysAvgs
OpenTable
Close

It seems like i can't delete and recreate a new table.......... what
happen????

Please help.....I have been cracking my brain.....

Nelson Chou
..

 

[Jim Burke in Novi]

Is that table in the same database as the application, or do you have the
tables linked? If the table was originally created in a 'remote DB', with
those tables linked to the application, that could explain it. Other than
that I'm not sure why a delete wouldn't work, though I've never use the
method you're using. If I'm deleting a table I use the command

DoCmd.DeleteObject acTable, "tblToBeDeleted"

and have never had a problem.

 

[Dirk Goldgar]

In

Hi,

I have this code where every time i select a stock name, it will
query from the main stock table and append that particular stock's
all the fields and records into a new table created. After that, I
will use this table to perform some calculation.

However, it seems like even i have delete the existing table as in my
code. The previous stock data is still in the table which create
error.

Anybody could advise me! Enclose herewith the code:

Function CreateTable()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
On Error Resume Next

'If the table already exists, delete it
DBEngine(0)(0).TableDefs.Delete "SGX Individual Historical"

On Error GoTo 0

'Create the table definition in memory
Set tdf = dbs.CreateTableDef("SGX Individual Historical")

'Specify the fields
With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'Date field
.Fields.Append .CreateField("Trade Date", dbDate)

'Stock Name field
.Fields.Append .CreateField("Stock Name", dbText, 30)

'Remarks field
.Fields.Append .CreateField("Remarks", dbText, 8)

'Currency field
.Fields.Append .CreateField("Currency", dbText, 4)

'Close of the day field
.Fields.Append .CreateField("Close", dbDouble, 3)

'Changes field
.Fields.Append .CreateField("Change", dbDouble, 3)

'Volume field
.Fields.Append .CreateField("Volume", dbLong)

'High field
.Fields.Append .CreateField("High", dbDouble, 3)

'Low field
.Fields.Append .CreateField("Low", dbDouble, 3)

'Turnover Value field
.Fields.Append .CreateField("Value", dbLong)

'14days Average Value field
.Fields.Append .CreateField("DaysAvg14", dbDouble, 3)

End With

'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf

'Refresh the TableDefs collection
dbs.TableDefs.Refresh

Application.RefreshDatabaseWindow 'Show the changes

Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Function

If I were you, I wouldn't delete and recreate the tabledef each time.
It would be more efficient just to execute a delete query to empty the
table.

However, as to what is causing your problem, the only thing that jumps
out at me is this:

Set dbs = CurrentDb [...]
DBEngine(0)(0).TableDefs.Delete "SGX Individual Historical" [...]
Set tdf = dbs.CreateTableDef("SGX Individual Historical")

You're not using the same database object to delete the tabledef as you
are using to create it. Conceivably the dbs object isn't aware of what
you did in the DBEngine(0)(0) object. I'd expect your error-handling to
report an error if that's the case, but still, what happens if you use
this instead:

dbs.TableDefs.Delete "SGX Individual Historical"

?

 

[Nelson The Missing Lead]

Hi

Thank all of you.....i manage to figure out what is happen!!!!

I have to rearrange my macro.............some of the function calling sequence

Nelson Chou
Hurray.....

In

Hi,

I have this code where every time i select a stock name, it will
query from the main stock table and append that particular stock's
all the fields and records into a new table created. After that, I
will use this table to perform some calculation.

However, it seems like even i have delete the existing table as in my
code. The previous stock data is still in the table which create
error.

Anybody could advise me! Enclose herewith the code:

Function CreateTable()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
On Error Resume Next

'If the table already exists, delete it
DBEngine(0)(0).TableDefs.Delete "SGX Individual Historical"

On Error GoTo 0

'Create the table definition in memory
Set tdf = dbs.CreateTableDef("SGX Individual Historical")

'Specify the fields
With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'Date field
.Fields.Append .CreateField("Trade Date", dbDate)

'Stock Name field
.Fields.Append .CreateField("Stock Name", dbText, 30)

'Remarks field
.Fields.Append .CreateField("Remarks", dbText, 8)

'Currency field
.Fields.Append .CreateField("Currency", dbText, 4)

'Close of the day field
.Fields.Append .CreateField("Close", dbDouble, 3)

'Changes field
.Fields.Append .CreateField("Change", dbDouble, 3)

'Volume field
.Fields.Append .CreateField("Volume", dbLong)

'High field
.Fields.Append .CreateField("High", dbDouble, 3)

'Low field
.Fields.Append .CreateField("Low", dbDouble, 3)

'Turnover Value field
.Fields.Append .CreateField("Value", dbLong)

'14days Average Value field
.Fields.Append .CreateField("DaysAvg14", dbDouble, 3)

End With

'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf

'Refresh the TableDefs collection
dbs.TableDefs.Refresh

Application.RefreshDatabaseWindow 'Show the changes

Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Function

If I were you, I wouldn't delete and recreate the tabledef each time.
It would be more efficient just to execute a delete query to empty the
table.

However, as to what is causing your problem, the only thing that jumps
out at me is this:

Set dbs = CurrentDb [...]
DBEngine(0)(0).TableDefs.Delete "SGX Individual Historical" [...]
Set tdf = dbs.CreateTableDef("SGX Individual Historical")

You're not using the same database object to delete the tabledef as you
are using to create it. Conceivably the dbs object isn't aware of what
you did in the DBEngine(0)(0) object. I'd expect your error-handling to
report an error if that's the case, but still, what happens if you use
this instead:

dbs.TableDefs.Delete "SGX Individual Historical"

?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)