table from .ADP to tab delimited text file

hello cdma enthusiasts. long time no speak. i have an .adp which is
connected to a sql 2k back end from which i need to export a table to
a tab delimited text file. the export text wizard apparently doesn't
allow you to store export specs (makes sense since it has no local
storage) so i'm not sure the TransferText method is going to do the
trick. i need to do this in VBA. any suggestions?

it's been a while since i've been active in cdma and the last i
remember there weren't a lot of people discussing ADPs. i think Steve
Jorgenson and Lyle were using them quite a bit so i'm hoping someone
has encountered this. right now i'm automating excel to get this done
which works but is sorta clumsy. TIA.

Ted

Re: table from .ADP to tab delimited text file

On Mon, 3 Dec 2007 18:45:45 -0800 (PST), Ted Theo
wrote:

Au contraire, the export wizard *does* allow you to save an export
specification. Just step through the export wizard and you'll get the
option to save.
If your requirements are modest, you can also use DoCmd.TransferText
without an export specification.

Lyle is still here. Haven't heard from Steve in a while.

-Tom.


>hello cdma enthusiasts. long time no speak. i have an .adp which is
>connected to a sql 2k back end from which i need to export a table to
>a tab delimited text file. the export text wizard apparently doesn't
>allow you to store export specs (makes sense since it has no local
>storage) so i'm not sure the TransferText method is going to do the
>trick. i need to do this in VBA. any suggestions?
>
>it's been a while since i've been active in cdma and the last i
>remember there weren't a lot of people discussing ADPs. i think Steve
>Jorgenson and Lyle were using them quite a bit so i'm hoping someone
>has encountered this. right now i'm automating excel to get this done
>which works but is sorta clumsy. TIA.
>
>Ted

Re: table from .ADP to tab delimited text file

hmm, i must be doing something wrong then because i'm not given the
option to save the specs. you are talking about an ADP correct?
since data projects don't have any local storage where would the specs
be stored? i've seen a few references to using a FileSystemObject but
haven't run across any sample code. again, i can get there by dumping
it to excel and then using automation to save it out but that just
doesn't seem like the best approach.

good to hear some of the usual suspects are still around. it's a
shame steve isn't. he really knew ADPs. i appreciate any feedback
you guys may have.

On Dec 3, 10:35 pm, Tom van Stiphout wrote:
> On Mon, 3 Dec 2007 18:45:45 -0800 (PST), Ted Theo
> wrote:
>
> Au contraire, the export wizard *does* allow you to save an export
> specification. Just step through the export wizard and you'll get the
> option to save.
> If your requirements are modest, you can also use DoCmd.TransferText
> without an export specification.
>
> Lyle is still here. Haven't heard from Steve in a while.
>
> -Tom.
>
> >hello cdma enthusiasts. long time no speak. i have an .adp which is
> >connected to a sql 2k back end from which i need to export a table to
> >a tab delimited text file. the export text wizard apparently doesn't
> >allow you to store export specs (makes sense since it has no local
> >storage) so i'm not sure the TransferText method is going to do the
> >trick. i need to do this in VBA. any suggestions?
>
> >it's been a while since i've been active in cdma and the last i
> >remember there weren't a lot of people discussing ADPs. i think Steve
> >Jorgenson and Lyle were using them quite a bit so i'm hoping someone
> >has encountered this. right now i'm automating excel to get this done
> >which works but is sorta clumsy. TIA.
>
> >Ted

Re: table from .ADP to tab delimited text file

On Mon, 3 Dec 2007 20:09:57 -0800 (PST), Ted Theo
wrote:

I was in an A2002/2003 format ADP in A2007.
I'm assuming the specs are stored in one of the sys* tables on SQL
Server.

-Tom.


>hmm, i must be doing something wrong then because i'm not given the
>option to save the specs. you are talking about an ADP correct?
>since data projects don't have any local storage where would the specs
>be stored? i've seen a few references to using a FileSystemObject but
>haven't run across any sample code. again, i can get there by dumping
>it to excel and then using automation to save it out but that just
>doesn't seem like the best approach.
>
>good to hear some of the usual suspects are still around. it's a
>shame steve isn't. he really knew ADPs. i appreciate any feedback
>you guys may have.
>
>On Dec 3, 10:35 pm, Tom van Stiphout wrote:
>> On Mon, 3 Dec 2007 18:45:45 -0800 (PST), Ted Theo
>> wrote:
>>
>> Au contraire, the export wizard *does* allow you to save an export
>> specification. Just step through the export wizard and you'll get the
>> option to save.
>> If your requirements are modest, you can also use DoCmd.TransferText
>> without an export specification.
>>
>> Lyle is still here. Haven't heard from Steve in a while.
>>
>> -Tom.
>>
>> >hello cdma enthusiasts. long time no speak. i have an .adp which is
>> >connected to a sql 2k back end from which i need to export a table to
>> >a tab delimited text file. the export text wizard apparently doesn't
>> >allow you to store export specs (makes sense since it has no local
>> >storage) so i'm not sure the TransferText method is going to do the
>> >trick. i need to do this in VBA. any suggestions?
>>
>> >it's been a while since i've been active in cdma and the last i
>> >remember there weren't a lot of people discussing ADPs. i think Steve
>> >Jorgenson and Lyle were using them quite a bit so i'm hoping someone
>> >has encountered this. right now i'm automating excel to get this done
>> >which works but is sorta clumsy. TIA.
>>
>> >Ted
>
>

Re: table from .ADP to tab delimited text file

i probably should have specified that i'm using 2003 but it doesn't
work for me in 2000 or 2002. i don't think that access doesn't add
any tables to the SQL database you use as your data source. i've also
seen several posts w/people saying they can't save im/ex specs in ADPs
so maybe that's something new in 2007.

maybe automating Excel to save a table as a tab delimited file isn't
as clumsy as i thought. this app has a few dozen users and they all
have Excel. i was just hoping for a better way.

On Dec 4, 8:01 am, Tom van Stiphout wrote:
> On Mon, 3 Dec 2007 20:09:57 -0800 (PST), Ted Theo
> wrote:
>
> I was in an A2002/2003 format ADP in A2007.
> I'm assuming the specs are stored in one of the sys* tables on SQL
> Server.
>
> -Tom.
>
> >hmm, i must be doing something wrong then because i'm not given the
> >option to save the specs. you are talking about an ADP correct?
> >since data projects don't have any local storage where would the specs
> >be stored? i've seen a few references to using a FileSystemObject but
> >haven't run across any sample code. again, i can get there by dumping
> >it to excel and then using automation to save it out but that just
> >doesn't seem like the best approach.
>
> >good to hear some of the usual suspects are still around. it's a
> >shame steve isn't. he really knew ADPs. i appreciate any feedback
> >you guys may have.
>
> >On Dec 3, 10:35 pm, Tom van Stiphout wrote:
> >> On Mon, 3 Dec 2007 18:45:45 -0800 (PST), Ted Theo
> >> wrote:
>
> >> Au contraire, the export wizard *does* allow you to save an export
> >> specification. Just step through the export wizard and you'll get the
> >> option to save.
> >> If your requirements are modest, you can also use DoCmd.TransferText
> >> without an export specification.
>
> >> Lyle is still here. Haven't heard from Steve in a while.
>
> >> -Tom.
>
>
>> >hello cdma enthusiasts. long time no speak. i have an .adp
which is
> >> >connected to a sql 2k back end from which i need to export a table to
> >> >a tab delimited text file. the export text wizard apparently doesn't
> >> >allow you to store export specs (makes sense since it has no local
> >> >storage) so i'm not sure the TransferText method is going to do the
> >> >trick. i need to do this in VBA. any suggestions?
>
> >> >it's been a while since i've been active in cdma and the last i
> >> >remember there weren't a lot of people discussing ADPs. i think Steve
> >> >Jorgenson and Lyle were using them quite a bit so i'm hoping someone
> >> >has encountered this. right now i'm automating excel to get this done
> >> >which works but is sorta clumsy. TIA.
>
> >> >Ted

Re: table from .ADP to tab delimited text file

On Dec 3, 11:09 pm, Ted Theo wrote:
> hmm, i must be doing something wrong then because i'm not given the
> option to save the specs. you are talking about an ADP correct?
> since data projects don't have any local storage where would the specs
> be stored? i've seen a few references to using a FileSystemObject but
> haven't run across any sample code. again, i can get there by dumping
> it to excel and then using automation to save it out but that just
> doesn't seem like the best approach.
>
> good to hear some of the usual suspects are still around. it's a
> shame steve isn't. he really knew ADPs. i appreciate any feedback
> you guys may have.
>
> On Dec 3, 10:35 pm, Tom van Stiphout wrote:
>
> > On Mon, 3 Dec 2007 18:45:45 -0800 (PST), Ted Theo
> > wrote:
>
> > Au contraire, the export wizard *does* allow you to save an export
> > specification. Just step through the export wizard and you'll get the
> > option to save.
> > If your requirements are modest, you can also use DoCmd.TransferText
> > without an export specification.
>
> > Lyle is still here. Haven't heard from Steve in a while.
>
> > -Tom.
>
> > >hello cdma enthusiasts. long time no speak. i have an .adp which is
> > >connected to a sql 2k back end from which i need to export a table to
> > >a tab delimited text file. the export text wizard apparently doesn't
> > >allow you to store export specs (makes sense since it has no local
> > >storage) so i'm not sure the TransferText method is going to do the
> > >trick. i need to do this in VBA. any suggestions?
>
> > >it's been a while since i've been active in cdma and the last i
> > >remember there weren't a lot of people discussing ADPs. i think Steve
> > >Jorgenson and Lyle were using them quite a bit so i'm hoping someone
> > >has encountered this. right now i'm automating excel to get this done
> > >which works but is sorta clumsy. TIA.
>
> > >Ted

Access Data Projects don't have any local storage is a bit of a
stretch. They are closely aligned with ADO. ADO provides the Save and
Open Methods of the Recordset which provide very powerful local
storage.

To your original post:

Public Sub SaveFile( _
ByVal SQL$, _
ByVal Path$)
Dim FileNumber%
On Error GoTo SaveFileErr
FileNumber = FreeFile()
Open Path For Output As #FileNumber
Print #FileNumber, _
CurrentProject.Connection.Execute(SQL) _
.GetString(adClipString, , vbTab, vbNewLine, "")
Close FileNumber
SaveFileExit:
Close
Exit Sub
SaveFileErr:
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume SaveFileExit:
End Sub

Sub test()
SaveFile _
"SELECT * FROM [4060148Accounts]", _
"C:\Documents and Settings\lyle fairfield\desktop\temp.txt"
End Sub

This contents (less lines with private information) of the file:

2 Computer Hardware
3 FFDBA -1
4 Lyle Fairfield -1
5 Meals & Entertainment
6 Computer Software
7 Communications
8 Transportation
9 Accommodation
10 Financial Fees
11 Consulting Fees
12 Payroll
13 Interest
14 Furniture
15 Office Supplies
16 Revenue
17 Balance (Initial)
22 Canada Customs And Revenue
23 Goods For Resale
26 Income Tax - Federal
27 Income Tax - Provincial
28 Rent

Notes: This is one of the few situations where I am careful to write
my own error-handling. In days of yore I was taught to close low-level
file handles without fail. So my error handling tries to ensure that
it is done. In VBA, Close without parameters closes all such handles,
TTBOMK.

Re: table from .ADP to tab delimited text file

that looks very simple. i was pretty sure there was a better way. i
really appreciate it!

On Dec 4, 9:05 am, lyle wrote:
> On Dec 3, 11:09 pm, Ted Theo wrote:
>
>
>
>
>
> > hmm, i must be doing something wrong then because i'm not given the
> > option to save the specs. you are talking about an ADP correct?
> > since data projects don't have any local storage where would the specs
> > be stored? i've seen a few references to using a FileSystemObject but
> > haven't run across any sample code. again, i can get there by dumping
> > it to excel and then using automation to save it out but that just
> > doesn't seem like the best approach.
>
> > good to hear some of the usual suspects are still around. it's a
> > shame steve isn't. he really knew ADPs. i appreciate any feedback
> > you guys may have.
>
> > On Dec 3, 10:35 pm, Tom van Stiphout wrote:
>
> > > On Mon, 3 Dec 2007 18:45:45 -0800 (PST), Ted Theo
> > > wrote:
>
> > > Au contraire, the export wizard *does* allow you to save an export
> > > specification. Just step through the export wizard and you'll get the
> > > option to save.
> > > If your requirements are modest, you can also use DoCmd.TransferText
> > > without an export specification.
>
> > > Lyle is still here. Haven't heard from Steve in a while.
>
> > > -Tom.
>
> > > >hello cdma enthusiasts. long time no speak. i have an .adp which is
> > > >connected to a sql 2k back end from which i need to export a table to
> > > >a tab delimited text file. the export text wizard apparently doesn't
> > > >allow you to store export specs (makes sense since it has no local
> > > >storage) so i'm not sure the TransferText method is going to do the
> > > >trick. i need to do this in VBA. any suggestions?
>
> > > >it's been a while since i've been active in cdma and the last i
> > > >remember there weren't a lot of people discussing ADPs. i think Steve
> > > >Jorgenson and Lyle were using them quite a bit so i'm hoping someone
> > > >has encountered this. right now i'm automating excel to get this done
> > > >which works but is sorta clumsy. TIA.
>
> > > >Ted
>
> Access Data Projects don't have any local storage is a bit of a
> stretch. They are closely aligned with ADO. ADO provides the Save and
> Open Methods of the Recordset which provide very powerful local
> storage.
>
> To your original post:
>
> Public Sub SaveFile( _
> ByVal SQL$, _
> ByVal Path$)
> Dim FileNumber%
> On Error GoTo SaveFileErr
> FileNumber = FreeFile()
> Open Path For Output As #FileNumber
> Print #FileNumber, _
> CurrentProject.Connection.Execute(SQL) _
> .GetString(adClipString, , vbTab, vbNewLine, "")
> Close FileNumber
> SaveFileExit:
> Close
> Exit Sub
> SaveFileErr:
> MsgBox Err.Description, vbCritical, "Error " & Err.Number
> Resume SaveFileExit:
> End Sub
>
> Sub test()
> SaveFile _
> "SELECT * FROM [4060148Accounts]", _
> "C:\Documents and Settings\lyle fairfield\desktop\temp.txt"
> End Sub
>
> This contents (less lines with private information) of the file:
>
> 2 Computer Hardware
> 3 FFDBA -1
> 4 Lyle Fairfield -1
> 5 Meals & Entertainment
> 6 Computer Software
> 7 Communications
> 8 Transportation
> 9 Accommodation
> 10 Financial Fees
> 11 Consulting Fees
> 12 Payroll
> 13 Interest
> 14 Furniture
> 15 Office Supplies
> 16 Revenue
> 17 Balance (Initial)
> 22 Canada Customs And Revenue
> 23 Goods For Resale
> 26 Income Tax - Federal
> 27 Income Tax - Provincial
> 28 Rent
>
> Notes: This is one of the few situations where I am careful to write
> my own error-handling. In days of yore I was taught to close low-level
> file handles without fail. So my error handling tries to ensure that
> it is done. In VBA, Close without parameters closes all such handles,
> TTBOMK.- Hide quoted text -
>
> - Show quoted text -