import SQL statements

[klowe@solomon.ie]

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]

Re: import SQL statements

<klowe@solomon. ie> wrote in message
news:1116846873 .638601.281860@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> 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[/color]


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@solomon.ie]

Re: import SQL statements

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]

Re: import SQL statements

<klowe@solomon. ie> wrote in message
news:1116856394 .317577.53610@g 43g2000cwa.goog legroups.com...[color=blue]
> 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?[/color]


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@solomon.ie]

Re: import SQL statements

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]

Re: import SQL statements

<klowe@solomon. ie> wrote in message
news:1116863471 .497342.252540@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> 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?[/color]

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@solomon.ie]

Re: import SQL statements

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@gmail.com]

Re: import SQL statements


klowe@solomon.i e wrote:[color=blue]
> 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[/color]
existing[color=blue]
> ones.
>
> What I was thinking of doing is have my web app create a text file[/color]
with[color=blue]
> Access SQL INSERT/UPDATE statements and get Access to import and
> execute this file.
>
> Is this possible?
>
> Thanks,
> Kevin[/color]

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@solomon.ie]

Re: import SQL statements

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]

Re: import SQL statements

klowe@solomon.i e wrote in
news:1116868183 .733170.76620@g 44g2000cwa.goog legroups.com:
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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?[/color]

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.Reco rdcount <>0 Then
rsDownload.Move First
Do Until rsDownload.EOF
Set rsLocal = [SQL loading the local record with a WHERE
clause on the PK value of the table]
If rsLocal.Recordc ount = 1 Then
For Each fld In rsDownload.Fiel ds
strField = fld.Name
varNewValue = fld.Value
If Nz(varNewValue) <> Nz(rsLocal(strF ield)) Then
rsLocal.Edit
rsLocal(strFiel d) = varNewValue
rsLocal.Update
End If
Next fld
End If
rsLocal.Close
rsDownload.Move Next
Loop
End If

rsLocal.Close
Set rsLocal = Nothing
rsDownload.Clos e
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).
[color=blue]
> 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.[/color]

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@gmail.com]

Re: import SQL statements


klowe@solomon.i e wrote:[color=blue]
> Hi Kevin,
>[/color]
[---][color=blue]
>
> 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?
>[/color]

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@solomon.ie]

Re: import SQL statements

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]

Re: import SQL statements

klowe@solomon.i e wrote in
news:1116949303 .820890.53090@g 14g2000cwa.goog legroups.com:
[color=blue]
> 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?[/color]

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@solomon.ie]

Re: import SQL statements

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]

Re: import SQL statements

<klowe@solomon. ie> wrote in message
news:1117019386 .756506.223460@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> 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[/color]


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