Code or Macro maybe?

[Sean]

I am not sure what would be best or how to do it, so if anyone could assist,
or point me in the right direction, that would be great.

I have a database attached to an Excel spreadsheet. The spreadsheet is the
raw data for all the queries in the database. The spreadsheet is over
written each morning with new data. Everyday, I do a make table query and
save the data for historical reporting. How do I program a button on a form
or set up a macro to do this for me so I do not have to it manually everyday
where I am always changing the table name. I want the name of the table to
be Backlog with the current days date appened to it, so today would be
Backlog072709 and tomorrow's would be Backlog072809.
Thanks in advance,

 

[Jerry Whittle]

I highly recommend changing your file naming to a yyyymmdd format. That way
the table names will sort much better if you are looking for a specific date.

Put the following in a module. Change ASA to the correct name of the linked
spreadsheet. Put DoSQL on the On Click event of a command button. Make sure
to debug and save the module.

Public Sub DoSQL()

Dim SQL As String
Dim TheDate As String

TheDate = Format(Date, "yyyymmdd")

DoCmd.SetWarnings False
SQL = "SELECT * INTO " & _
"Backlog" & TheDate & _
" FROM ASA "

DoCmd.RunSQL SQL
DoCmd.SetWarnings True

End Sub

 

[Sean]

Thanks so much. I even played with it and got it to work using a query as
the raw data to make a table. Question, is there a bit of code I can use
that will rename a table 1st before I would run code like you sent me? So If
I have a table called PreviousDay" and I want to rename that table to
PreviousDay20090724, then run the code to create a new table previous day?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You are going down a black hole. You should NOT be making new tables
for each days data. You should be importing that data into one table
that has a column (field) that would hold the date that the data was
imported - call it "import_date." If the spreadsheet (s/s) has the same
name you could use a QueryDef to import the data - like this:

INSERT INTO Imports (import_date, <other column names>)
SELECT Date(), <other s/s column names>
FROM spreadsheet_name

The Date() will insert the current day's date into the column
import_date.

You have to have the column names of both the target table and the s/s.

Now you will be able to compare previous month's/year's data without
using UNION queries. Just be sure you index the import_date column and
other columns that may be used in queries (for speed).

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSm/OFYechKqOuFEgEQLM4QCgy/TEEhQlVJb/LYUm5f6jFzU2uqMAoPg2
D0juHVBcl/xjK6aXHB05xlJw
=ob0Z
-----END PGP SIGNATURE-----