I'd like to Output a certain record from a table into a Text file. GlbLastWip is the criteria - Dim strSQL As String
-
strSQL = "SELECT * FROM [tbl_Zeichnungen] WHERE ([F23]='" & GlbLastWip & "');"
-
doCmd.TransferText acExportDelim, "ExpRec_q", strSQL, "C:\temp\ExpRec_q.txt"
why does this not work?, i'm getting error 3011
Thanks for your help
vittorio0
9 1072 NeoPa 32,556
Expert Mod 16PB DoCmd.TransferText() requires the name of a table or QueryDef object. It will not work with a simple SQL string as you've tried to use. If you want a QueryDef to select a single record as per your SQL then you'll probably need to filter it by a control on a form you know to be open.
NB. Please, when asking for help with code that has failed, include the error message with the question. The error number is of very limited help for those trying to assist you.
Thansk a lot for your answer, I have tried that and it works. When I try to create the Exportspecification with the filter in place, it comes up with the errormessage "1 Parameter was excepted, to few were supplied" (translated from German) any idea?
NeoPa 32,556
Expert Mod 16PB
I would guess that the form wasn't open and available when you were trying to create it. Am I right?
Yes, thats right, how can I have the form open and at the same time be in edit mode at the querydef.
I managed to have the form open and tried to create the exportspecification, however the error remains the same :(
NeoPa 32,556
Expert Mod 16PB
In that case I'll need some more info from you : - The Name & SQL of the QueryDef.
- Details of your Export Specification.
Before we get to that though, I assume you've already checked that the QueryDef runs perfectly when opened in Datasheet view? Obviously that should be checked before trying to export it.
The QueryDef runs ok when the form is opened and Returns the proper record.
1. The Name of the QueryDef is ExpRec_q and the SQL of it: -
SELECT tbl_Zeichnungen.*
-
FROM tbl_Zeichnungen
-
WHERE (((tbl_Zeichnungen.F23)=[forms]![ZeichnungSuchen_f]![dwgname]));
-
The Error comes up after Rightklick - Export - Textfile, I can set the Output file and after the error pops up. I like to specify that the first line has Labels and the file is Tab separated.
NeoPa 32,556
Expert Mod 16PB
Frankly, I can't think why it would complain in those circumstances, and without direct access to your session I'm unlikely to see enough to determine the problem. However, you should be able to get around the issue.
Do a temporary update of your QueryDef to say : - SELECT tbl_Zeichnungen.*
-
FROM tbl_Zeichnungen
-
WHERE ([F23]='')
Use the correct format for the literal. I guessed string but it may be numeric. It's probably easiest and safest to use an valid value in there. Now, use this to create and save your export specification.
Once that's done you can revert to your earlier SQL and use it.
zmbd 5,501
Expert Mod 4TB
:-)
How about the following? Open a simple text file using standard IO, open the recordset, step thru the fields (there is a way to pull the record; however, it can get messy depending on the contents) and either directly write the value to the text file or build a string (as I've done here) and write it all at once.
(I've not put a lot of commenting here, I think the code is fairly straight forward; however, if one wants I can go back and comment :) )
>Note that one will have to modify the zPath string to point to the correct location. I've not tested for the non-existence of the path nor read/write/modify privilege.
(( this modification comes to mind :) home > topics > microsoft access / vba > insights > select a file or folder using the filedialog object to allow the user to select at least the correct directory... ))
>One can change this to a function and/or add arguments to make this more generic. - Sub getandwrite()
-
Dim zdb As DAO.Database
-
Dim zrs As DAO.Recordset
-
Dim zflds As DAO.Fields
-
Dim zfld As DAO.Field
-
Dim zSQL As String
-
Dim zFNum As Long
-
Dim zPath As String
-
Dim zFailSafe As Long
-
'
-
Set zdb = CurrentDb
-
'
-
zSQL = "SELECT tbl_EventHistory.EventHistory_pk, tbl_EventHistory.fk_tbluser FROM tbl_EventHistory;"
-
Set zrs = zdb.OpenRecordset(Name:=zSQL, Type:=dbOpenSnapshot)
-
If zrs.RecordCount Then
-
'
-
Set zflds = zrs.Fields
-
'
-
'Now one can check for the existence of a file
-
'and open for append, or delete using the stdIO
-
'I just appended the current date-time-seconds
-
zPath = "C:\Users\
[accountname] \Documents\" & _ -
"\My Documents\" & _
-
"MS_Database_Projects\BytesWork\" & _
-
"testoutput_" & Format(Now(), "yyyymmddss") & ".txt"
-
zFNum = FreeFile
-
Open zPath For Output As zFNum
-
'
-
'
-
zrs.MoveFirst
-
zSQL = ""
-
zFailSafe = 0
-
Do
-
For Each zfld In zflds
-
zSQL = zSQL & zfld.Value & ","
-
Next zfld
-
zSQL = Left(zSQL, (Len(zSQL) - 1)) & ":"
-
' Debug.Print zSQL
-
Print #zFNum, zSQL
-
zSQL = ""
-
zrs.MoveNext
-
'
-
zFailSafe = zFailSafe + 1
-
If zFailSafe >= 1000 Then Err.Raise Number:=-2147220991, Source:="RecordLoop", Description:="Record loop fail safe at 1000"
-
Loop Until zrs.EOF
-
Else
-
Err.Raise Number:=-2147220992, Source:="Record Source", Description:="No records in source for output"
-
End If
-
'
-
zcleanup:
-
Close 'close any active text files
-
If Not zflds Is Nothing Then Set zflds = Nothing
-
If Not zrs Is Nothing Then
-
zrs.Close
-
Set zrs = Nothing
-
End If
-
If Not zdb Is Nothing Then Set zdb = Nothing
-
zeexit:
-
Exit Sub
-
Zerrtrap:
-
MsgBox Prompt:="ErrS: " & Err.Source & vbCrLf & _
-
"ErrN: " & Err.Number & vbCrLf & _
-
"ErrD: " & Err.Description, _
-
Title:="Oh Bother an Error"
-
If zFailSafe < 0 Then Resume zeexit
-
zFailSafe = -1000
-
Resume zcleanup
-
End Sub
From test table the text file output is simply:
1,1:
2,2:
or with the strings
1,Dummy1:
2,Dummy2:
>> NOTICE << no quotes around the string values. You could add additional logic to test for the data-type of the field and add quotes as needed.
Modify the string output as needed: home > topics > microsoft access / vba > insights > vba standard text file i/o statements Sign in to post your reply or Sign up for a free account.
Similar topics
by: Orjan |
last post by:
I have a table that only should contain one record.
I know there is a function to make sure that only one record can be written
into a table but I cannot find it.
Can anybody help me with...
|
by: Daron |
last post by:
I am using this command to import text file:
DoCmd.TransferText acImportDelim, str_SpecName, str_TblName,
str_FileName, False
- The import will always be missing the first line in the text...
|
by: Eric |
last post by:
If you look into data the expr1 not match with CORP but it still shows
multiple records as account4 has only one record
TicketNum Account4 Expr1 LstVldTech TECH
CORP...
|
by: dzulai |
last post by:
i have a continuous subform and its AllowAdditons and AllowEdits property are set to false. A command button("Add") in my main form sets both properties to True, enabling the New Record to appear. My...
|
by: zack |
last post by:
This query has been driving me crazy, and would be grateful for a
nudge in the right direction. Still pretty much a beginner using
Access 2003, I just cannot figure the solution to the following in...
|
by: GeryLuz |
last post by:
Hi !!
I have a problem showing a users table in an ASP page.
It shows me only one record when i checked that there are more than one in my table.
But when i print the RecordCount property of...
|
by: oranoos3000 |
last post by:
hi
(i use php and mysql on os windows)
i want to use statment in mysql like statement select top in sql server
for show only 1 record with specific condition
i use under statment as follow
...
|
by: lrod |
last post by:
I need some help maybe somebody can help me with this. I'm tyring to run this query, however the out result is only one Record but if i run the select statement without declare I get a bunch, can...
|
by: JamesHalko |
last post by:
Hello Everyone,
I want a command button to open a report to the last record in my database and only that report. Right now My button opens up the rport and the report has the code:
Private Sub...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |