Solved: Copy tables to Escel based on last date modified

[austenr]

Hi,

I need to populate an Excel workbook with all tables in my DB modified with a year greater than 2006. Each table should go in it's own worksheet. Can anyone help? Thanks :dunno

[stanl]

Hi,

I need to populate an Excel workbook with all tables in my DB modified with a year greater than 2006. Each table should go in it's own worksheet. Can anyone help? Thanks :dunno

Does the Excel workbook already exist, or is it created as needed, viz. Using SELECT INTO... queries

When you say DB modified, is that a field in the table with a date greater than 2006 or the Access System's last modified date for each table?

Stan

[austenr]

No I need to create a WB. I want to use this to eventually copy over tables for data conversion into another system. The date would be the systems last modified date. Thanks

[stanl]

No I need to create a WB. I want to use this to eventually copy over tables for data conversion into another system. The date would be the systems last modified date. Thanks

For the first part, I would use ADO - see
http://support.microsoft.com/kb/295646

I'm still a little confused over system date - do you mean the date the MDB file was last modified (which you can gte with WMI or the FileSystemObject), because I don't think Access stores the last date data was entered or modified, only the date the table was created/modified.

Depending upon what the other application is you are moving data to, I would open the mdb as an ADO Connection iterate the tables [for that see]
http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun06/hey0608.mspx

then persist each to XML (the ADO recordset.save() method creates a special type of xml file that behaves like a recordset.

Or, you could just SELECT INTO... each table as the first article suggests, the first SELECT Into will create the .xls and a tab with the name you indicated, then subsequent SQL will create additional tabs. Very fast by the way.

Stan

[austenr]

By the last date modified I mean if you open the "Tables" tab in Access the date modified in that tab is what I am after. Will give those articles a read and post back if still confused. Thanks

[stanl]

By the last date modified I mean if you open the "Tables" tab in Access the date modified in that tab is what I am after.

this one?

[omocaig]

you can use tabledefs to get the last modified date:

Debug.Print CurrentDb.TableDefs("someTable").LastUpdated

This is date of the last structural change to the table, it does not update if records are updated, altered, or deleted.

hth,
Giacomo

[austenr]

Thanks OMO. Yes Stan that one.

[omocaig]

just to make sure I'm on the same page with you all. I think Stan was asking if you need the modified date for the database (.mdb file). I provided code for the last modified date for a table in a database.

Maybe I'm the only confused one (wouldn't surprise me if I am) but which one was it? :confused:

Giacomo

[stanl]

and.. if you don't want to mix Access basic with ADO, you can make a call to OpenSchema:



Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsPROC As ADODB.Recordset
Dim fld As ADODB.Field

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4"


Set rs = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, "Table1", Empty))

While Not rs.EOF

For Each fld In rs.Fields
Debug.Print fld.NAME & ": "; fld.Value
Next fld
rs.MoveNext

Wend

rs.close
cnn.close


Stan

[austenr]

Thanks Stan! That will do the trick. :thumb