Importing excel spreadsheet with changed data

[gfe]

Hi,

Here's my issue. I have a database that is on 4 standalone computers.
presently, 3 standalones enter data and export the data, via a query that
pulls the data from a date range selected on the main form, that is sent to
an the A: Drive to a floppy disk. on my end i have vba code written to a
command button that pulls the data into the main table "daily info sheet"
from the Arive. now this is done daily and works fine. however, on a
weekly basis, the data needs to be re-imported for the entire week to correct
any changes made during the week. the present system has me going into the
"daily info sheet" table every monday and deleting the week's records and
re-importing. i would like to stop this process of manually deleting the
previous week's data and write some code that looks for changes in the fields
and overwrites the records that have changed.

Here is the Exporting code:

Private Sub Command132_DblClick(Cancel As Integer)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EXPORTQ",
"A:\123db.xls", , "EXPORTQ"
End Sub

the importing code:

Private Sub Command134_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "A:"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "DAILY INFO SHEET"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop
End Sub

thx in advance,
Gordon

 

[Gina Whipp]

gfe,

I suppose if I were to have to do it this way I would add a field to the
table DateModified which would update to Now() everytime the data is changed
in a record. This is providing you are using a form/menu for data input.

Private Sub Form_Dirty(Cancel As Integer)
Me.txtDateModified = Now()
End Sub

Then at weeks end I would compare the DateModified field and the PrimaryKey
and run an update query. I am, of course, assuming that the Primary Key
does not change on the week end import from the daily imports.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm