By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Quick Question

P: n/a
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!"
Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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*********@hotmail.com
http://amazecreations.com/datafast/

Public Function ComparePatientData()
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.OpenRecordset(strSQL, dbOpenSnapshot)

Do Until rstList.EOF
strCode = Nz(rstList!PatientCode, "???")
lngNum = Nz(rstList!AENumber, 0)
StartDate = Nz(rstList!StartDate, 0)
'FUDate = Nz(rstList!FollowUpDate, Date)

strSQL = "SELECT * FROM tblAdverseEvents_Old WHERE [PatientCode] = '" & strCode & "' AND [AENumber]=" & lngNum & "
AND [StartDate]=#" & StartDate & "#"
Set rstItem = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

If Not rstItem.BOF And Not rstItem.EOF Then
For intField = 1 To rstList.Fields.Count - 1
If Trim(rstList.Fields(intField)) <> Trim(rstItem.Fields(intField)) Then
strSQL = "INSERT INTO 817Exceptions (PatientCode, Key, TableName, FieldName, NewValue, OldValue) VALUES
('" & strCode & "','" & lngNum & " : " & StartDate & "','tblAdverseEvent','" & _
rstList.Fields(intField).Name & "','" & Replace(rstList.Fields(intField).Value, "'", "''") &
"','" & Replace(rstItem.Fields(intField).Value, "'", "''") & "')"
dbs.Execute strSQL
End If
Next
End If
rstList.MoveNext
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!"

Nov 13 '05 #2

P: n/a
In article <RP********************@comcast.com>,
(dl*********@hotmail.com) muttered something along the lines of ...
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.

<snip code>

Right, bit more explanation then...

The database is only used by me to keep track of Cubs details and awards
etc. This lives on my home PC. I want a way to take a particular table
from the database (Cub Details) and view/edit on my PDA. No changes will
be made to database on PC while changes are made/updated on PDA.

I'm not very good when it comes to coding - most of my database building
is pretty basic.

--
Chris Naylor
Remove your trousers to reply
http://www.neff.org.uk
http://www.ireland2006eb.org.uk/

An optimist is someone who thinks the future is uncertain.
Nov 13 '05 #3

P: n/a
Add a field to your table called "ExportedToPDA". In your export procedure
set this field to True for all the records in your export. In your import
procedure, first delete all the records on your PC where ExportToPDA is
marked True. Then import (append) the records on your PDA.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.

"Chris Naylor" <ne**************@pobice.com> wrote in message
news:MP************************@news.individual.ne t...
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!"

Nov 13 '05 #4

P: n/a
If the ID field is an autonumber, you can't delete the record. If the
record exists, and needs to be updated, it will require an Update
statement.

It would be difficult to explain all the steps, but if you could post
the fields in your table, that would be a start. You'll probably need
to write some code, but most of the work can be done with queries.

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

"PC Datasheet" <no****@nospam.spam> wrote ...
Add a field to your table called "ExportedToPDA". In your export procedure set this field to True for all the records
in your export. In your import procedure, first delete all the records on your PC where ExportToPDA is marked True.
Then import (append) the records on your PDA.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built the Titanic.

"Chris Naylor" <ne**************@pobice.com> wrote in message news:MP************************@news.individual.ne t...
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!"


Nov 13 '05 #5

P: n/a
Danny,

Why can't you delete a record that has a primary key that is autonumber?
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:RI********************@comcast.com...
If the ID field is an autonumber, you can't delete the record. If the
record exists, and needs to be updated, it will require an Update
statement.

It would be difficult to explain all the steps, but if you could post
the fields in your table, that would be a start. You'll probably need
to write some code, but most of the work can be done with queries.

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

"PC Datasheet" <no****@nospam.spam> wrote ...
Add a field to your table called "ExportedToPDA". In your export
procedure set this field to True for all the records in your export. In
your import procedure, first delete all the records on your PC where
ExportToPDA is marked True. Then import (append) the records on your PDA.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals
built the Titanic.

"Chris Naylor" <ne**************@pobice.com> wrote in message
news:MP************************@news.individual.ne t...
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!"



Nov 13 '05 #6

P: n/a
In article <H0**************@newsread3.news.atl.earthlink.net >,
(no****@nospam.spam) muttered something along the lines of ...
Danny,

Why can't you delete a record that has a primary key that is autonumber?

It won't let me delete records because they are linked to other records
in other tables.

The table in question has rather a lot of field names, I don't actually
need all of them to be exported.

The fields I do need to export and change are:

FirstName, LastName, Guardian, PhoneNumber, Six and a load more fields
that contain either a y/n answer or a date. In all there are about 50
that I need to export.

Would it be easier for me to post a copy of the DB somewhere (minus
current data for obvious reasons)?

I'm beginning to think that it might just be a better idea to export the
files I need and then update the database manually as and when I need
to, but if I can automate it then it saves time - if it involves writing
code then I haven't really got that much of and idea on coding.
Cheers

--
Chris Naylor
Remove your trousers to reply
http://www.neff.org.uk
http://www.ireland2006eb.org.uk/

Men do not like to admit to even momentary imperfection. My husband
forgot the code to turn off the alarm. When the police came, he wouldn't
admit he'd forgotten the code.....he turned himself in. - Rita Rudner
Nov 13 '05 #7

P: n/a
"Chris Naylor" <ne**************@pobice.com> wrote in message
news:MP************************@news.individual.ne t...
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?

Is the data in the Access table changing whilst your changing it in Excel?
If not then an update query might suffice.

Regards,
Keith.
www.keithwilby.com
Nov 13 '05 #8

P: n/a
You have Referential Integrity enforced but you don't have Cascade Delete.
You could go to yout tables and check Cascade Delete and then you would be
able to delete the records. However you are also going to delete the records
in the cascade so you got to make provisions to preserve those records and
replace them at the time you do your import.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.

"Chris Naylor" <ne**************@pobice.com> wrote in message
news:MP************************@news.individual.ne t...
In article <H0**************@newsread3.news.atl.earthlink.net >,
(no****@nospam.spam) muttered something along the lines of ...
Danny,

Why can't you delete a record that has a primary key that is autonumber?

It won't let me delete records because they are linked to other records
in other tables.

The table in question has rather a lot of field names, I don't actually
need all of them to be exported.

The fields I do need to export and change are:

FirstName, LastName, Guardian, PhoneNumber, Six and a load more fields
that contain either a y/n answer or a date. In all there are about 50
that I need to export.

Would it be easier for me to post a copy of the DB somewhere (minus
current data for obvious reasons)?

I'm beginning to think that it might just be a better idea to export the
files I need and then update the database manually as and when I need
to, but if I can automate it then it saves time - if it involves writing
code then I haven't really got that much of and idea on coding.
Cheers

--
Chris Naylor
Remove your trousers to reply
http://www.neff.org.uk
http://www.ireland2006eb.org.uk/

Men do not like to admit to even momentary imperfection. My husband
forgot the code to turn off the alarm. When the police came, he wouldn't
admit he'd forgotten the code.....he turned himself in. - Rita Rudner

Nov 13 '05 #9

P: n/a
If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.


These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and job hunting.

You only care about making money, and you act as if the groups are your private hunting ground.
So why would ANYBODY ever trust a person like you and hire you?
************************************************** ******

Explanation and more on this answer to Steve:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R
Nov 13 '05 #10

P: n/a
"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in
news:43********************@dreader2.news.tiscali. nl:
Explanation and more on this answer to Steve:
http://home.tiscali.nl/arracom/stopsteve.html


<PLONK>

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

P: n/a
You can delete it, but then you'll have to do an insert of a record with an autonumber. While this works, it can
corrupt the database by forcing the seed back to a number one greater than the one you inserted. (I believe this is a
bug, but either way, it's a bad practice.)

Now, if the user doesn't care what the AutoNumber value is, then fine ... delete and reinsert, but if the number is
referenced elsewhere, do an update.

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

"PC Datasheet" <no****@nospam.spam> wrote ...
Danny,

Why can't you delete a record that has a primary key that is autonumber?
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built the Titanic.

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message news:RI********************@comcast.com...
If the ID field is an autonumber, you can't delete the record. If the
record exists, and needs to be updated, it will require an Update
statement.

It would be difficult to explain all the steps, but if you could post
the fields in your table, that would be a start. You'll probably need
to write some code, but most of the work can be done with queries.

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

"PC Datasheet" <no****@nospam.spam> wrote ...
Add a field to your table called "ExportedToPDA". In your export procedure set this field to True for all the
records in your export. In your import procedure, first delete all the records on your PC where ExportToPDA is
marked True. Then import (append) the records on your PDA.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built the Titanic.

"Chris Naylor" <ne**************@pobice.com> wrote in message news:MP************************@news.individual.ne t...
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!"



Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.