By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,575 Members | 1,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,575 IT Pros & Developers. It's quick & easy.

Exporting text files without .txt extension

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
"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

P: n/a
..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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.