Dear Experts,
I have the following problem. I'm generating output txt files from access (an access 2002 table is converted into deleimited text with tabs including field names). For that I have a small function that I call from the main VBA script, which includes the so-called Export Specification. Everything works fine as long as the name of the output file is written in characters in the file specification of the DoCmd.TransferText command.
Nevertheless, if I try to assign a dynamic name (say FileX where X can vary from 0 to 9) I get a message error. However if I Debug.Print my current string variable containing the path and the file name, I see the correct name (e.g. C:\Directory\FileX.txt). Does anybody has an idea what's going on here....
Marcin
The function called from the main code is the following: - Option Compare Database
-
-
-
-
Function TXTsendTblFinalMATLABTable1()
-
WlistNum = DLookup("Lists", "WorkingIndexList")
-
Debug.Print "WListNum", WlistNum
-
strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List" & WlistNum & ".txt"
-
Debug.Print "strJname", strJname
-
-
-
-
DoCmd.TransferText transfertype:=acExportDelim, _
-
specificationname:="FinalMATLABTable1 Export Specification", _
-
TableName:="FinalMATLABTable1", _
-
Filename:=strJname, _
-
hasfieldnames:=True
-
End Function
-
-
The resulting Debug.Print is: - OldNameSpecialRegion
-
NewNameRegion
-
OldNameSpecialRegionID
-
NewNameRegionCode
-
OldNameCodeBloomberg
-
NewNameCode
-
WListNum 37
-
strJname C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List37.txt
Big thanks everybody!
I spent quite a long time figuring out what was wrong. In fact nothing was wrong in the code, however for some reason the ACCESS wasn't accepting the export DoCmd command. After a step-by-step elimination procedure for possible errors, I started to suspect that something was wrong with the program itself. Closing, compacting and opening the database wouldn't help neither. Then, I tried to resave the Export Specification file, but the problem was still persisting. Finally I created a completely new database, and tried to build the same thing in. And this time it finally worked with no hassle!!! In fact all works perfectly (my code and your suggestions too) as it should be. So the last step was to delete a module with my function in the original database and to re-type a new one from scratch.
So now it's time for conclusions:
The possible reason for the error: Some bug in ACCESS 2002 that activates itself under certain (undetected for the moment) circumstances.....
The lesson to take: If it ever happens to you, just delete the "faulty" module and rewrite a new one from scratch.
Thanks again for everybody's input!
Marcin
6 2925 @mabrynda - Is there a Typo in the Absolute Path portion?
- What happens if you replace Line #8 with:
- strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List37.txt"
Looks like it might not like the number followed by the . delimiter for the extension.
What happens if you follow that variable number with a character, i.e List37a.txt or List38x.txt? Or 37List.txt?
Jim
If everything else is OK, then try: - strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List" & CStr(WlistNum) & ".txt"
Big thanks everybody!
I spent quite a long time figuring out what was wrong. In fact nothing was wrong in the code, however for some reason the ACCESS wasn't accepting the export DoCmd command. After a step-by-step elimination procedure for possible errors, I started to suspect that something was wrong with the program itself. Closing, compacting and opening the database wouldn't help neither. Then, I tried to resave the Export Specification file, but the problem was still persisting. Finally I created a completely new database, and tried to build the same thing in. And this time it finally worked with no hassle!!! In fact all works perfectly (my code and your suggestions too) as it should be. So the last step was to delete a module with my function in the original database and to re-type a new one from scratch.
So now it's time for conclusions:
The possible reason for the error: Some bug in ACCESS 2002 that activates itself under certain (undetected for the moment) circumstances.....
The lesson to take: If it ever happens to you, just delete the "faulty" module and rewrite a new one from scratch.
Thanks again for everybody's input!
Marcin
Too late for this time, but you can avoid retyping code easily. Copy/Paste code for entire module into MS Notepad or another text editor without an RTF format.
This strips all special characters, leaving only the text. Then Copy/Paste from Notepad into your new database.
@OldBirdman
Hi OldBirdman,
Thanks for the advice. I always use the Notepad++ to get rid of the hidden characters. Deleting the text and copying/pasting didn't work this time. I had really to physically delete the entire module in the ACCESS database and then put it back. But you are right, I should have pasted the text from the deleted module into the fresh one instead of typing it. Well, I was so mad I couldn't find why it wasn't working that I didnt think about it....
M.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Oleg Ogurok |
last post by:
Hi all,
On my web page, I have a <OBJECT DATA="mydatafile.ica" ...> which loads
Citrix ICA web client.
The problem is that I want to generate the mydatafile.ica on the fly (e.g.
based on...
|
by: adrian GREEMAN |
last post by:
When I try to import a text file with new data for an existing
table I get the error "1148 - the used command is not allowed
with this MySQL version."
I have tried with both PHPMyAdmin2.3 and...
|
by: PC Work |
last post by:
I am using code to export a database to a text file in ms access 2002
Not matter what export method I choose, a field name with a # in it exports
to a period.
so a field name caled '# of items'...
|
by: Scott |
last post by:
I wish to create a text document (to the users desktop on any machine) which outputs to a standard text file with information obtained from there use of my program... Some of the text will be fixed...
|
by: gualtmacchi |
last post by:
I'm processing an XML input file getting a plain text file where from M
nodes I got N output lines...
It's not relevant but the input file is a recordset coming from a
database and the output is...
|
by: Chris |
last post by:
Can anyone recommend how to do a screen scrape from a dynamic page and save
it to a text file? Regards, Chris.
|
by: Max Vit |
last post by:
Here is my problem: I have an application built in Access that outputs
sensitive data to a text file.
I would like to encrypt this data *whilst* the file is being
outputted.
The encryption I was...
|
by: Sheepman |
last post by:
My quest, to put 100 random numbers in ten rows of ten. Then output the same to the screen and a file. My screen output is working,yeah! Text file, not so much. The data is getting there but not...
|
by: WayneBurrows |
last post by:
Can I save dynamically created data to a text file on the client's
machine?
Preferrably in a user named file through a save dialog box.
Thanks
Wayne
|
by: dlbuller |
last post by:
Hi, I apologize if this question has been asked already. I have searched with no luck. I am trying to output a specifically formatted text file and then change the extension to another program...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |