If one selects a query one can View the SQL and edit it by hand - or
cut & paste from one's favorite editor.
I tried putting several lines of SQL in there but Access swarks about
there being stuff after the first ";".

So how can I get Acess to execute a text file containing multiple
lines of SQL?

I have something to do that is very repetitive - just a column name
changes in each line and it would be easiest for me just to use emacs
or something like to to make a file with many very similar lines of
SQL in it, then run the file of SQL. How can one do that? It's me
that will be running it interactively, so any error reporting need not
be friendly or graceful - I would just keep editing it until it ran
successfully.

The manuals I have do not tell me how to do this. I found on other
bulletin boards references to free programs that would do it, but
either the links were out of date or you had to install them as
extensions into access which I was a bit dubious about.

I have Access 2003 - but I might be able to upgrade to Access 2007 if
necessary.

Any help appreciated.

Hi,

Could display a sample of what the sql in the text file looks like? It
is unclear what you are describing. I am picturing something like

select fld1,
fld2,
fld3,
fld4,
fld5,
...,
fld100
from tbl1 t1 join tbl2 t2 on t1.ID = t2.ID
where some condition exists.

If it is something like this then the easiest approach would be to read
the textfile into the mdb and then create a sql string from the text you
just read in. Here is how to read from a textfile in VBA

Sub ReadTxtFile()
Dim strTxt
Open "C:yourTxtFile.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strTxt
Debug.Print strTxt
Loop
Close #1
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Mar 25, 2:03*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:
Rich,
Thanks. Actually what I meant was to have Access run it without me
having to write any Visual Basic. Something analagous to import a
file into a table, as if the File -> Open menu item when it opened a
file with extension ".sql" executed the sql commands therein one-by-
one. Other databases do this and maintainers of those dbs use such a
feature a lot to do maintenance, data fix up after changes to the
schema of the db, fixes to data caused by programming errors, etc.
The SQL statements I want to run are several hundred like the
following where the statments will insert into the same columns in
tblAward, but be selecting from different columns in tblSurveyResults.




INSERT INTO tblAward ( ID, Term, AwardType, AwardName, AwardP1,
AwardStatus )
SELECT [tblSurveyResults.ID, 3100 AS Term, "CALI" AS AwardType, "CALI"
AS AwardName, [tblSurveyResults].[
qn25response] AS "AwardP1", "Claimed" AS AwardStatus
FROM [tblSurveyResults]
WHERE ((([tblSurveyResults].[qn25response]) Is Not Null));


I suppose I could use this as the opportunity to start learning to
write Visual Basic. I could use the code you suggested to read the
file in, line by line, and then use that code that was discussed on
this board a month or two ago to execute it. I was trying to avoid
having to become proficient at VB quickly - I was hoping to wait until
I had more time
Thanks,

Michael

OK. I think I am getting the picture now. Well, let me be the welcome
wagon to the wonderful world of databases and database programming.
Sadly, my magic database wand that could perform just about any database
operation without having to write a lick of code came to rest when the
batteries died, and I was not able to replace the batteries. Thus, I
had to resign myself to the lowly life of a code monkey. If you want to
get your project going -- you have a few options: 1) start acquiring a
taste for bananas, 2) find somene else who likes bananas.

As for reading/running sql code from .sql files, I think that is limited
to server based systems like sql server, Oracle. Access is a file based
system (a miniature -- scaled down -- version of sql server). Access
can perform the mainline operations like running queries, data storage
and also has an integrated front end system -- which requires VB for
manipulating.

For what Access is -- it is quite powerful. It is kind of like the 4
cylinder pickup truck of RDBMS's on steroids. It's not a semi like sql
server/Oracle, but there is no getting around having to write code.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Just write a little routine that opens up the sql and runs it.

eg:

Sub SqlScripts()

Dim vSql() As String
Dim vSqls As Variant
Dim strSql As String
Dim intF As Integer

intF = FreeFile()
Open "c:\sql.txt" For Input As #intF
strSql = Input(LOF(intF), #intF)
Close intF
vSql = Split(strSql, ";")

On Error Resume Next
For Each vSqls In vSql
CurrentDb.Execute vSqls
Debug.Print "--->" & vSqls
Next


End Sub

I suppose you could add 2-3 more lines to the above to pop open the file
dialog to browse to the sql file if it was to be changed a lot.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

On Mar 26, 11:24*pm, "Albert D. Kallal"
<PleaseNOOOsPAMmkal... (AT) msn (DOT) com> wrote:
Rich, Albert,

Thanks for your help on this. I bit the bullet and wrote code based
on your examples. It worked right away - within minutes of my
starting. Problem solved.
I decided to add a file open dialog - that took hours to get right.
Each time I googled for an example of how to do it I got a different
example which always had something missing and would not compile.

Eventually I found the best example code to open a file open dialog on
Microsoft's website - it worked right away.

I think some of my problem was that Visual Basic for Access is
different from Visual Basic, compounded by my sometimes finding code
for later or earlier versions of Access - I have Access 2003.

But thanks again.

To follow on from your other comments, I was expecting a way to run
files of SQl because my introduction SQL and databases was using
things like Orace, Informix, etc, which all have such a capability.
In fact I had only ever typed SQL straight into a window or written it
into a file then run the file - I had never used a tool which built
the query for you and I found it hard at first to use the Access Query
Design View. Now I've got used to using Access I think it's great.
It is easy to do some things very quickly and easily. For my uses it
is fast enough.

I did not want to start using Visual Basic because, in the past, I
have had to become proficient in several Assembly languages, Algol,
Fortran, Ada, C, C++, various shell and scripting languages, and
probably some I've forgotten. I have just had a ten year break from
programming, so the idea of learning another programming language was
not too appealing. Now I've made the leap, it's not so bad.

Bye.

Mike