(Msg. 1) Posted: Thu Aug 14, 2008 10:19 am
Post subject: Linked table question Archived from groups: microsoft>public>access>tablesdbdesign (more info?)
I have a split database, BE on a LAN, each user has FE on machine. Some of my
tables need to be periodically updated from Excel spreadsheets. Some need to
be updated often. What is the best way to do that? I understand that now
Excel can no longer be directly linked to Access files? Is it possible to
have "live" tables that automatically update when a spreadsheet is changed?
Or what is the best solution? Thanks a bunch!!!!
(Msg. 2) Posted: Thu Aug 14, 2008 1:10 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I'm not aware of any "triggers" built into Excel that would "push" a new
record (or changes to existing records) over into an Access table.
But if the data is being kept in Excel, I believe you can still "link" to
that data. So why would you need to keep a copy in Access? Is there
another business requirement you didn't mention.
And if you do have a business need to store a copy of the same data in both
Excel and in Access, and are looking for a mechanism that can help you keep
these in sync, consider writing a routine that you include as part of the
start-up for the Access application. While this won't be "real-time" and
won't accommodate a change during normal business hours (unless someone
exits the Access app and restarts it), it would refresh the data each time
the app is launched.
.... not that I'm suggesting that! Again, if the data is being maintained in
Excel, why not just "link" to it?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Golfinray" <Golfinray RemoveThis @discussions.microsoft.com> wrote in message
news:CD18616D-7614-4ED6-9639-50ACA8FD40C5@microsoft.com...
>I have a split database, BE on a LAN, each user has FE on machine. Some of
>my
> tables need to be periodically updated from Excel spreadsheets. Some need
> to
> be updated often. What is the best way to do that? I understand that now
> Excel can no longer be directly linked to Access files? Is it possible to
> have "live" tables that automatically update when a spreadsheet is
> changed?
> Or what is the best solution? Thanks a bunch!!!!
(Msg. 3) Posted: Thu Aug 14, 2008 1:40 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I have the Excel files linked. Can I directly write queries, forms, and
reports off the Excel data? What I did was take that data and put it into
Access tables so that I could normalize it, break it up into the right
tables, and then build my queries, forms, and reports, use code and so
forth. I wouldn't be able to do that directly off the spreadsheets would I?
Thanks!!!
"Jeff Boyce" wrote:
> I'm not aware of any "triggers" built into Excel that would "push" a new
> record (or changes to existing records) over into an Access table.
>
> But if the data is being kept in Excel, I believe you can still "link" to
> that data. So why would you need to keep a copy in Access? Is there
> another business requirement you didn't mention.
>
> And if you do have a business need to store a copy of the same data in both
> Excel and in Access, and are looking for a mechanism that can help you keep
> these in sync, consider writing a routine that you include as part of the
> start-up for the Access application. While this won't be "real-time" and
> won't accommodate a change during normal business hours (unless someone
> exits the Access app and restarts it), it would refresh the data each time
> the app is launched.
>
> .... not that I'm suggesting that! Again, if the data is being maintained in
> Excel, why not just "link" to it?
>
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "Golfinray" <Golfinray.DeleteThis@discussions.microsoft.com> wrote in message
> news:CD18616D-7614-4ED6-9639-50ACA8FD40C5@microsoft.com...
> >I have a split database, BE on a LAN, each user has FE on machine. Some of
> >my
> > tables need to be periodically updated from Excel spreadsheets. Some need
> > to
> > be updated often. What is the best way to do that? I understand that now
> > Excel can no longer be directly linked to Access files? Is it possible to
> > have "live" tables that automatically update when a spreadsheet is
> > changed?
> > Or what is the best solution? Thanks a bunch!!!!
>
>
>
(Msg. 4) Posted: Thu Aug 14, 2008 2:16 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
It depends on what you mean by updated. Do you need to replace all existing
records with the records in Excel or do you need to add new records in Excel
to the existing records in your database.
Steve
"Golfinray" <Golfinray RemoveThis @discussions.microsoft.com> wrote in message
news:CD18616D-7614-4ED6-9639-50ACA8FD40C5@microsoft.com...
>I have a split database, BE on a LAN, each user has FE on machine. Some of
>my
> tables need to be periodically updated from Excel spreadsheets. Some need
> to
> be updated often. What is the best way to do that? I understand that now
> Excel can no longer be directly linked to Access files? Is it possible to
> have "live" tables that automatically update when a spreadsheet is
> changed?
> Or what is the best solution? Thanks a bunch!!!!
(Msg. 5) Posted: Thu Aug 14, 2008 2:16 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
If a new record is added to the spreadsheet, I need to add just that record
to the table. All other existing table data would stay the same. No records
might be changed one month, some months there might be 15 or 20 records
changed on 3 different spreadsheets and that would affect 7 or 8 different
tables in my database.
"Steve" wrote:
> It depends on what you mean by updated. Do you need to replace all existing
> records with the records in Excel or do you need to add new records in Excel
> to the existing records in your database.
>
> Steve
>
>
> "Golfinray" <Golfinray RemoveThis @discussions.microsoft.com> wrote in message
> news:CD18616D-7614-4ED6-9639-50ACA8FD40C5@microsoft.com...
> >I have a split database, BE on a LAN, each user has FE on machine. Some of
> >my
> > tables need to be periodically updated from Excel spreadsheets. Some need
> > to
> > be updated often. What is the best way to do that? I understand that now
> > Excel can no longer be directly linked to Access files? Is it possible to
> > have "live" tables that automatically update when a spreadsheet is
> > changed?
> > Or what is the best solution? Thanks a bunch!!!!
>
>
>
(Msg. 6) Posted: Thu Aug 14, 2008 2:16 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Everyone handles this scenario differently... but the way I've done it in the
past is:
First I create a form to act as my control panel... this panel has button to
access action queries, reports and graphs... so I can monitor everything
that's going on.
My process then is:
I've created temp tables and import the data to the temp tables.
Check for errors in the data.
Check for any additional data by comparing the temp table to the production
tables.
Append additional data.
Empty temp tables.
Cathy
"Golfinray" wrote:
> If a new record is added to the spreadsheet, I need to add just that record
> to the table. All other existing table data would stay the same. No records
> might be changed one month, some months there might be 15 or 20 records
> changed on 3 different spreadsheets and that would affect 7 or 8 different
> tables in my database.
>
> "Steve" wrote:
>
> > It depends on what you mean by updated. Do you need to replace all existing
> > records with the records in Excel or do you need to add new records in Excel
> > to the existing records in your database.
> >
> > Steve
> >
> >
> > "Golfinray" <Golfinray.RemoveThis@discussions.microsoft.com> wrote in message
> > news:CD18616D-7614-4ED6-9639-50ACA8FD40C5@microsoft.com...
> > >I have a split database, BE on a LAN, each user has FE on machine. Some of
> > >my
> > > tables need to be periodically updated from Excel spreadsheets. Some need
> > > to
> > > be updated often. What is the best way to do that? I understand that now
> > > Excel can no longer be directly linked to Access files? Is it possible to
> > > have "live" tables that automatically update when a spreadsheet is
> > > changed?
> > > Or what is the best solution? Thanks a bunch!!!!
> >
> >
> >
(Msg. 7) Posted: Thu Aug 14, 2008 2:36 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Once the data looks like a 'table' (linked or otherwise), you can create
queries against it, create forms against ... (create forms and reports
against queries you create against the tables ... more flexibility), write
code referring to the "table" and its "fields", etc.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Golfinray" <Golfinray.TakeThisOut@discussions.microsoft.com> wrote in message
news:5FBF060A-584B-44FA-85C6-50D8EC466BFA@microsoft.com...
>I have the Excel files linked. Can I directly write queries, forms, and
> reports off the Excel data? What I did was take that data and put it into
> Access tables so that I could normalize it, break it up into the right
> tables, and then build my queries, forms, and reports, use code and so
> forth. I wouldn't be able to do that directly off the spreadsheets would
> I?
> Thanks!!!
>
> "Jeff Boyce" wrote:
>
>> I'm not aware of any "triggers" built into Excel that would "push" a new
>> record (or changes to existing records) over into an Access table.
>>
>> But if the data is being kept in Excel, I believe you can still "link" to
>> that data. So why would you need to keep a copy in Access? Is there
>> another business requirement you didn't mention.
>>
>> And if you do have a business need to store a copy of the same data in
>> both
>> Excel and in Access, and are looking for a mechanism that can help you
>> keep
>> these in sync, consider writing a routine that you include as part of the
>> start-up for the Access application. While this won't be "real-time" and
>> won't accommodate a change during normal business hours (unless someone
>> exits the Access app and restarts it), it would refresh the data each
>> time
>> the app is launched.
>>
>> .... not that I'm suggesting that! Again, if the data is being
>> maintained in
>> Excel, why not just "link" to it?
>>
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "Golfinray" <Golfinray.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:CD18616D-7614-4ED6-9639-50ACA8FD40C5@microsoft.com...
>> >I have a split database, BE on a LAN, each user has FE on machine. Some
>> >of
>> >my
>> > tables need to be periodically updated from Excel spreadsheets. Some
>> > need
>> > to
>> > be updated often. What is the best way to do that? I understand that
>> > now
>> > Excel can no longer be directly linked to Access files? Is it possible
>> > to
>> > have "live" tables that automatically update when a spreadsheet is
>> > changed?
>> > Or what is the best solution? Thanks a bunch!!!!
>>
>>
>>
(Msg. 8) Posted: Thu Aug 14, 2008 2:48 pm
Post subject: Re: Linked table question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Thu, 14 Aug 2008 10:19:00 -0700, Golfinray
<Golfinray RemoveThis @discussions.microsoft.com> wrote:
>I have a split database, BE on a LAN, each user has FE on machine. Some of my
>tables need to be periodically updated from Excel spreadsheets. Some need to
>be updated often. What is the best way to do that? I understand that now
>Excel can no longer be directly linked to Access files? Is it possible to
>have "live" tables that automatically update when a spreadsheet is changed?
>Or what is the best solution? Thanks a bunch!!!!
Just to clarify a possible misconception... what's been lost is the ability to
*UPDATE* a linked Excel spreadsheet from Access. You can still use File... Get
External Data... Link to create a link to a spreadsheet, which will work (in
most respects) as if it were an Access table; you just can't update the data.
You also cannot Index the linked table, or enforce referential integrity
between it and any other table - so this may not meet your needs.
--
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other