Quick Question

[Chris Naylor]

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!"

[Danny J. Lesandrini]

Re: Quick Question

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
dlesandrini@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" <neffyourtrouse rs@pobice.com> wrote ...[color=blue]
> 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!"[/color]

[Chris Naylor]

Re: Quick Question

In article <RPSdnYy1lZfSvc HeRVn-uQ@comcast.com> ,
(dlesandrini@ho tmail.com) muttered something along the lines of ...[color=blue]
> 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.
>[/color]
<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.

[PC Datasheet]

Re: Quick Question

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
resource@pcdata sheet.com

https://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" <neffyourtrouse rs@pobice.com> wrote in message
news:MPG.1dc624 8af75823a59896d 3@news.individu al.net...[color=blue]
> 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!"[/color]

[Danny J. Lesandrini]

Re: Quick Question

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
dlesandrini@hot mail.com

http://amazecreations.com/datafast/

"PC Datasheet" <nospam@nospam. spam> wrote ...[color=blue]
> 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
> resource@pcdata sheet.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" <neffyourtrouse rs@pobice.com> wrote in message news:MPG.1dc624 8af75823a59896d 3@news.individu al.net...[color=green]
>> 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!"[/color]
>
>[/color]

[PC Datasheet]

Re: Quick Question

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
resource@pcdata sheet.com

https://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" <dlesandrini@ho tmail.com> wrote in message
news:RIidnX3WbP vd2sHeRVn-hg@comcast.com. ..[color=blue]
> 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
> dlesandrini@hot mail.com
> http://amazecreations.com/datafast/
>
>
>
> "PC Datasheet" <nospam@nospam. spam> wrote ...[color=green]
>> 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
>> resource@pcdata sheet.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" <neffyourtrouse rs@pobice.com> wrote in message
>> news:MPG.1dc624 8af75823a59896d 3@news.individu al.net...[color=darkred]
>>> 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!"[/color]
>>
>>[/color]
>
>[/color]

[Chris Naylor]

Re: Quick Question

In article <H0Y6f.595$AS6. 90@newsread3.ne ws.atl.earthlin k.net>,
(nospam@nospam. spam) muttered something along the lines of ...[color=blue]
> Danny,
>
> Why can't you delete a record that has a primary key that is autonumber?
>
>
>[/color]
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

[Keith]

Re: Quick Question

"Chris Naylor" <neffyourtrouse rs@pobice.com> wrote in message
news:MPG.1dc624 8af75823a59896d 3@news.individu al.net...[color=blue]
> 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?
>[/color]
Is the data in the Access table changing whilst your changing it in Excel?
If not then an update query might suffice.

Regards,
Keith.

https://www.keithwilby.com

[PC Datasheet]

Re: Quick Question

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
resource@pcdata sheet.com

https://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" <neffyourtrouse rs@pobice.com> wrote in message
news:MPG.1dc6b6 774d8ec18e9896d 6@news.individu al.net...[color=blue]
> In article <H0Y6f.595$AS6. 90@newsread3.ne ws.atl.earthlin k.net>,
> (nospam@nospam. spam) muttered something along the lines of ...[color=green]
>> Danny,
>>
>> Why can't you delete a record that has a primary key that is autonumber?
>>
>>
>>[/color]
> 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[/color]

[StopThisAdvertising]

Re: Quick Question

[color=blue]
> 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.[/color]

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

[David W. Fenton]

Re: Quick Question

"StopThisAdvert ising" <StopThisAdvert ising@DataShit> wrote in
news:435ccfd2$0 $713$5fc3050@dr eader2.news.tis cali.nl:
[color=blue]
> Explanation and more on this answer to Steve:
> http://home.tiscali.nl/arracom/stopsteve.html[/color]

<PLONK>

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Danny J. Lesandrini]

Re: Quick Question

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
dlesandrini@hot mail.com

http://amazecreations.com/datafast/

"PC Datasheet" <nospam@nospam. spam> wrote ...[color=blue]
> 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
> resource@pcdata sheet.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" <dlesandrini@ho tmail.com> wrote in message news:RIidnX3WbP vd2sHeRVn-hg@comcast.com. ..[color=green]
>> 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
>> dlesandrini@hot mail.com
>> http://amazecreations.com/datafast/
>>
>>
>>
>> "PC Datasheet" <nospam@nospam. spam> wrote ...[color=darkred]
>>> 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
>>> resource@pcdata sheet.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" <neffyourtrouse rs@pobice.com> wrote in message news:MPG.1dc624 8af75823a59896d 3@news.individu al.net...
>>>> 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!"
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]