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

[Justin Hoffman]

<kl***@solomon.ie> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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

Since this will be a web app then you are not using MS Access at all - you
are only using the same file format (mdb) that Access uses. You don't say
what you are using to build the application, but ASP is a common choice for
working with 'Access databases' but whatever you are using, you this
newsgroup may not be the most appropriate.
Anyway, it doesn't sound like an intermediate text file will bring any
benefits. In ASP for example, the code you write generates and executes the
sql statements directly. If you are really only just starting out with
this, my advice would be to start simple. For example create a database
with one table and two or three columns e.g.
tblPerson: PsnID (Autonumber), PsnFirstName, PsnLastName
Start by creating a web form to add new people to the database. It's a
pretty simple start really, but you may find it takes you longer than you
anticipated.

[klowe]

Hi,

The web app is completly seperate to the Access d/b - it will generate
the update file and the user will simply download it to their local PC
which had access on it. Sorry for not making this clear. The web app is
in PHP/MySQL.

I need to add record to Access,which should be easy, but also update
previsually loaded records in access.

So, I figured I could have my web app create a fileso SQL statements
such as:

insert into mytable values ("a", "b", etc)
update mytable set col_a="c" where id=3

(obvisually incorrect Access syntax, but you get the idea)

Then have Access read the file after the user has downloaded it, and
run the SQL in it.

Can this be done?

[Justin Hoffman]

<kl***@solomon.ie> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Hi,

The web app is completly seperate to the Access d/b - it will generate
the update file and the user will simply download it to their local PC
which had access on it. Sorry for not making this clear. The web app is
in PHP/MySQL.

I need to add record to Access,which should be easy, but also update
previsually loaded records in access.

So, I figured I could have my web app create a fileso SQL statements
such as:

insert into mytable values ("a", "b", etc)
update mytable set col_a="c" where id=3

(obvisually incorrect Access syntax, but you get the idea)

Then have Access read the file after the user has downloaded it, and
run the SQL in it.

Can this be done?

OK - I see what you mean. Yes it can be done - this is the sort of thing
Access people do all the time. However creating separate sql statements for
each line would be too ineficient - you should just have the data in the
file.
Since it is your app that is creating the text file you can ensure that it
is of the correct format and this makes things quite easy for you. The
import process could start by creating a linked table to the text file and
then doing something like updating all the rows where you have an existing
id match in the database and then inserting the rest. Try creating a linked
table to a text file - you will then be able to treat the rows as if they
existed in the database.

[klowe]

Hi Justin,

I hear what you say about importing SQL line by line but the
volumeofupates wold be low -less than 50 a week. I think if I could do
this it might be the simplest solution since I know SQL but not Access.

However, I did as you suggest, I made a small text tab delimited file
and imported it to a linked table. Asfor the next step, using that
table to update the main table, I'm at a loss as to what to do next?

[Justin Hoffman]

<kl***@solomon.ie> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi Justin,

I hear what you say about importing SQL line by line but the
volumeofupates wold be low -less than 50 a week. I think if I could do
this it might be the simplest solution since I know SQL but not Access.

However, I did as you suggest, I made a small text tab delimited file
and imported it to a linked table. Asfor the next step, using that
table to update the main table, I'm at a loss as to what to do next?

Well now you can simply say:
INSERT INTO tblMain (A1, A2) SELECT B1, B2 FROM tblLink

So if you know SQL, inserting the contents of one table into another is done
in one line - rather than writing an insert statement for each line.

[klowe]

Thanks again Justin. I've been playing with the linked table and it
seesm that basically, the entire contents of it are replaced with the
contents of the text file each time I update it using the linked table
manager -is this correct?

How can I do updates of existing records in tblMain? Can it be done
without writing something like a VBA routine? The updated records in
the text file will contain an ID field which exists in tblMain.

[kevindotcar]

kl***@solomon.ie wrote:

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

Hi Kevin-

I've been looking at this thread of your and decided to chime in after
some of the things I've read;

Firstly, I import about 450K lines of text into access databases
several times a day from PERL programs, one line at a time and have
no problems (as long as the databases are local and not networked).

If you have a LOT of data (I assume you do) AND you have to network
your Access database- a very valid situation since you're running
a web server, you might want to try this;

1. Create a "dummy" file format in a CSV or TSV file, and set up
a "Link table" entry to this in youe Access database.

2. Write a CGI program (I like Perl- you mught like C# or Python-
whatever) to write this data into your "blank" linked table-
It should be REAL fast and not impact your web server.

You obviously won't beable to index/Key this table, but at least
you'll have it in available to your Access database; I guess after
you load it, you could run an INSERT INTO statement to put it more
permanently into your Access database.

HTH-
kevDot

[klowe]

Hi Kevin,

I'm using PHP and the writing of my text files is no problem. Problem
is I know zero abou Access and as this is a once off thing I want to do
this as easilly /quickly as possible.

I've been able to create the link fileno problem following Justin's
advice and the adding of new records to my main tableis fine (although
I've not tried it yet, but it's asimple SQLstatement).

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?

This app will actually have a small num of records. And it's not
networked, the only connection between web site and Access will be my
client downloading my update file from the site.

Thanks,

Kevin

[David W. Fenton]

kl***@solomon.ie wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:

I'm using PHP and the writing of my text files is no problem.
Problem is I know zero abou Access and as this is a once off thing
I want to do this as easilly /quickly as possible.
I've done exactly what you're describing in the past, including the
PHP part (not me, someone else coding it), though the website db was
MySQL (that was where I concluded that MySQL was a toy database, as
all RI enforcement was in the PHP app, and the app failed to
maintain integrity after an upgrade of the PHP version; the result
was invalid data inserted into the MySQL db (0 in foreign key
fields), and so the update of the Access db failed; but I digress).

Access cannot execute multiple SQL statements. It can only process
one at a time, so you can't just record the SQL that updated the
back end on the website and then have Access run it against the
local file.

Secondly, SQL executed through ODBC against Jet has slightly
different rules than SQL executed native in Access. So, you wouldn't
be able to use the SQL from the website raw -- you'd have to process
it.
I've been able to create the link fileno problem following
Justin's advice and the adding of new records to my main tableis
fine (although I've not tried it yet, but it's asimple
SQLstatement).

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?
The easiest way to do it in Access is with two recordsets, one for
the source data (downloaded from the website) and one for the local
data:

Dim db As DAO.Database
Dim rs as DAO.Recordset
Dim fld As DAO.Field
Dim strField as String
Dim varNewValue As Variant

Set db = CurrentDB()
Set rsDownload = [SQL for the linked downloaded data]
If rsDownload.Recordcount <>0 Then
rsDownload.MoveFirst
Do Until rsDownload.EOF
Set rsLocal = [SQL loading the local record with a WHERE
clause on the PK value of the table]
If rsLocal.Recordcount = 1 Then
For Each fld In rsDownload.Fields
strField = fld.Name
varNewValue = fld.Value
If Nz(varNewValue) <> Nz(rsLocal(strField)) Then
rsLocal.Edit
rsLocal(strField) = varNewValue
rsLocal.Update
End If
Next fld
End If
rsLocal.Close
rsDownload.MoveNext
Loop
End If

rsLocal.Close
Set rsLocal = Nothing
rsDownload.Close
Set rsDownload = Nothing
Set db = Nothing

Now, you have to be careful about your SQL -- you don't want to
update the PK field, for instance, so you have to exclude it from
both recordsets. There may be other fields that you want to exclued,
as well. You also will want to filter your rsDownload recordset to
include only the records that exist in both tables (an inner join is
the easiest way to do it), since that's the only records that are
going to have updated values.

You also need to use a delete flag to handle deletions and never
actually delete records (just hide them), because, otherwise,
deletions from the web data will never propagate to the local Access
db (if the record is not there, it can't be downloaded and then
deleted).
This app will actually have a small num of records. And it's not
networked, the only connection between web site and Access will be
my client downloading my update file from the site.

It's quite doable, but it works best if it's one-way, from website
to Access database. This means the Access database can't be edited
by the user.

Also, you have to take account of the order of operations in order
not to violate referential integrity. That is, for record additions
to child tables, you must make sure that the additions for the
parent are processed first. If you're actually going to delete the
records flagged as deletes, then you have to delete them in the
child table before deleting them from the parent table.

I would not recommend having no RI on the local Access database,
unless there is reliable RI enforcement on the website db. Indeed,
in the case where the PHP upgrade broke the application-enforced RI
of the MySQL db I was working with, it was only Access's RI
enforcement that informed us of the corrupted data.

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

[kevindotcar]

kl***@solomon.ie 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.ie wrote in
news:11*********************@g14g2000cwa.googlegro ups.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.googlegr oups.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.ie wrote in
news:11**********************@g47g2000cwa.googlegr oups.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