Trouble with ODBC selecting records from external mdb
Posted on 2001-08-09
This question has a couple of issues. The primary problem is as follows. I want to select records from an external mdb and create a querydef within the currentDB from those records. I have an existing DB and existing code that needs to be modified to allow the user to select records from either the currentDB or the old historic DB with historic records. Both databases are the same only with recent and old records. The recent/currentDB only has records for the past 3 months, the old database has the records from 4 months and older. I want to be able to use the same SQL string regardless of the datasource.
here's the pseudo code.
select case DBsource 'option btns
set DB = currentDB
set DB = opendatabase(location,true,false)
SQL = select orders.orderid,orders.customerid from orders
set QD = currentdb.createquerydef("query name",SQL)
Now depending on the case, I want to retrieve records from different locations. I was thinking of trying to use a pass-through query but I've been having trouble setting the connection string, this leads me to the second problem, but first. I was thinking of writting the SQL string to dynamically allow for different databases. Eg: select DB.orders.orderid, DB.orders.customerid from orders. this would pick records from what ever database the user selects and create the querydef in the currentDB
passthrough problem: I have created a file DSN. now in my passthrough query I select properties and in the connection string I click the elipses. This opens the odbc DSN window with the newly created DSN. When I select the DSN I get an error, "you can not use ODBC to import from,export to,or link to external jet or ISAM database table to your database."
What am I doing wrong.
I think a passthrough may do the trick but I cant get the darn thing to work.