Exporting to Excel

[Chad]

Is there a way to export tables to an already established
Excel file but instead of replacing the data in the file
have add it to the data already there?

Thanks
Chad

 

[Ken Snell]

Not by the export wizards. You'd need to use VBA code to open the EXCEL file
and worksheet via Automation and then write the data into the worksheet.

 

[Ken Snell]

Your code is not "Automation" code. Automation is the use of code to
directly interact with another Office program.

What you would need is code similar to the following (air code provided as
an example):

Public Sub ExportToExcel()
Dim lngRow As Long
Dim xlx As Object, xlwb As Object, xlws As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
lngRow = 0
Set xlx = CreateObject("Excel.Application")
Set xlwb = xlx.Workbooks.Open("C:\Filename.xls")
Set xlws = xlwb.Worksheets("SheetName")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TableOrQueryName", dbOpenDynaset)
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
xlws.Range("A11").Offset(lngRow, 0).Value = rst!FieldName
rst.MoveNext
lngRow = lngRow + 1
Loop
End If
xlwb.Save
xlwb.Close False
Set xlwb = Nothing
xlx.Quit
Set xlx = Nothing
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub

 

[Chad]

Would it be easier to import data from another access
database to add to a table?

Example:

Import the fields from tblRequests in one database to be
added to tblRequests in another database.

Thanks again,
Chad

 

[Chad]

Sorry I was unclear earlier. What I am ultimately trying
to do is find an efficient way to back up the data in all
of my tables so that I can retrieve the information later
on. I am not sure the best way to go about this (hence
the reason for jumping around with different ideas) If
you know of a way to help please let me know

Thanks again,
Chad

 

[Ken Snell]

If you want to back up the data, why not just make a copy of the database
file and store it as a backup? Are you really needing to just back up some
of the data?

 

[Chad]

Ken,
Sorry about all this. But here it goes from the
beginning.


The program I am creating is a report generating
software / database. Data from each record a user inputs
is stored in many tables. I want to back up this data
every month and each month append the new data to the
data store from the previous months. After a year has
passed I would simply start another database for backup.
(I am not sure about the best way to go about this)

Due to the large amount of data that will be going into
the system, it would be unpracticle to keep ALL of the
data in the database so after each year I would empty the
current tables (After i have backed them up of course)
and start working on a blank database. (that part I can
do)

One of the fields in one of my tables contains the date
the that information was entered. Periodically the user
will have to go back to previous years and pull data from
the archived database. I want them to be able to specify
a date range to pull the data from (ie 1/1/00 - 2/1/00)
instead of having to pull the entire year. I also don't
want the data they pulled to remain in my current
database once they are done pulling it. ( I am not sure
if it would be better to link tables between Access
databases {if thats what I use}, or to import the data
and then delete once they are finished with it.)

SOrry its so long but this is exactly what I need. I
hope this helps.

Chad

 

[Chad]

Thanks again Ken for all of your help. Regarding your
concerns about why I thought Access would not be able to
handle a lot of information:

I know the database is able to handle this amount of data
but I am trying to stay away fom any noticable decreases
in performance. I don't have too much experience with
large databasesin Access so I don't know when this would
start happening. I know it has to do with a lot of
factors so I just want to be sure to play it safe.

Chad