Export Query out
-
I have MS ACCESS table which stored yearly around information. I am wondering whether I can export the table out monthly. Currently I use timer while the end user launch the application. It seem it did not work well. It did not export the data out.
-
Assuming here... wouldn't it be simpler to just use sql server to import the data monthly from that database using its scheduling capabilities?
-
I am thinking about that. I am wondering whether MS ACCESS have that capitality. Otherwise it seem over kill. Import the MS ACCESS table to SQL server and export the table out to excel format.
-
I know you can use the access export wizard. Maybe you can save that as a macro and see how it's done (sorry never had to do that programmatically). Then maybe the timer an be changed to something that works!!
-
DTS is much simplier and more reliable than any tools in Access.
-
I would tend to agree, but since sql server is not needed at the moment and that maybe the access application may be used in a context where sql server is not available, then I'd try to find a solution without it. Unless you can assume that the server will always be available.
-
If you had to use Access, then you will require a table containing either the Date Last Extract or all the dates of extract (and a query the extract the last).
You also require a method to automate the extraction.
To run code automatically create a macro call AutoExec and in it call a function.
The function would perform the following.
Read Last processing date.
If last processing Month <> current Month then
extract to spreadsheet
Update processing date
Endif
-
And the extract to spreadsheet would look like what??
-
DoCmd.TransferSpreadsheet ....
Look this up in help and it will give you all the parameters to use.
HTH, Vic
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com -
Thanx, this is what I was searching for in that solution
. -
When you use DoCmd.TransferSpreadsheet , it will lost the format of the date. Like you want the data format as date , most of the time , it export as different format in excel.
-
You will need to format those cells (or columns) within Excel. I know of no way to export the format. What I have done is to format those cells with VBA from Access AFTER exporting to Excel. This means you will need to use Access VBA to open the Excel file, then find the column you need to format, select the column, then apply the format.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com -
It take time to open the ms file and format the excel . Sometime the end user will confused , too.
-
Post a message letting the user know the computer is still working. That way the user will know that there is a little more time to wait.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply