473,320 Members | 1,909 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Exporting text files without .txt extension

Following code exports tables as text files:

For Each tdf In db.TableDefs
StrTblName = tdf.Name
Me.txtProgName = StrTblName
Me.txtProgName.Requery
tblAtt = tdf.Attributes
moddate = tdf.LastUpdated
If Left(StrTblName, 3) <> "tbl" And tblAtt = 0 Then
strSQL = "INSERT INTO tmpExport ( Code ) SELECT _
& " & StrTblName & ".[code] FROM " & StrTblName & ";"
DoCmd.RunSQL strSQL

DoCmd.TransferText acExportFixed, "ExportTxt", "tmpExport", _
& "C:\CODEFiles\" & StrTblName

I want to export the text file with the exact name as the
table, however, I get an error saying
"Cannot update. Database or object is read only".
This is due to Jet 4.0 text ISAM.
I will concede this and make sure all tables are exported as
..txt files by changing the last line to
"C:\CODEFiles\" & StrTblName & ".txt".
Just need to have one line that RENAMES the
exported file, striping out the ".txt" extension.
I think the filesystemobject function is used to rename
external files from within Access, but I'm not sure.
Any ideas?

Nov 13 '05 #1
6 6615
"J. Shrimp, Jr." <ju***********@hotmail.com> wrote:
Following code exports tables as text files:

For Each tdf In db.TableDefs
StrTblName = tdf.Name
Me.txtProgName = StrTblName
Me.txtProgName.Requery
tblAtt = tdf.Attributes
moddate = tdf.LastUpdated
If Left(StrTblName, 3) <> "tbl" And tblAtt = 0 Then
strSQL = "INSERT INTO tmpExport ( Code ) SELECT _
& " & StrTblName & ".[code] FROM " & StrTblName & ";"
DoCmd.RunSQL strSQL

DoCmd.TransferText acExportFixed, "ExportTxt", "tmpExport", _
& "C:\CODEFiles\" & StrTblName

I want to export the text file with the exact name as the
table, however, I get an error saying
"Cannot update. Database or object is read only".
This is due to Jet 4.0 text ISAM.
I will concede this and make sure all tables are exported as
.txt files by changing the last line to
"C:\CODEFiles\" & StrTblName & ".txt".
Just need to have one line that RENAMES the
exported file, striping out the ".txt" extension.
I think the filesystemobject function is used to rename
external files from within Access, but I'm not sure.
Any ideas?

Seee the Name statement:

Name <oldpathfilename> As <newpathfilename>

HTH
Matthias Kläy
--
www.kcc.ch
Nov 13 '05 #2
..dat is a generic extension frequently used. Would that suffice? Change the
last line to:
"C:\CODEFiles\" & StrTblName & ".dat"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"J. Shrimp, Jr." <ju***********@hotmail.com> wrote in message
news:11*************@corp.supernews.com...
Following code exports tables as text files:

For Each tdf In db.TableDefs
StrTblName = tdf.Name
Me.txtProgName = StrTblName
Me.txtProgName.Requery
tblAtt = tdf.Attributes
moddate = tdf.LastUpdated
If Left(StrTblName, 3) <> "tbl" And tblAtt = 0 Then
strSQL = "INSERT INTO tmpExport ( Code ) SELECT _
& " & StrTblName & ".[code] FROM " & StrTblName & ";"
DoCmd.RunSQL strSQL

DoCmd.TransferText acExportFixed, "ExportTxt", "tmpExport", _
& "C:\CODEFiles\" & StrTblName

I want to export the text file with the exact name as the
table, however, I get an error saying
"Cannot update. Database or object is read only".
This is due to Jet 4.0 text ISAM.
I will concede this and make sure all tables are exported as
.txt files by changing the last line to
"C:\CODEFiles\" & StrTblName & ".txt".
Just need to have one line that RENAMES the
exported file, striping out the ".txt" extension.
I think the filesystemobject function is used to rename
external files from within Access, but I'm not sure.
Any ideas?

Nov 13 '05 #3
Read http://support.microsoft.com/?id=245407

The only extensions allowed by default are txt, csv, tab, asc, htm and html

I believe that was one of the reasons the .dat extension was explicitly
excluded is that the registry files use it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Rz*****************@newsread3.news.atl.earthl ink.net...
.dat is a generic extension frequently used. Would that suffice? Change
the
last line to:
"C:\CODEFiles\" & StrTblName & ".dat"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"J. Shrimp, Jr." <ju***********@hotmail.com> wrote in message
news:11*************@corp.supernews.com...
Following code exports tables as text files:

For Each tdf In db.TableDefs
StrTblName = tdf.Name
Me.txtProgName = StrTblName
Me.txtProgName.Requery
tblAtt = tdf.Attributes
moddate = tdf.LastUpdated
If Left(StrTblName, 3) <> "tbl" And tblAtt = 0 Then
strSQL = "INSERT INTO tmpExport ( Code ) SELECT _
& " & StrTblName & ".[code] FROM " & StrTblName & ";"
DoCmd.RunSQL strSQL

DoCmd.TransferText acExportFixed, "ExportTxt", "tmpExport", _
& "C:\CODEFiles\" & StrTblName

I want to export the text file with the exact name as the
table, however, I get an error saying
"Cannot update. Database or object is read only".
This is due to Jet 4.0 text ISAM.
I will concede this and make sure all tables are exported as
.txt files by changing the last line to
"C:\CODEFiles\" & StrTblName & ".txt".
Just need to have one line that RENAMES the
exported file, striping out the ".txt" extension.
I think the filesystemobject function is used to rename
external files from within Access, but I'm not sure.
Any ideas?


Nov 13 '05 #4
From my file -----

What Is A DAT File

It is not a predefined format, it generaly mean "data" and can be like a
dot-ini file, or an xml string, but it can also be a simple list of words to
binary data. Basically, it is not a file extension generally associated to
a, one, specific program and each program may use its own interpretation of
such a file. Generally, a program won't look for some dot-dat file, but for
ProgramName.dat file, or something similar.

In Windows (95,98, ?...), files "system.dat" and "user.dat" store the
configuration ettings, etc... basically, registry settings for your Windows,
installed software. You access / odify these settings using RegEdit.exe
(with EXTREME caution).

Otherwise, a lot of 3rd-party software packages use the "dat" extension for
their settings / data file.
Older versions of SQL Server (pre 7.0) use .DAT to identify backup files and
database device files

Also used for video files.


I read in a newsgroup post where someone had an Access database where the
data as stored in a .DAT file. How is this done?
If it's a SQL Server 6.x backup file it was done using the 6.x management
tools. You will need to restore it through Enterprise Manager. If it's a
device file you're out of luck. Otherwise it might be a proprietary backup
file.


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:6b********************@rogers.com...
Read http://support.microsoft.com/?id=245407

The only extensions allowed by default are txt, csv, tab, asc, htm and html
I believe that was one of the reasons the .dat extension was explicitly
excluded is that the registry files use it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Rz*****************@newsread3.news.atl.earthl ink.net...
.dat is a generic extension frequently used. Would that suffice? Change
the
last line to:
"C:\CODEFiles\" & StrTblName & ".dat"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"J. Shrimp, Jr." <ju***********@hotmail.com> wrote in message
news:11*************@corp.supernews.com...
Following code exports tables as text files:

For Each tdf In db.TableDefs
StrTblName = tdf.Name
Me.txtProgName = StrTblName
Me.txtProgName.Requery
tblAtt = tdf.Attributes
moddate = tdf.LastUpdated
If Left(StrTblName, 3) <> "tbl" And tblAtt = 0 Then
strSQL = "INSERT INTO tmpExport ( Code ) SELECT _
& " & StrTblName & ".[code] FROM " & StrTblName & ";"
DoCmd.RunSQL strSQL

DoCmd.TransferText acExportFixed, "ExportTxt", "tmpExport", _
& "C:\CODEFiles\" & StrTblName

I want to export the text file with the exact name as the
table, however, I get an error saying
"Cannot update. Database or object is read only".
This is due to Jet 4.0 text ISAM.
I will concede this and make sure all tables are exported as
.txt files by changing the last line to
"C:\CODEFiles\" & StrTblName & ".txt".
Just need to have one line that RENAMES the
exported file, striping out the ".txt" extension.
I think the filesystemobject function is used to rename
external files from within Access, but I'm not sure.
Any ideas?



Nov 13 '05 #5
I fail to see what point you're trying to make.

If the OP attempts to use your suggestion, he will get the same "Cannot
update. Database or object is read only" error.

And with respect to your question about having "an Access database where the
data as stored in a .DAT file. How is this done?", while you can't link to
it through the File | Get External Data | Link Tables dialog, you can link
to any filename using code.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:v5**************@newsread3.news.atl.earthlink .net...
From my file -----

What Is A DAT File

It is not a predefined format, it generaly mean "data" and can be like a
dot-ini file, or an xml string, but it can also be a simple list of words
to
binary data. Basically, it is not a file extension generally associated to
a, one, specific program and each program may use its own interpretation
of
such a file. Generally, a program won't look for some dot-dat file, but
for
ProgramName.dat file, or something similar.

In Windows (95,98, ?...), files "system.dat" and "user.dat" store the
configuration ettings, etc... basically, registry settings for your
Windows,
installed software. You access / odify these settings using RegEdit.exe
(with EXTREME caution).

Otherwise, a lot of 3rd-party software packages use the "dat" extension
for
their settings / data file.
Older versions of SQL Server (pre 7.0) use .DAT to identify backup files
and
database device files

Also used for video files.


I read in a newsgroup post where someone had an Access database where the
data as stored in a .DAT file. How is this done?
If it's a SQL Server 6.x backup file it was done using the 6.x management
tools. You will need to restore it through Enterprise Manager. If it's a
device file you're out of luck. Otherwise it might be a proprietary backup
file.


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:6b********************@rogers.com...
Read http://support.microsoft.com/?id=245407

The only extensions allowed by default are txt, csv, tab, asc, htm and

html

I believe that was one of the reasons the .dat extension was explicitly
excluded is that the registry files use it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Rz*****************@newsread3.news.atl.earthl ink.net...
> .dat is a generic extension frequently used. Would that suffice? Change
> the
> last line to:
> "C:\CODEFiles\" & StrTblName & ".dat"
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> re******@pcdatasheet.com
> www.pcdatasheet.com
>
>
>
> "J. Shrimp, Jr." <ju***********@hotmail.com> wrote in message
> news:11*************@corp.supernews.com...
>> Following code exports tables as text files:
>>
>> For Each tdf In db.TableDefs
>> StrTblName = tdf.Name
>> Me.txtProgName = StrTblName
>> Me.txtProgName.Requery
>> tblAtt = tdf.Attributes
>> moddate = tdf.LastUpdated
>> If Left(StrTblName, 3) <> "tbl" And tblAtt = 0 Then
>> strSQL = "INSERT INTO tmpExport ( Code ) SELECT _
>> & " & StrTblName & ".[code] FROM " & StrTblName & ";"
>> DoCmd.RunSQL strSQL
>>
>> DoCmd.TransferText acExportFixed, "ExportTxt", "tmpExport", _
>> & "C:\CODEFiles\" & StrTblName
>>
>> I want to export the text file with the exact name as the
>> table, however, I get an error saying
>> "Cannot update. Database or object is read only".
>> This is due to Jet 4.0 text ISAM.
>> I will concede this and make sure all tables are exported as
>> .txt files by changing the last line to
>> "C:\CODEFiles\" & StrTblName & ".txt".
>> Just need to have one line that RENAMES the
>> exported file, striping out the ".txt" extension.
>> I think the filesystemobject function is used to rename
>> external files from within Access, but I'm not sure.
>> Any ideas?
>>
>>
>>
>
>



Nov 13 '05 #6
Thanks for all contributors, had a look at the report utilities
program, wrong version of access and there are errors when converting
so it is a non starter unfortunately.

JBN
Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Berislav Lopac | last post by:
I know how to set Apache to send files with any extension to PHP before sending a response. Is there a way to do so for files with no extension whatsoever? I don't want *all* files to be parsed by...
4
by: hoke | last post by:
I want to display plain text files in the browser. The files contain html and javascript and have a .txt extension. This works fine with files with just html. Unfortunately when showing files with...
5
by: Jayjay | last post by:
Is it possible to export a report to word and have it include the graphics that are in the report? I'd like to take some of our reports and export them to word so we can email those to the...
3
by: Nate Hekman | last post by:
I have an aspx page that generates a file on the fly for a person to download. It sends these http headers: Content-Type: text/plain Content-Disposition: attachment; filename="myfile.lic" ...
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
3
by: gdaniels | last post by:
Hi, I need some help. The routine below runs when a command button is clicked. A prompt appears to enter the directory path to the folder containing delimited text files to be imported into an...
6
by: sara | last post by:
I have what I think is a little strange...I have to get data from our payroll system into a specific format (fixed record length) as a .txt or .prn file only to upload to our 401k custodian. I...
1
by: Marty Klunk | last post by:
I have an Access97 data base where we are exporting records out to a text file that is then sent to a customer via EDI transmission. The problem I am having is that during the export process access...
5
by: Doogie | last post by:
Can anoyne tell me why this VBScript will create the file to Excel just fine, but the Excel file will not open up? I am saving it as a xlsx file instead of an xls one and I have the new version of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.