export from vb 6.0 to excel with a timer

i am trying to set an archive of my database. i know that i have to include the timer so that every say 30days ill have all the data in the MsAcess will go into excel and save the folder in a folder in the whole system automatically.

is there a way i can do that?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

perhaps you could assign a program to your start up routine that checks to see if its been more than 30 days since you last did your back up (i.e. you store a date value) that way the program doesn't need to be in memory all of the time. As long as you are rebooting your computer at least once a month this would work fine.

If you need a code example let me know. . .
Why not write a program to do this and just schedule it on windows scheduler?
grimaenriqueznaomiAuthor Commented:
10x for your comment, this system has to run 24/7 since it is a telephone system that records all the data in the database via a pabx.....i would appreciate some examples though since i am pretty new to vb...
thanking you for your kind co-operation
regards Naomi
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dont write a program that consumes memory and CPU as  aflat362  says just schedule it by windows scheduler.
Running the program when starting up I think is not a good solution. Windows does not run for 30 days without rebooting:) So every time you restart the comp. Your program runs.
Hi grimaenriqueznaomi

I join my voice to those that recommend the Task Scheduler that comes with Windows

grimaenriqueznaomiAuthor Commented:
okie sure, has anyone got a clue how to do it since im new to vb 6.0.....i would appreciate some examples re task scheduler :) i thank you all for your nice co-operation
Here's why I suggested Windows Scheduler (and why iozturk, Dabas back me up)

1) why put all the effort into coding / debugging a timer when its built in to windows already?

2) You don't need to have this functionality built in to your other program that runs 24 / 7.  That's the beauty of databases.  Multiple programs can access them simultaneously unless you choose to lock the database by one app.

3) Resource Hog?  Depending on how efficient your timer is it could be a big resource hog on your system - cpu / memory

4) Code instability.  You said you are a novice programmer.  Timers aren't cut and dry.  It is an advanced programming technique and will be prone to difficulties.

This is a two step process:

1) Writing a program that will do your archiving.

2) Setting up Task scheduler to run 1) once a month.

You do not need VB for 2). It is a Windows utility.

Please let us know which version of Windows you are using, so that we can tell you how to set it up

grimaenriqueznaomiAuthor Commented:
i use windows 98 but this program will be run on xp too! what shall i do?
i thank you once again guys :)
No problem if you can sit on XP computer.
Start->programs->Accessories->System tools->Scheduled Tasks
When you start there is a easy to use GUI. Even first users cannot make mistake:)

I did not use 98 much but I know it has a scheduler. Most porbably it was in my computer or in mycomputer tool bars. I dont remember if you should choose to install it when installation.
grimaenriqueznaomiAuthor Commented:
yeah okie, i think i figured how to do the scheduler, but im sure to run the program you have to find a way in coding (vb)to tell it to put the data in access in excel and to use different sheets for every month....is there a way to do that?
have u got an example if so please? regards Naomi
Sure, add a sheet in every time of your program run. Since it runs one per mounth for each mounth you will have unique sheet.

I think you have also problem with exporting data from Access to Excel.
Look at the code below there may be typos in it and reference Microsoft Excel Object library.

Dim strcnn As String
Dim cnn1 As ADODB.Connection
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
strcnn = "data source=argon_web1; user id=proje; password=rop"  'dont forget to change this

Set cnn1 = New ADODB.Connection
cnn1.Open strcnn

sql1 = "select * from sinif_ders order by 1,2" ''dont forget to change this

Set rs = New ADODB.Recordset
rs.Open sql1, cnn1, , , adCmdText

xlApp.Visible = True
do while not rs.EOF    
     If ders_kodu = 0 Then
        xlSheet.cells(i, j).Value = ""
        xlSheet.cells(i, j).Value = rs(0)
     End If

set rs=nothing
set cnn1=nothing
An other alternative, which would require no programming at all is to follow these steps (assuming your Access is 2000 or later.)
Open up a new workbook in Excel, then click on Data, Get External Data, New Database query.
You are prompted for a new data source. Choose MS Access Database, and click OK.
Select your .mdb and then the table within that mdb, and the columns you want to import.
Follow the prompts of the wizard and Return the data to Excel.
Save the workbook twice:
Once to save the current months information.
Second time as a template to call from task scheduler.\
All you need now is to write a macro that will refresh the data in the spreadsheet and save it under the new months name.
(Recording the macro will do most of the coding for you)


grimaenriqueznaomiAuthor Commented:
Dear iozturk

I did something like that before, but i need something to trigger the deed....i want this excel operation to be done automatically.  i know there is the scheduler, but i think you need an  .exe to select task right?

thanx for your good support!:)

Private Sub cmdExportO_Click()
Dim rs As ADODB.Recordset
Dim con As ADODB.connection
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim sql As String
Dim FreeNumber As Integer
Dim Choice As String

'Choice = "data source = Telephone;user id = password"

   Set con = New ADODB.connection
   con.Open "provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & AppPath & "telephone.mdb"

sql = "SELECT * FROM telephoneO" & _
      " WHERE index   "
   FreeNumber = 0
   Set rs = New ADODB.Recordset
   rs.Open sql, con, adOpenDynamic, adLockPessimistic
   Set xl = CreateObject("Excel.Application")

FreeNumber = FreeFile

xl.Visible = True
If Not rs.EOF Then
  Open "C:\results1.xls" For Output As #FreeNumber
  Print #FreeNumber, "CallType" & vbTab & "Date" & vbTab & "Time" & vbTab & "Duration" & vbTab & "ExtCode" & vbTab & "DialoutNumber" & vbTab & "TelephoneNumber" & vbTab & "Pulses" & vbTab & "TCode" & vbTab & "EOR"
 With rs
  If Not rs.EOF Then
  While Not rs.EOF
 Print #FreeNumber, rs("OCallType") & vbTab & rs("ODate") & vbTab & rs("OTime") & vbTab & rs("ODuration") & vbTab & rs("OExtCode") & vbTab & rs("ODialoutNumber") & vbTab & rs("OTelephoneNumber") & vbTab & rs("OPulses") & vbTab & rs("OTCode") & vbTab & rs("OEOR")
 Row = Row + 1
 Close #FreeNumber
 xl.Workbooks.Open "C:\results1.xls"
End If
    Set ws = Nothing
    Set wb = Nothing
    Set xl = Nothing
    Set rs = Nothing
    Set con = Nothing
End With
End If
End Sub
grimaenriqueznaomiAuthor Commented:
Dear Dabas,

i also thank you for your kind reply...but unfortunately i cannot find office 2000 to install the new database query!
is there another way to go around it?
thanx and regards
Dear Naomi,
Arent you using VB? Make it exe and schedule the exe.
If you are not using VB the you are in somehow trouble. Try making it in VBS (VB script) and schedule VBS. Here is another code sample for vbs ofcourse you should change it to fit you. Save it in a text file and make its extention vbs. (i.e sample.vbs)

Dim objXL
Dim rssorgu
Set objXL = WScript.CreateObject("Excel.Application")

Set cnn1 = CreateObject("ADODB.Connection")
strcnn = "data source=TARKOP; user id=HELPDESK; password=ORTEGA"
cnn1.Open strcnn

sql = "select substr(x.kurumno,4) KURUMNO, x.kurumadi, " & _
         "substr(y.kurumno,2,2) ustkurum, y.kurumadi USTKURUMADI, " & _
         "z.iladi, t.ilceadi, z.telkodu, " & _  
         "x.telno1, x.telno2, x.faxno, x.modem " & _
      "from kurumlar x, kurumlar y, iller z, ilceler t " & _
      "where x.ustkurum = y.kurumno and x.ilkodu=z.ilkodu and " & _
          "x.ilkodu=t.ilkodu and x.ilcekodu=t.ilcekodu"

set rssorgu = CreateObject("ADODB.Recordset")
rssorgu.Open sql, cnn1, 0, 1, &H0001

objXL.Visible = TRUE


objXL.Columns(1).ColumnWidth = 5
objXL.Columns(2).ColumnWidth = 20
objXL.Columns(3).ColumnWidth = 5
objXL.Columns(4).ColumnWidth = 20
objXL.Columns(5).ColumnWidth = 20
objXL.Columns(6).ColumnWidth = 20
objXL.Columns(7).ColumnWidth = 4
objXL.Columns(8).ColumnWidth = 8
objXL.Columns(9).ColumnWidth = 8
objXL.Columns(10).ColumnWidth = 8
objXL.Columns(11).ColumnWidth = 8

objXL.Cells(1, 1).Value = "KURUMNO"
objXL.Cells(1, 2).Value = "KURUMADI"
objXL.Cells(1, 3).Value = "USTKURUM"
objXL.Cells(1, 4).Value = "USTKURUMADI"
objXL.Cells(1, 5).Value = "ÝL"
objXL.Cells(1, 6).Value = "ÝLÇE"
objXL.Cells(1, 7).Value = "TELEFONKODU"
objXL.Cells(1, 8).Value = "TEL1"
objXL.Cells(1, 9).Value = "TEL2"
objXL.Cells(1, 10).Value = "FAX"
objXL.Cells(1, 11).Value = "MODEM"

do while not rssorgu.Eof
   objXL.Cells(i, 1).Value = rssorgu("KURUMNO")
   objXL.Cells(i, 2).Value = rssorgu("KURUMADI")  
   objXL.Cells(i, 3).Value = rssorgu("USTKURUM")
   objXL.Cells(i, 4).Value = rssorgu("USTKURUMADI")      
   objXL.Cells(i, 5).Value = rssorgu("ILADI")  
   objXL.Cells(i, 6).Value = rssorgu("ILCEADI")
   objXL.Cells(i, 7).Value = rssorgu("TELKODU")
   objXL.Cells(i, 8).Value = rssorgu("TELNO1")  
   objXL.Cells(i, 9).Value = rssorgu("TELNO2")  
   objXL.Cells(i, 10).Value = rssorgu("FAXNO")  
   objXL.Cells(i, 11).Value = rssorgu("MODEM")  

   i = i +1
set rssorgu = nothing
set cnn1 = nothing


grimaenriqueznaomiAuthor Commented:
yeah i am using visual baisc 6.0....but if i run this program, i run the whole system....i want it that when the scheduler indicates the task, it will automatically put the data in excel....i am sure that there has to be some kind of coding to trigger this act....and to change it to exe i won't change just the excel, i got to do the whole program....not to forget this will be on for 24/7,there has to be some coding saying that if the scheduler triggers the act every 31days it will have to export the stuff to excel....there is where i am stuck as such....am sorry for asking lots of questions but ....ive only been practicing vb for the past 2wks so i need extra help and i don't know any other language...thanking you for your kind help
Hi Naomi,

1. Delete forms from your application (Dont forget to make a copy of your code.)
2. Add a module to your app, add a procedure named Main in your module. Write your code in Main proc.
3. From project properties select start object as Sub Main
4. Make you application exe with VB6.
6. Schedule yoour exe file.

Now Scheduler triggers your application to start running. And when application starts it invokes the main procedure and your main proc. does what you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
grimaenriqueznaomiAuthor Commented:
i thank you all for such a support
wish you all a great day
regards Naomi
i think u can do this in easy way put a value in registry and also that value in a file now. initilised it to0 when u create  insert the reacord in database now use  now in every day u incrase the value with 1. if it comes to 30 then  transfer it to excel
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.