Exporting to a text file

[Anthony Greenidge]

I have an Access 2K ADP connected to SQL backend. As
part of a program that i am working on, the exportation
of information to a tab delimited, no text identifier
file is required. I created a Schema.ini file with the
config set at TabDelimited and then issued the
appropriate docmd. to create the file. However, each
time the procedure is run, the schema.ini file is
overwritten and replaced with a CSV, comma delimited
version.

My ? is, how do i get my desired output?

TIA to all that respond.

 

[BJ Freeman]

I gave up on the "easy way".
I take a recordset and use VBA to create my exports.
here is an example:
Sub GenerateUpload()
Dim RS As New ADODB.Recordset
Dim tmpcur As Currency
Open "Upload_" & Format(Now(), "YYDDMM") & "_full.txt" For Output As #1
'get all avalibe
Set RS = CurrentProject.Connection.Execute("ADP_export")
If RS.BOF = False Or RS.EOF = False Then '
'Print #1, """"; "item-is-marketplace"; """"; ","; """"; "product-id";
""""; ","; """"; "product-id-type"; """"; ","; """"; "item-condition"; """";
","; """"; "sku"; """"; ","; """"; "Price"; """"
Print #1, "item-is-marketplace"; Chr(9); "product-id"; Chr(9);
"product-id-type"; Chr(9); "item-condition"; Chr(9); "sku"; Chr(9); "Price";
""""
Do
DoEvents
'Print #1, """"; "Y"; """"; ","; """"; RS![product-id]; """";
","; RS![price]; ","; """"; RS![Condition]; """"; ","; """"; RS![Notes];
""""; ","; RS![Quantity]
Print #1, "Y"; Chr(9); RS![COL001]; Chr(9); "2"; Chr(9); "11";
Chr(9); RS![COL001]; Chr(9); RS![Quantity]
RS.MoveNext
Loop Until RS.EOF
End If
RS.Close
Close #1

End Sub

 

[Kevin3NF]

I may be nuts, but I find it easier to use SQL Server's DTS processes for
this.

 

[BJ Freeman]

Good point..
Some of my clients, though, need to have an export on their desktop.
is there a way to use dTS to do this, across the internet?

 

[Anthony Greenidge]

That sounds like a great idea. How dow i get the DTS
process to run from code? I have never done that before.

 

[Kevin3NF]

You mean have DTS send the result file to an internet user, or to run a DTS
package from the internet?

 

[Kevin3NF]

Use the dtsrun utility (possibly in a shell command, or calling XP_cmdshell
from the master SQL Server db), specifying the servername and other
pertinant prameters. See SQL Server Books online for more about dtsrun
utility.

 

[BJ Freeman]

all my client that use the ms-sql, connect via an ADP, over the internet.
so when they need an export of a certain flavor, I need to code it. to run
on their ADP.
Now if I could write a Stored procedure, that would be called from VBA code
in the ADP, to Run the DTS, on the server, then use the VBA code to FTP it
to their desktop that might work.

 

[Kevin3NF]

You can write a stored procedure to call master..xp_cmdshell, in which you
run the DTSRUN utility, which calls a package to do whatever you need done,
and place that file in an FTP folder. Unfortunately, you would still need
VBA to go get that file...which probably defeats the purpose of the DTS,
which was to avoid the code...I think.

 

[Anthony Greenidge]

Works like a dream now..
Thanks a mil..

-----Original Message-----
Use the dtsrun utility (possibly in a shell command, or calling XP_cmdshell
from the master SQL Server db), specifying the servername and other
pertinant prameters. See SQL Server Books online for more about dtsrun
utility.

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm






.

 

[Van T. Dinh]

I don't know but DTS fails for me. Even a simple DTS to import all Tables
from one Database to another empty database on the same MS SQL Server 2K
using the DTS (Wizard?) via EM failed half way through.

And I tried it 3 times reading all the options carefully!

Eventually, I backed up the Source Database and restored the back-up using a
new database name!

Any possible cause???

 

[Van T. Dinh]

Thanks for the reply, Kevin.

"Failed to import {TableName}" but 3 different Table names in 3 attempts.
When I looked into the destination database, nothing was actually imported
even though the DTS ran for about 5 mins each time.

I was actually trying to make a copy of the live database (but no other
users connected to the database at that time) so that I can do some testing.

 

[Kevin3NF]

When you double-click on the table that failed in the DTS results window,
you should get more information.

 

[Van T. Dinh]

Thanks, Kevin

In the 3 original attempts, I didn't even get the DTS Results window.

However, after I restored from the back-up of the live database to the test
database, I found some Tables missing in the test database today (I start
worrying about MS-SQL backups now) even though they are fine in the live
database. I tried to import these Tables. I did get the DTS Results window
but DTS wasn't happy about the indices of the Tables. It turned out these
Tables were upsized from Access (remembering that Access got a habit of
creating 2 indices for the PK Field) and the upsizing gave some weird names
for the indices, some even look like Class ID. Somehow, when I fixed the
indices in the live database, the Tables were imported OK from the live
database to the test database.