Make Table Query - setting Table properties?

robjones23

Registered User.
Local time
Today, 21:20
Joined
Dec 16, 2004
Messages
66
Hi,

I need a make table query to run when a database is started - that's the easy part.

What i can't figure out is how to set the table field properties as it's created. I need two of the fields to be set as Date / Time however I can't find anything which seems to give me this option. I'm sure it's staring at me in the face - can someone point me in the right direction please??

The fields have to be set to Date / Time to allow another query to run and bring back the correct results.

At the moment, I'm settling for an amend query, however I can't figure out a way to automatically delete everything from the table after the DB has been closed (I could delete the table on close but this isn't helpful as i need the table there to be set with the Date / Time)

I'm probably going about this the wrong way - someone please help me!! :)

Cheers

Rob.
 
If your table always has the same structure, why not keep the table but just add and delete the data.

Add via an append query (as you are doing)
Delete via this SQL

DELETE * FROM tblTableName;

I would choose to keep the Table in place rather than create and delete it with each run as this will undoubtedly cause bloat and eventually reduce the efficiency of your Db.

hth
 
Thanks for responding but when I try:

"DELETE * FROM tblDates For TET"

I get an error saying:

"Compile error - Expected: expression" with the * highlighted.

Any ideas??
 
For TET


What's that supposed to be?
 
lol - its the name of the table - "Dates for TET" TET = Total Elapsed Time

is it a problem?
 
Just to add another question -

How do I get the make table query to run when a button is pressed?

I don't want to display anything of the query as it's only used to create the table, however it has to run and the table has to be present for another query (which interrogates the table created by the make table query) to run and then display a pivot chart form that query.

so:
Form loads up on open with various buttons for reports
User clicks "TET Report weekly" Button
"Dates for TET" query runs on a linked table
This Amends the "Dates for TET" table (with the Date / Time fields properties)
Another query "TET calculation" runs on the new "Dates for TET" table
This then creates a pivot Chart

I then need the DELETE * FROM etc... line to run when the original form is closed -OR- When the pivot chart is closed if thats possible?

Thanks for all the help everyone it's really appreciated!

Rob
 
Because you have spaces in the table name (bad move!) you have to surround things with square brackets.

"DELETE * FROM [tblDates For TET];"

Thing is though, just make a proper delete query and run it rather than recreating a temporary query every time you want to empty the table - your current method contributes to database bloat.
 
Hmm, I'm still getting the same error message,

I tried "DELETE * FROM tbl[Dates For TET];" too just in case the brackets had to surround only the table name. It's stopping at the "*" with the same error message as before.


Incidentally, I just tried running a delete table (I'd tried it previously but couldn't remember why I wasn't using it) and it states "you are about to delete X records from the table - are you sure" so I click yes, and it gives me a warning that I can't go back etc... so i click yes again and then it gives an error message:

----LOL - midway through writing this I just figured out what's causing the error I was getting - had it trying to delete from a linked table and not the dates for TET table----

Ok I think it might be sorted now :D

I'll check it and post back here either way - thanks for all the help!
 
Success!

It's working - I've now got the Append query to run with the button (with the warning messages off) and then when the pivot chart is closed and the front form re-activated the delete query runs to clear the table.

Woo.

Thanks very much for all the help it's really appreciated!
 
robjones23 said:
I've now got the Append query to run with the button (with the warning messages off)

Like this?

Code:
With DoCmd
    .SetWarnings False
    .OpenQuery "QueryName
    .SetWarnings True
End With
 
hmmm, no, i did it via a macro, there's an option in there to set warning messages to "no".

by the looks of the code though I'd guess they're doing the same thing.

Is there a way to automatically compact the database when it's closed? At the mo it's about 1.2MB after a compression and will rise as these macros are ran (data sets are going to get quite highe - 60,000+ records each month expected by may next year with more this time next year). it might become cumbersome in which case I'll probably need to revisit how the DB is working.

This is my second week using Access though (Since my school project about 6 / 7 years ago!) and I've never written any SQL so I think I'm doing ok so far lol :) Need to read up on programming SQL perhaps.
 
I found Tools - Options - General - Compact on close but is this a database specific (i.e. it will follow the database around as users open / close it) or is it user profile / workstation specific (i.e. each workstation must set the option individually) ???
 
robjones23,
You're off to a bad start. You're using spaces in you names and you're using macros. A professional should do neither.

Fizzio,
I would choose to keep the Table in place rather than create and delete it with each run as this will undoubtedly cause bloat and eventually reduce the efficiency of your Db.
- deleting the rows will also cause bloat. Access cannot recover the space for the deleted rows/table until the db is compacted.
 
Pat Hartman said:
Fizzio,
- deleting the rows will also cause bloat. Access cannot recover the space for the deleted rows/table until the db is compacted.

Thanks Pat, I did not realise that deleting rows created bloat but I knew that the space could not be recovered until compact. However, surely making and deleting a table will cause irretrievable bloat whereas with deleting records, the bloat will be 'recovered' during the compact process, or is this a misconception of mine?
 
Whether you delete the table or just the rows, the space taken by the deleted rows is not recovered until the db is compacted. So the effect is the same, you delete a table containing 1000 rows or you delete 1000 rows. The space those rows occupied remains in the db, unused and unavailable, until compact. Deleting and recreating tables would add additional overhead in that the actual table object is being manipulated. But, that space would also be recovered when the db is compacted.

I rarely use temporary tables. They are problematic in a shared database and besides, Access can use queries interchangably with tables so there is very little need to actually create a table when you can usually just use the query that would have created the table.
 
Good point Pat.
Thanks for clarifying the Db bloat issue.
 
Pat Hartman said:
robjones23,
You're off to a bad start. You're using spaces in you names and you're using macros. A professional should do neither.

Lol, thanks for the vote of confidence pat! I've been using access for two weeks!

I never said I was a professional :)
 
Pat Hartman said:
Whether you delete the table or just the rows, the space taken by the deleted rows is not recovered until the db is compacted. So the effect is the same, you delete a table containing 1000 rows or you delete 1000 rows. The space those rows occupied remains in the db, unused and unavailable, until compact. Deleting and recreating tables would add additional overhead in that the actual table object is being manipulated. But, that space would also be recovered when the db is compacted.

I rarely use temporary tables. They are problematic in a shared database and besides, Access can use queries interchangably with tables so there is very little need to actually create a table when you can usually just use the query that would have created the table.

While on the subject - is there any code I could add to the Database so that on close it automatically compacts? Rather than set the option on each PC the database is used on? (There's only 3 people who will be using it though!!)
 

Users who are viewing this thread

Back
Top Bottom