SELECT INTO to text file

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
Thanks, I thought it would be something like that, but the quoted thread is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?

RBS
 
RB said:
Thanks, I thought it would be something like that, but the quoted thread is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?
Click to expand...

You don't need me to tell you how to create an ADO connection to a
non-existant Excel workbook and execute the SQL, do you <g>?

Sub JustFourLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;" & _
"Data Source=C:\doesnotexist.xls"
con.Execute _
"SELECT fname, minit, lname" & _
" INTO [Text;Database=C:\My Folder\;].MyFile#txt FROM
[ODBC;Driver={SQL" & _
" Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee;
"
End Sub

Jamie.

--
 
RB said:
Thanks, I thought it would be something like that, but the quoted thread is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?
Click to expand...

You don't need me to tell you how to create an ADO connection to a
non-existant Excel workbook and execute the SQL, do you <g>?

Sub JustFourLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;" & _
"Data Source=C:\doesnotexist.xls"
con.Execute _
"SELECT fname, minit, lname" & _
" INTO [Text;Database=C:\My Folder\;].MyFile#txt FROM
[ODBC;Driver={SQL" & _
" Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee;
"
End Sub

Jamie.

--
 
In Access

Dim Db AS DAO.Database

Set Db = Access.CurrentDb
Db.Execute "DROP TABLE [Text;Database=C:\My Folder\;].[MyFile#txt]"
Db.Execute "SELECT " & _
"fname, minit, lname " & _
"INTO [Text;Database=C:\My Folder\;].MyFile#txt " & _
"FROM " & _
"[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee"

Rewriting to ADO I leave as an exercise

Pieter
 
In Access

Dim Db AS DAO.Database

Set Db = Access.CurrentDb
Db.Execute "DROP TABLE [Text;Database=C:\My Folder\;].[MyFile#txt]"
Db.Execute "SELECT " & _
"fname, minit, lname " & _
"INTO [Text;Database=C:\My Folder\;].MyFile#txt " & _
"FROM " & _
"[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee"

Rewriting to ADO I leave as an exercise

Pieter
 
Do I indeed have to put a non-existent workbook here?
Click to expand...

How could you do that <g>? My point is, the workbook doesn't have to
exist.

You need to establish a Jet connection. I use an Excel workbook as the
data source because it need not exist. If it doesn't, a Jet connection
is still established and no workbook is created (unless you do
something to cause it to e.g. 'CREATE TABLE...'). If it does exist,
make sure it is not open to avoid the Excel ADO memory leak bug.

Take a look at the article up thread, it may give you the details your
require about connection strings, data types, etc.

HTH,
Jamie.

--
 
Do I indeed have to put a non-existent workbook here?
Click to expand...

How could you do that <g>? My point is, the workbook doesn't have to
exist.

You need to establish a Jet connection. I use an Excel workbook as the
data source because it need not exist. If it doesn't, a Jet connection
is still established and no workbook is created (unless you do
something to cause it to e.g. 'CREATE TABLE...'). If it does exist,
make sure it is not open to avoid the Excel ADO memory leak bug.

Take a look at the article up thread, it may give you the details your
require about connection strings, data types, etc.

HTH,
Jamie.

--
 
Right, I see now what you mean.
I need to connect to an Interbase database and output to text.
I have the connection string for the connection to Interbase.
I think I have all the needed information now and I think I can work it
out.
It is just that all this non-standard ADO data work isn't that well
documented.

RBS
 
Right, I see now what you mean.
I need to connect to an Interbase database and output to text.
I have the connection string for the connection to Interbase.
I think I have all the needed information now and I think I can work it
out.
It is just that all this non-standard ADO data work isn't that well
documented.

RBS
 
Sorry, I still don't get this.
What should I put at:
"Data Source=C:\doesnotexist.xls"

My datasource is an Interbase .gdb file, but putting that
in place of C:\doesnotexist.xls doesn't work:
External table is not in expected format.

Not sure now I can use the text driver here.

RBS


Jamie Collins said:
RB said:
Thanks, I thought it would be something like that, but the quoted thread
is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?
Click to expand...

You don't need me to tell you how to create an ADO connection to a
non-existant Excel workbook and execute the SQL, do you <g>?

Sub JustFourLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;" & _
"Data Source=C:\doesnotexist.xls"
con.Execute _
"SELECT fname, minit, lname" & _
" INTO [Text;Database=C:\My Folder\;].MyFile#txt FROM
[ODBC;Driver={SQL" & _
" Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee;
"
End Sub

Jamie.
Click to expand...
 
Sorry, I still don't get this.
What should I put at:
"Data Source=C:\doesnotexist.xls"

My datasource is an Interbase .gdb file, but putting that
in place of C:\doesnotexist.xls doesn't work:
External table is not in expected format.

Not sure now I can use the text driver here.

RBS


Jamie Collins said:
RB said:
Thanks, I thought it would be something like that, but the quoted thread
is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?
Click to expand...

You don't need me to tell you how to create an ADO connection to a
non-existant Excel workbook and execute the SQL, do you <g>?

Sub JustFourLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;" & _
"Data Source=C:\doesnotexist.xls"
con.Execute _
"SELECT fname, minit, lname" & _
" INTO [Text;Database=C:\My Folder\;].MyFile#txt FROM
[ODBC;Driver={SQL" & _
" Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee;
"
End Sub

Jamie.
Click to expand...
 
Back
Top