Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL query in VBA with QueryTable Object

Status
Not open for further replies.

magnaral

Programmer
Sep 29, 2004
11
NO
SQL query in VBA with QueryTable Object
Hello

I am trying to import selected data from a comma separated text file using the following method.


Sub PerformQuery()

Dim thisSheet As Worksheet
Set thisSheet = ThisWorkbook.Worksheets(2)

Dim ThisSQL
ThisSQL = "SELECT * "

Dim connString As String
connString = "TEXT;q:\arbeid\20040903.icm.95pc.dvf.txt"

Dim thisQT As QueryTable
Set thisQT = thisSheet.QueryTables.Add(Connection:=connString, Destination:=Range("A1"))

thisQT.BackgroundQuery = False

thisQT.TextFileCommaDelimiter = True
thisQT.TextFileParseType = xlDelimited
thisQT.TextFileStartRow = 3

thisQT.sql = ThisSQL

thisQT.Refresh

End Sub

The problem is that I get this error message: "Runtime Error '1004': Incomplete datasource"

What are the name of the tables/coloumns/fields in the text file I need to refer to in the Sql query?


With best regards,

M. Alexander Kleiven
 
Have you tried MS-Query and the macro-recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 

I agree with PHV - macro recording and then observng & modifying the code will help you the most.

Your SQL String needs to include From TableName probably something like this...
Code:
ThisSQL = "SELECT * From q:\arbeid\20040903.icm.95pc "


Skip,

[glasses] [red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [tongue]
 
The MS Query does not find any tables at all, although I defined them when created the data source from MS Text Driver. The macro recorder did not record anything from this creation procedure.

Alexander
 

Did you try to modify your sql as advised?

Skip,

[glasses] [red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [tongue]
 
Yes, I still get the same error message.

I am able to manually create a new text-based datasource in Excel, but not able to find any fields in it when I open MS Query.

Alexander
 
Unfortunately, I have ZERO experience with TEXT source as opposed to ODBC source queries.

If you can get your data into Excel, then SAVE AS an .xls (Excel) workbook and then use the Excel Files data source to create your query. Be sure that your table has one heading row.

Skip,

[glasses] [red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [tongue]
 
The problem is that one of the text files has over 400000 rows, and Excel can't handle that. I want a selection of these into Excel.

Alexander
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top