OutPut the results of a select query to a *.TXT file
Joe Mills
query before a delete query wipes out the information. if i can avoid
dumping it into a temporary table first then deleting the table after the
archive it would be nice. skipping unecessary steps is a cool thing. also,
can i bypass the select query and just output the results of the delete query
before the info is gone? not asking much huh
thanks
'69 Camaro
> i'm trying to create a *.TXT file as an archive from the results of a select
> query before a delete query wipes out the information.
This is the syntax of the SQL query to save a SELECT query as a text file:
SELECT ID, Junk, FromDate
INTO [Text;HDR=YES;DATABASE=C:\Work\].Stuff.txt
FROM tblStuff
WHERE (ID<20);
The resulting text file will be C:\Work\Stuff.txt and will contain three
columns of data, ID, Junk, and FromDate. The formatting of the data types
will be automatic, since Jet will automatically create a Schema.ini file in
the destination directory.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
Joe Mills
'69 Camaro
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
"Joe Mills" <JoeM...@discussions.microsoft.com> wrote in message
news:11EC37E7-3986-4E0B...@microsoft.com...
David F Cox
"'69 Camaro" <ForwardZERO_SP...@Spameater.orgZERO_SPAM> wrote in
message news:79BD90E4-5F72-4131...@microsoft.com...
'69 Camaro
Happy New Year!
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
"David F Cox" <nos...@please.com> wrote in message
news:%23YDe7x$KHHA...@TK2MSFTNGP04.phx.gbl...
Joe Mills
i'm one of those guys that likes to know why things work, not just how. can
you make sure i'm getting this right please.
CodeLine1: "SELECT" (this tells it that the data comes from a query?), ",
ID" (field1?), ", Junk" (field2?). ", FromDate" (field3?), ....etc?
CodeLine2: "INTO" (what and where to put it?), "[Text (output type?);
HDR=YES (don't know this?), "DATABASE=C:\Work\]." (where to put it?),
"Stuff.txt" (what to call it?)
CodeLine3: "FROM tblStuff" (where did tblStuff come from?)
CodeLine4: "WHERE (ID<20);" (why a criteria clause? i want all of it to go.)
in CodeLine1 do i have to spell out every field with a comma in between?
that could take awhile since the query selects all the fields from six tables
most of which have more than 20 fields.
this way when i screw things up i'll have a better idea of where to fix it.
Douglas J. Steele
Don't think of it as four lines of code: it's a single SQL statement that
Gunny chose to display on four lines. No offense, but if you're going to
work with Access, you really should learn SQL.
To adapt it to your situation, create a query that returns all of the data
you want. Yes, you should specify specific fields, rather than use SELECT *,
in your query. Once the query is working how you want it to, go into the SQL
of the query (look under the View option on the menu when you've got the
query open) and add the text
INTO [Text;HDR=YES;DATABASE=C:\Work\].Stuff.txt
before the FROM keyword of the query.
As you supposed, the INTO keyword tells Access to store a copy of the data
retrieved "somewhere". The "somewhere" is defined by the combination of
what's in square brackets and the .Stuff.txt afterwards. What Gunny's got
will create a file named C:\Work\Stuff.txt that contains the data.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Joe Mills" <JoeM...@discussions.microsoft.com> wrote in message
news:7E1063F3-CCF6-46C9...@microsoft.com...
Joe Mills
thank you for being specific about how and where to put the code. i did
create the query using SELECT* for each of the tables instead of naming each
field as there are a couple hundred fields. the query works great. is that
going to cause a problem after inserting the SQL statement? do you need (or
even want) more info as to what my application is for/doing to better
understand where i'm at?
i've read a lot of responses by you and Gunny and my opinion is, i couldn't
be in better hands. thank you very much
Douglas J. Steele
If it does, you're fine. If it doesn't, post back with what didn't work, and
we'll see what we can do.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Joe Mills" <JoeM...@discussions.microsoft.com> wrote in message
news:1A686ED2-968A-4EB7...@microsoft.com...
Joe Mills
it's says it can't find "Archive.mdb"
i'm not trying to save it to another database, it's just supposed to be a
backup .TXT file of the data in the record they are deleting in case they
change their mind after the delete. am i trying to be too nice to the users?
the deleted data will probably never be needed again.
John Vinson
I think Gunny's INTO option can be made to work - but if you have
trouble with it, take a look into using VBA code with the TransferText
method to export the data to a text file. Open the VBA editor and look
for help on TransferText.
John W. Vinson[MVP]
David F Cox
Try INTO [Text; HDR=Yes; Database=C:\.Archive.txt]
"Joe Mills" <JoeM...@discussions.microsoft.com> wrote in message
news:BDF023C1-D1C6-488E...@microsoft.com...
Douglas J. Steele
What's the exact SQL you're trying to run now?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Joe Mills" <JoeM...@discussions.microsoft.com> wrote in message
news:BDF023C1-D1C6-488E...@microsoft.com...
Joe Mills
backup). the user has to confirm the delete anyway so if it's gone, tough
crap it's their problem?
off-the-subject question: how do the pros like yourself protect your
applications from unauthorized duplication? are there copywrite issues i
need to be aware of? i'm obviously a new developer and want to cover my
financial butt. the application i'm writing has the potential to be sold to
literally thousands of institutions (happened to stumble on a nationwide
problem that this will solve) and i, naturally, want the credit. would i
sell it as a license agreement? if so, your thoughts on fees?
thank you for your interest
Joe Mills
a valid name. i noticed that you have a period (.) before and after Archive,
is that the problem?
Joe Mills
here's the SQL i'm using:
SELECT Residents.ID, Residents.*, Admission.*, Devices.*, Skin.*,
Property.*, [Physical and Structural].*, Vitals.*
INTO [Text; HDR=Yes; Database=C:\Archive.txt]
FROM (((((Residents INNER JOIN Admission ON Residents.ID=Admission.ID) INNER
JOIN Devices ON Admission.Key=Devices.Key) INNER JOIN Skin ON
(Devices.Key=Skin.Key) AND (Admission.Key=Skin.Key)) INNER JOIN Property ON
(Devices.Key=Property.Key) AND (Admission.Key=Property.Key)) INNER JOIN
[Physical and Structural] ON (Devices.Key=[Physical and Structural].Key) AND
(Admission.Key=[Physical and Structural].Key)) INNER JOIN Vitals ON
(Devices.Key=Vitals.Key) AND (Admission.Key=Vitals.Key)
WHERE (((Residents.ID)=Forms!DeleteResident.ID));
Douglas J. Steele
Database=C:\].Archive.txt, but the SQL below has INTO [Text; HDR=Yes;
Database=C:\Archive.txt]
The location of the closing square bracket is important: the folder name
goes inside the square brackets, but the file name is outside (with a period
in front of it)
You may have to put the actual field names in, though. Access will be
creating a file named schema.ini in the same folder as the text file, and
I'm not sure whether it will accept the * instead (sorry, too lazy to test)
And sorry I can't help you with your question about unauthorized
duplication. What databases I design at work are the property of the
company, not me, and what code I post on the internet, on my website and in
the articles I write, I don't care if others use it.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Joe Mills" <JoeM...@discussions.microsoft.com> wrote in message
news:FE6BC453-8E55-4C53...@microsoft.com...
Joe Mills
David F Cox
looking odd to me) is the only one that works a bit, and only sometimes, for
me. When I say works a bit it will only work for my ID PK field, and ignores
other fields.
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ui39%235ELH...@TK2MSFTNGP06.phx.gbl...
John Vinson
<JoeM...@discussions.microsoft.com> wrote:
>i'm wondering if i'm just wasting all your valuable time (the need for a
>backup). the user has to confirm the delete anyway so if it's gone, tough
>crap it's their problem?
Two suggestions:
Keep regular backups (several layers deep ideally) of the entire
database.
Or... don't delete records at all; just add a yes/no field Active
defaulting to Yes, set it to No, and filter your forms to show only
active records.
Extracted text files are a hassle to manage!
No words of wisdom about securing your database, other than to
distribute only compiled MDE files and keep the MDB for yourself.
John W. Vinson[MVP]
Joe Mills
hassle of extracted data files. the users have to have some responsibility
right!
i greatly appreciate all the dialog on this issue but it is Saturday, let's
all go and have a beer!
David W. Fenton
> how do the pros like yourself protect your
> applications from unauthorized duplication?
I don't. I own the apps or the client owns the apps. The clients
sign agreements that spell out what distributionrights they have
(which is always NONE).
> are there copywrite issues i
> need to be aware of? i'm obviously a new developer and want to
> cover my financial butt. the application i'm writing has the
> potential to be sold to literally thousands of institutions
> (happened to stumble on a nationwide problem that this will solve)
> and i, naturally, want the credit. would i sell it as a license
> agreement? if so, your thoughts on fees? thank you for your
> interest
You could print the customer name on all reports. In an MDE this
couldn't be changed.
You might want to look at this page:
http://www.frez.co.uk/freecode.htm
and look for the "Create and Validate License Keys." That will allow
you to distribute apps that won't function without a valid license
key from you. I'm working on a way to tie that to the location where
it's installed. Should I work that out, I'll post about it.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David F Cox
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> Your original post said you were using INTO [Text; HDR=Yes;
> Database=C:\].Archive.txt, but the SQL below has INTO [Text; HDR=Yes;
> Database=C:\Archive.txt]
>
> The location of the closing square bracket is important: the folder name
> goes inside the square brackets, but the file name is outside (with a
> period in front of it)
>
> You may have to put the actual field names in, though. Access will be
> creating a file named schema.ini in the same folder as the text file, and
> I'm not sure whether it will accept the * instead (sorry, too lazy to
> test)
FWIW my schema.ini
[Stuff.txt]
ColNameHeader=True
CharacterSet=1252
Format=CSVDelimited
Col1=ID Integer
I had to delete stuff.txt between tests, Access would not overwrite the old
file. It would not overwrite schema.ini either, it just re-used the existing
one without any warning, leading to much bewilderment, as each test produced
the same result.
I have not been able to find any documentation for this writing TXT files
feature. The syntax does not appear natural to me. I suspect that there are
hidden depths to this feature and would like to find a write-up.
'69 Camaro
> I suspect that there are hidden depths to this feature and would like to find
> a write-up.
For the syntax in your schema.ini file, you'll find the specifications and
information for using text files with Access on these Web pages:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
http://support.microsoft.com/default.aspx?scid=155512
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
David F Cox
"'69 Camaro" <ForwardZERO_SP...@Spameater.orgZERO_SPAM> wrote in
message news:%23wgtf0K...@TK2MSFTNGP03.phx.gbl...
'69 Camaro
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
"David F Cox" <nos...@please.com> wrote in message
news:uT1MMBLL...@TK2MSFTNGP02.phx.gbl...