Run a file of SQL
in [Help]
|
From: Gilgamesh on 25 Mar 2010 14:25 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.
From: Rich P on 25 Mar 2010 17:03 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 ***
From: Gilgamesh on 26 Mar 2010 13:13 On Mar 25, 2:03 pm, Rich P <rpng...(a)aol.com> wrote: > 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 Developersdexhttp://www.developersdex.com*** 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
From: Rich P on 26 Mar 2010 14:18 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 ***
From: Albert D. Kallal on 27 Mar 2010 02:24 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(a)msn.com
|
Next
|
Last
Pages: 1 2 Prev: Trap Closing Access Next: to change the color of several parts in a text in RichText |