export from Sql Server table to delimited text ";"

[sal21]

I need a suggestion to export a seperated fileds from sql table in a text file with separated ";"...
Please via VBA for excel and ADO.
I just know the name of server, and name of database and nema of table.

Note:
I have a trusted account. I just have a opened conn to the sql server and database active and bamed MYconn.

[HansV]

Declare an ADODB recordset named rst, and open the recordset on the table or SQL string that you want to export. You can then use code like this:

Code: Select all

Dim strList As String
Dim f As Integer

' Get string containing the records
strList = rst.GetString(Stringformat:=adClipString, _
  ColumnDelimeter:=";", RowDelimeter:=vbCrLf)
' Open text file
f = FreeFile
Open "Test.txt" For Output As #f
' Write records to text file
Print #f, strList
' Close text file
Close #f

[sal21]

Declare an ADODB recordset named rst, and open the recordset on the table or SQL string that you want to export. You can then use code like this:

Code: Select all

Dim strList As String
Dim f As Integer

' Get string containing the records
strList = rst.GetString(Stringformat:=adClipString, _
  ColumnDelimeter:=";", RowDelimeter:=vbCrLf)
' Open text file
f = FreeFile
Open "Test.txt" For Output As #f
' Write records to text file
Print #f, strList
' Close text file
Close #f

Tested... but getstring causes not enought memory, into the sql table are 41 fields and approx 3/4 milions of records. In otheer case a need to export from sql table not all field but only 21 fields

[HansV]

You could specify an SQL string that selects the fields you need instead of the table name. But with that many records, you'll still run out of memory.

I don't think there is a really good way to do this from Excel with so much data. I'd set up an export job on SQL Server.

[sal21]

You could specify an SQL string that selects the fields you need instead of the table name. But with that many records, you'll still run out of memory.

I don't think there is a really good way to do this from Excel with so much data. I'd set up an export job on SQL Server.

tks for suggestion... but not have access on the sql server...
And this prob still me destroied my mind...

In other case i dont want to store the sql rset in excle sheets but i really store the sql rset into access table.
I have tested with a simple code to save from sql table into access table, similar rst.addnew... rst.update during the loop.
But this choice make a very very large time.
I friend suggest, to speed up operation, to store the sql rset into the txt file and after import into acces table...

[HansV]

You should ask the database administrator to set up the export job for you.

[sal21]

You should ask the database administrator to set up the export job for you.

the admin is a "Gosth", not possible to contact it

[HansV]

There's always a real person who manages a SQL Server database.