I am trying to automate data entry into an Access form. I would like
to develop a python script to read values from an Excel spreadsheet
and pass them onto fill the fields in an Access form which would then
be saved and move onto to the next record. Is this achieveable using
python? I am a beginner and any help would be appreciated.
> I am trying to automate data entry into an Access form. I would like > to develop a python script to read values from an Excel spreadsheet > and pass them onto fill the fields in an Access form which would then > be saved and move onto to the next record. Is this achieveable using > python? I am a beginner and any help would be appreciated.
> Cheers > Jimmy
Do you really need to automate entry into the forms, or just get the data into the database? Having played a little with win32com (https://sourceforge.net/projects/pywin32/), I'd say it's definitely possible. Having worked with Access forms, I would try writing directly to the underlying tables first.
Tony Theodore wrote: > Do you really need to automate entry into the forms, or just get the > data into the database? Having played a little with win32com > (https://sourceforge.net/projects/pywin32/), I'd say it's definitely > possible. Having worked with Access forms, I would try writing > directly to the underlying tables first.
Indeed, xlrd to read the data from the excel file and something like mxODBC to put it into Access would be the way I'd go...
> Tony Theodore wrote: >> Do you really need to automate entry into the forms, or just get the >> data into the database? Having played a little with win32com >> (https://sourceforge.net/projects/pywin32/), I'd say it's definitely >> possible. Having worked with Access forms, I would try writing >> directly to the underlying tables first.
> Indeed, xlrd to read the data from the excel file and something like > mxODBC to put it into Access would be the way I'd go...
I was wondering if I could send data from excel cells onto the fields
on access form and click a Save button on the form which would save
the record onto the database. I don't have the rights to do a direct
update of the database
On Jul 2, 9:40 pm, John Machin <sjmac...@lexicon.net> wrote:
> > Tony Theodore wrote:
> >> Do you really need to automate entry into the forms, or just get the
> >> data into the database? Having played a little with win32com
> >> (https://sourceforge.net/projects/pywin32/), I'd say it's definitely
> >> possible. Having worked with Access forms, I would try writing
> >> directly to the underlying tables first.
> > Indeed, xlrd to read the data from the excel file and something like
> > mxODBC to put it into Access would be the way I'd go...
If you're familiar with COM, then yes it can be done.
You must have a whale of a lot of data entry to do! Maybe it might be
worth your while talking to someone who can give you access to the
underlying tables. (I use pyODBC to get to the underlying tables.)
Jim
On 2 July, 08:02, Jimbo <jims2...@gmail.com> wrote:
> I was wondering if I could send data from excel cells onto the fields
> on access form and click a Save button on the form which would save
> the record onto the database. I don't have the rights to do a direct
> update of the database
> On Jul 2, 9:40 pm, John Machin <sjmac...@lexicon.net> wrote:
> > On 2/07/2009 5:48 PM, Chris Withers wrote:
> > > Tony Theodore wrote:
> > >> Do you really need to automate entry into the forms, or just get the
> > >> data into the database? Having played a little with win32com
> > >> (https://sourceforge.net/projects/pywin32/), I'd say it's definitely
> > >> possible. Having worked with Access forms, I would try writing
> > >> directly to the underlying tables first.
> > > Indeed, xlrd to read the data from the excel file and something like
> > > mxODBC to put it into Access would be the way I'd go...
> > pyODBC is another solid ODBC library.- Hide quoted text -
> I was wondering if I could send data from excel cells onto the fields > on access form and click a Save button on the form which would save > the record onto the database. I don't have the rights to do a direct > update of the database
That makes it more difficult. If it's a learning exercise, win32com or either of these:
would be worth investigating, but will probably test your patience.
As an aside, most Access forms I've seen are bound to their tables, so if you have rights to the form, you probably can write to the tables. That said, if the forms do a lot of pre-processing, it may not be a good idea to update tables directly.
If you have the Access app, then you can crack it open and the
credentials for the database will be inside.... or the usual case is
the credentials are in the ODBC connection that the Access app is
bound to. Even if the database is seriously messed up, it will usually
be easier to write code to write straight to it than it will be to
interact with COM. AFAIK you will have to either use very in-depth
tools to figure out the internal names of the buttons, or view the
Access source. No matter which route you will go, you'll be reading
the Access source. Personally I'd use pyodbc as Access uses "Jet",
which rather than throwing your SQL at the database, instead pulls the
entire table over the network*, and does the comparisons locally (can
you say slow as a dead snail?). You will find that where the Access
app took half a second to several seconds to respond to a data entry
action, using pyodbc directly will be measured in miliseconds per
input.
* this is just my experience, you may be using magic jet, which works
properly. I haven't seen it.
>> I was wondering if I could send data from excel cells onto the fields
>> on access form and click a Save button on the form which would save
>> the record onto the database. I don't have the rights to do a direct
>> update of the database
> That makes it more difficult. If it's a learning exercise, win32com or
> either of these:
> would be worth investigating, but will probably test your patience.
> As an aside, most Access forms I've seen are bound to their tables, so
> if you have rights to the form, you probably can write to the tables.
> That said, if the forms do a lot of pre-processing, it may not be a
> good idea to update tables directly.
thanks all for your expert opinions and help...i am shrugging off my
lethargy tonight to learn some Python tonight...I shall post queries
as I run into difficulties...
cheers
Jimbo
On Jul 3, 10:38 am, Daniel Burke <dan.p.bu...@gmail.com> wrote:
> If you have the Access app, then you can crack it open and the
> credentials for the database will be inside.... or the usual case is
> the credentials are in the ODBC connection that the Access app is
> bound to. Even if the database is seriously messed up, it will usually
> be easier to write code to write straight to it than it will be to
> interact with COM. AFAIK you will have to either use very in-depth
> tools to figure out the internal names of the buttons, or view the
> Access source. No matter which route you will go, you'll be reading
> the Access source. Personally I'd use pyodbc as Access uses "Jet",
> which rather than throwing your SQL at the database, instead pulls the
> entire table over the network*, and does the comparisons locally (can
> you say slow as a dead snail?). You will find that where the Access
> app took half a second to several seconds to respond to a data entry
> action, using pyodbc directly will be measured in miliseconds per
> input.
> * this is just my experience, you may be using magic jet, which works
> properly. I haven't seen it.
> regards,
> dan
> On Thu, Jul 2, 2009 at 11:25 PM, Tony Theodore<tony.theod...@gmail.com> wrote:
> >> I was wondering if I could send data from excel cells onto the fields
> >> on access form and click a Save button on the form which would save
> >> the record onto the database. I don't have the rights to do a direct
> >> update of the database
> > That makes it more difficult. If it's a learning exercise, win32com or
> > either of these:
> > would be worth investigating, but will probably test your patience.
> > As an aside, most Access forms I've seen are bound to their tables, so
> > if you have rights to the form, you probably can write to the tables.
> > That said, if the forms do a lot of pre-processing, it may not be a
> > good idea to update tables directly.
|
