ODBC connect string

  • Thread starter Thread starter Tony Wainwright
  • Start date Start date
T

Tony Wainwright

Hi Guys

I am trying to get an Access XP database to read from an external data
source (Excel - I have tried TransferSpreadsheet but it doesn't work how I
want it). I am now experimenting with ODBC. From the help files I can make
a connection to the Excel file I want, but it keeps asking for the path to
the data file. I am already passing this using Ken Getz and Paul Litwin's
OpenDialog routines - published I think on Lebans's website - and would like
to use this as the OpenDialog. To prove the code works I am using the
following sample (strPath will eventually contain the OpenDialog routine).

strPath = "D:\Tony's\IT Solutions\Clients\Gojo\Spreadsheets\Import Soap Data
SEP 04 YTD.xls"
strConnect = "ODBC;DATABASE=" & strPath & ";DSN=Excel Files"
Set conSoap = wrkODBC.OpenConnection("Soap Data", dbDriverComplete, False,
strConnect)

Once VBA executes the 'Set conSoap = ....' line I get a Select Workbook
Dialog, then the code executes normally. How do I get this code to utilise
the OpenDialog routine rather than the Select Workbook dialog?

Cheers
Tony
 
what are you after in the excel file.
data?
why not link to the excel file through file>get extrenal
data>link tables. you can then use the excel file like an
access table.
-----Original Message-----
Hi Guys

I am trying to get an Access XP database to read from an external data
source (Excel - I have tried TransferSpreadsheet but it doesn't work how I
want it). I am now experimenting with ODBC. From the help files I can make
a connection to the Excel file I want, but it keeps asking for the path to
the data file. I am already passing this using Ken Getz and Paul Litwin's
OpenDialog routines - published I think on Lebans's website - and would like
to use this as the OpenDialog. To prove the code works I am using the
following sample (strPath will eventually contain the OpenDialog routine).

strPath = "D:\Tony's\IT
Click to expand...
Solutions\Clients\Gojo\Spreadsheets\Import Soap Data
 
Or just build a Jet SQL string, e.g.

SELECT *
FROM
[Excel 8.0;HDR=No;database=D:\Folder\File.xls;].[Sheet1$A10:E20]
;

and then use it in a QueryDef or to open a recordset.
 
How does that work John. I've tried pasting your select statement into VBA
and I get an error creating file message. I have replaced your path with
mine.
Tony

John Nurick said:
Or just build a Jet SQL string, e.g.

SELECT *
FROM
[Excel 8.0;HDR=No;database=D:\Folder\File.xls;].[Sheet1$A10:E20]
;

and then use it in a QueryDef or to open a recordset.

what are you after in the excel file.
data?
why not link to the excel file through file>get extrenal
data>link tables. you can then use the excel file like an
access table.
Click to expand...
Click to expand...
 
Something like this air code. Before running it, create a Select query
in the database called "qryXXX" or some such. It doesn't matter what
table or fields the query selects; we're going to modify it as needed.

Dim strFileSpec As String
Dim strXLRange As String

strFileSpec = "D:\Folder\File.xls" 'or whatever
strXLRange = "Sheet1$" 'or whatever

dbEngine(0)(0).QueryDefs("qryXXX").SQL = _
"SELECT *" _
& " FROM [Excel 8.0;HDR=No;database=" _
& strFileSpec & ";].[" & strXLRange & "];"

You can then use the query however you want. You may need to fine-tune
the SQL SELECT statement, e.g. if there's a header row with the field
names, or if you want to assign your own field names instead of the
default F1, F2..., or select only certain columns, e.g.
"SELECT F1 AS MyColumn1, F2 As MyColumn2, F4 As MyColumn3 "

For more information on this sort of thing, search groups.google.com for
posts by in microsoft.public.access.externaldata by Jamie Collins




How does that work John. I've tried pasting your select statement into VBA
and I get an error creating file message. I have replaced your path with
mine.
Tony

John Nurick said:
Or just build a Jet SQL string, e.g.

SELECT *
FROM
[Excel 8.0;HDR=No;database=D:\Folder\File.xls;].[Sheet1$A10:E20]
;

and then use it in a QueryDef or to open a recordset.

what are you after in the excel file.
data?
why not link to the excel file through file>get extrenal
data>link tables. you can then use the excel file like an
access table.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top