Update Access table from Perl script via ODBC

[Guest]

I'm trying to update an Access table from a Perl script with DBI and
DBD::ODBC. (There is serious number crunching involved that I already have
programmed in Perl, and I haven't used Basic for anything much in 35 years.)
To my surprise I didn't have much trouble connecting. SELECT works fine too,
but UPDATE gives me the "Operation must use an updateable query" message.
Since this is a simple one-table update, it's the permissions problem, not
the cross tabs problem. As a newbie to Access, I'm unfamiliar with setting
permissions in it -- somewhere in the Windows XP, DSN, Access chain I don't
have write permission. Or maybe it's the Internet Guest account. (This same
query works fine on a MySQL database where I understand the permissions
better.) Help! And words of one syllable please. (The Read Only box is NOT
checked in the DSN.)

 

[Douglas J. Steele]

I don't know anything about web programming with Perl, but with ASP, you
need to ensure that the ID under which ASP is running (typically
IUSR_machinename) has full access (Read, Write, Delete and eXecute) on the
folder where the MDB file is located.

 

[Guest]

I've checked "No" in response to "Was this helpful" but that's not entirely
fair.

It had occurred to me on my own that I was adding an unnecessary layer by
running my Perl script as a CGI script through IIS and that I might have
better permissions running it as a straight Perl script that didn't use an
HTTP server. This has proved to be case -- now successful with UPDATE. Your
message made it seem more likely that I should try this. So thanks for that
encouraging word. (I was using IIS to get scrollable output as an HTML page.
There are too many lines to scroll in a command window. Of course, I can
redirect to a file and look at that.)

BUT your message lacked the words-of-one-syllable instructions I'd need to
set permissions for IUSR_machinename. With what, exactly, do I do this and
where, exactly do I find IUSR_machinename? Though I don't actually need it
for the present, I'd like to know how just in case.

 

[Sylvain Lafontaine]

The syntaxes for MySQL, Access and MS SQL-Server have differences. You
cannot make the assumption that you a permission problem based on the fact
that the same code is working on MySQL.

Without any real information about the structure of the underlying table and
the update query used, it's hard to tell more.

S. L.

 

[Douglas J. Steele]

Recognize that user ids and permissions are part of the operating system,
not part of Access, so I'm afraid this really isn't the most appropriate
group to expect to get those instructions. How you set permissions varies
from operating system to operating system, so I can't really give you
"words-of-one-syllable instructions".

One approach, typically, is to find the folder in question in File Explorer
and right-click on it. You might find an entry for Security in the context
menu that appears, or you might have to select the Properties option and
look for the appropriate tab on the window that appears.

And be aware that IUSR_machinename is a shorthand. You'd substitute the
actual name of the machine in question for machinename.