Using MS Access 2007 I would like to retrieve only part of an ODBC table. I can import the whole table in Access but I don't need all of it and it would be a waste of space and performance to store the whole table when I only need certain columns.
In Excel I wrote a SQL query that let me retrieve only the part I'm interested in. What I'd like to know is: is it possible to import only the result of a SQL query in Access or do I have to retrieve the whole table and then run the query on it?
Is it possible using built-in Access module or should I turn to VBA?
Edit: Basically I would like to run the ODBC data connection below (currently used in Excel) in Access.
Connection string:
DSN=BLA1;
UID=BLA2;
DBQ=BLA3;
PWD=BLA4;
DBA=W;
APA=T;
EXC=F;
FEN=T;
QTO=T;
FRC=10;
FDL=10;
LOB=T;
RST=T;
GDE=F;
FRL=F;
BAM=IfAllSuccessful;
MTS=F;
MDI=F;
CSR=F;
FWC=F;
PFC=10;
TLO=0;
Command string:
SELECT *
FROM TEST TEST
WHERE (TEST.DATE_STAMP=?)
When I try to link the database I get the error The database engine can't find 'WTD.DATAPOINT_5/1000'. Make sure it is a valid parameter or alias name, that it doesn't include characters or punctuation, and that the name isn't too long. but when I use the Excel database connection I get no error and everything is updated.
You don't need to import the whole table. You could link to the ODBC table and then run a make-table query against that linked table to copy in only the rows and columns that you need.