copy field value to text file and save as .sql

Question

in MS Access i have a field that stores the SQL used for a project. the sql field is a memo field with "Rich Text" property enabled. what that means is that a code below

CREATE TABLE NR_TMP_1_104 AS
     SELECT DISTINCT tmp.otherid
                    ,tmp.groupno
       FROM NR_tmp_1_900 tmp
           ,NR_TMP_1_103 vp
      WHERE tmp.otherid = vp.otherid;



      SELECT groupno, COUNT(otherid) Counts
      FROM NR_TMP_1_104
      GROUP BY groupno;

     SELECT otherid, actualvpchoice FROM NR_TMP_2_102

really looks like all the way below (i'm sorry for this huge chunk). so that means i can't do docmd.transfertext, the only way to get normal looking text is to copy and paste.

i need to be able to export this field to a text file and save it with the extension of .sql.

here's what i have so far))))

Forms!freports!sql.SetFocus
DoCmd.RunCommand acCmdCopy

how do i open a text file, paste what's on my clipboard and save it as .sql.

thank you very much

" 

CREATE TABLE NR_TMP_1_104 AS

     SELECT DISTINCT tmp.otherid

                    ,tmp.groupno

       FROM NR_tmp_1_900 tmp

           ,NR_TMP_1_103 vp

      WHERE tmp.otherid = vp.otherid;

      

      

      

      SELECT groupno, COUNT(otherid) Counts

      FROM NR_TMP_1_104

      GROUP BY groupno;

      

     SELECT otherid, actualvpchoice FROM NR_TMP_2_102"

HansUp · Accepted Answer

You can use the PlainText() function to get just the plain text from your memo field's rich text. Here's an example from the Immediate window.

? DLookup("memo_field", "tblFoo", "id=1")
a b c

? PlainText(DLookup("memo_field", "tblFoo", "id=1"))
a b c

Once you get the plain text, you don't need to go through Notepad in order to write it to a file. You can write to a file directly using VBA file functions or via the Scripting.FileSystemObject methods. Here is an example of the first approach. (I'm assuming Forms!freports!sql contains the rich text.)

Dim FileNum As Integer
FileNum = FreeFile()
Open "C:\SomeFolder\your.sql" For Output As #FileNum
Write #FileNum, PlainText(Forms!freports!sql)
Close #FileNum

copy field value to text file and save as .sql

Answers (2)

Related Questions