import SQL statements

[klowe]

Hi, I'm an Access newbie and need to update an Access database from a
web application. I'll need to add new records and also update existing
ones.

What I was thinking of doing is have my web app create a text file with
Access SQL INSERT/UPDATE statements and get Access to import and
execute this file.

Is this possible?

Thanks,
Kevin

[kevindotcar]

kl***@solomon.i e wrote:

Hi Kevin,
[---]
The problem will be to update existing records. I've been searching
this group and seen a post on a pass-through query - this looks
promising i think. I think I can use this to have my web app write
INSERT/UPDATE statements and run them on Access, is this right?

I've had the same situation- and it's definitely not pretty; First,
devise a query that determines the existing records; next, you can
either delete all the "old" records and perform a single "INSERT"
statement or insert "new blank" records and make one "UPDATE" sql
statement... Your call on that one.

HTH-

KevDot

[klowe]

Hi David,

Many thanks for your detailed posting.

This is a silly question, but I'm totally at sea when it comes to MS
technoglogy. Is that code you posted VB? And where would I enter it
into Access?

Thanks,
KEvin

[David W. Fenton]

kl***@solomon.i e wrote in
news:11******** *************@g 14g2000cwa.goog legroups.com:

Many thanks for your detailed posting.

This is a silly question, but I'm totally at sea when it comes to
MS technoglogy. Is that code you posted VB? And where would I
enter it into Access?

Yes, it's VB, but the dialect of it known as VBA, Visual Basic for
Applications. The core DLL for VBA is the same as the core DLL for
VB, but one is not a superset or subset of the other -- they just
overlap a lot.

The easiest place to put the code I wrote is in the Access database,
or in an Access database that has tables linked to the datafile
you're processing. It would be executed from Access.

In the apps where I've done this, I had a dialog form where the user
initiated the writing of the text files with the data from the
website (by calling a CGI script on the website), then ran an FTP
script locally to download the files from the website to the local
PC, then imported the text files into buffer tables in an Access
database (a temporary file that was replaced after each use to avoid
bloat), then linked to the front end the dialog was launched from.
Then the SQL would use the tables in that front end as though all of
them were local to that database.

Hope that makes sense -- ask if it doesn't.

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

[klowe]

Thanks for that. Do I put it into the modules under objects?

I've been looking on the web for a simple "hello world" type
examplejust to get me started but cant find one. Could you recomend a
site/book ?

I'd be interested to the code you used to open and read in a local
file. I think I'd prefeer just to work directly from the file (reading
into an array then looping through the array issuing SQL to Access)
rather than use a linked table - i will have a very small volume of
updates. Ideally, I'd like to include an MD5 checksum within the file,
I assume VBA has MD5 functions?

Many thanks,
KEvin

[Justin Hoffman]

<kl***@solomon. ie> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

Thanks for that. Do I put it into the modules under objects?

I've been looking on the web for a simple "hello world" type
examplejust to get me started but cant find one. Could you recomend a
site/book ?

I'd be interested to the code you used to open and read in a local
file. I think I'd prefeer just to work directly from the file (reading
into an array then looping through the array issuing SQL to Access)
rather than use a linked table - i will have a very small volume of
updates. Ideally, I'd like to include an MD5 checksum within the file,
I assume VBA has MD5 functions?

Many thanks,
KEvin

I did send an e-mail offering to send an import demo. Is your e-mail
address valid?

[klowe]

Hi Justin,

I didnt get it,or I may have deleted it (get so much spam, tend to del
anythig from people I dont know). My address is klowe at.symbol
solomon dot ie

Thanks,

KEvin

[David W. Fenton]

kl***@solomon.i e wrote in
news:11******** **************@ g47g2000cwa.goo glegroups.com:

Thanks for that. Do I put it into the modules under objects?

I've been looking on the web for a simple "hello world" type
examplejust to get me started but cant find one. Could you
recomend a site/book ?
The code goes in a module. You may want to build a UI to call that
code.
I'd be interested to the code you used to open and read in a local
file. I think I'd prefeer just to work directly from the file
(reading into an array then looping through the array issuing SQL
to Access) rather than use a linked table - i will have a very
small volume of updates. Ideally, I'd like to include an MD5
checksum within the file, I assume VBA has MD5 functions?

You can use SQL on linked tables, so why would you want to do it
sequentially? An all-SQL solution is going to be vastly easier to
code.

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