Data entry automation using python

[Jimbo]

Hi,

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

[Tony Theodore]

2009/7/2 Jimbo <jims...@gmail.com>:

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.

Regards,

Tony

[Chris Withers]

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...

Chris

[John Machin]

pyODBC is another solid ODBC library.

[Jimbo]

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

[JimM]

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

> > pyODBC is another solid ODBC library.- Hide quoted text -
>
> - Show quoted text -

[Tony Theodore]

2009/7/2 Jimbo <jims...@gmail.com>:

>
> 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:

http://www.tizmoi.net/watsup/intro.html
http://www.brunningonline.net/simon/blog/archives/winGuiAuto.py.html

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.

Regards,

Tony

[Daniel Burke]

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

[Jimbo]

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:
>
> > 2009/7/2 Jimbo <jims2...@gmail.com>:

>
> >> 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:
>
> >http://www.tizmoi.net/watsup/intro.html
> >http://www.brunningonline.net/simon/blog/archives/winGuiAuto.py.html
>
> > 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.
>
> > Regards,
>

> > Tony- Hide quoted text -