So, what you really need is a way to synchronize an Excel spreadsheet
with an Access table. I don't think this exists, outside of what you might
write for yourself in code, and then the rules would have to be defined.
It's not a trivial task, but could probably be done.
For example, you'll need to figure out how to determine which record
is the most recent. If changes occur on the palmtop and the Access table,
even if done to different records, the records need to be merged, not
replaced in Access. If, however, you assume that the Access table isn't
changed while you make changes in the Excel doc, then it's easy and you
only need to loop through and update Access.
I'm not being clear here, but basically, one would need to know more about
the way it's used in order to devise a plan. Below is some code I used
recently to compare two similar datasets, looking for differences. In my
case, I dumped differences to a table. What you need isn't far from this.
Danny J. Lesandrini
dl*********@hot mail.com http://amazecreations.com/datafast/
Public Function ComparePatientD ata()
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rstList As DAO.Recordset
Dim rstItem As DAO.Recordset
Dim intField As Integer
Dim fldItem As DAO.Field
Dim strSQL As String
Dim strCode As String
Dim lngNum As Long
Dim FUDate As Date
Dim StartDate As Date
Set dbs = CurrentDb
strSQL = "SELECT * FROM tblAdverseEvent ORDER BY [PatientCode], [AENumber], [StartDate]"
Set rstList = dbs.OpenRecords et(strSQL, dbOpenSnapshot)
Do Until rstList.EOF
strCode = Nz(rstList!Pati entCode, "???")
lngNum = Nz(rstList!AENu mber, 0)
StartDate = Nz(rstList!Star tDate, 0)
'FUDate = Nz(rstList!Foll owUpDate, Date)
strSQL = "SELECT * FROM tblAdverseEvent s_Old WHERE [PatientCode] = '" & strCode & "' AND [AENumber]=" & lngNum & "
AND [StartDate]=#" & StartDate & "#"
Set rstItem = dbs.OpenRecords et(strSQL, dbOpenSnapshot)
If Not rstItem.BOF And Not rstItem.EOF Then
For intField = 1 To rstList.Fields. Count - 1
If Trim(rstList.Fi elds(intField)) <> Trim(rstItem.Fi elds(intField)) Then
strSQL = "INSERT INTO 817Exceptions (PatientCode, Key, TableName, FieldName, NewValue, OldValue) VALUES
('" & strCode & "','" & lngNum & " : " & StartDate & "','tblAdverseE vent','" & _
rstList.Fields( intField).Name & "','" & Replace(rstList .Fields(intFiel d).Value, "'", "''") &
"','" & Replace(rstItem .Fields(intFiel d).Value, "'", "''") & "')"
dbs.Execute strSQL
End If
Next
End If
rstList.MoveNex t
Loop
MsgBox "Done"
exit_Here:
Set rstList = Nothing
Set rstItem = Nothing
Set dbs = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Next
End Function
--
"Chris Naylor" <ne************ **@pobice.com> wrote ...
I'm sure that to most of you this will be an easy answer but to me it
isn't so here goes..
I have a database that I want to export a table from into Excel format
so that I can use it on my palmtop. I don't want all of the data in the
table - just current members details (I allready have a query to show
only current members that is used in a number of other places in the
database).
I can manage to export the data to an excel file and view through excel
but I also want to be able to make aditions/changes to the data while
away from my PC (using the palmtop) and then import the data back to the
main database.
I've seen how to import data into a current table but when I try to
import the data back to the table it won't allow me to do so because it
creates multiple primary keys.
How can I export the information from the table to excel and then back
again after making changes?
Any suggestions welcomed.
--
Chris Naylor
Remove your trousers to reply
http://www.neff.org.uk
http://www.ireland2006eb.org.uk/
"No point in making a molehill out of an elephant!"
