[AccessD] Querying the back end directly

Dan,

Yes, I am trying to connect to an access BE.  The problem with the syntax you mention is that in my 
current environment we have four valid locations to open and work on the database from, dev, qa, 
user testing and production.  Each has its own file storage location and the locations are just a 
mish mash of server name / path info.  So I reallly don't want to get into hard coding thhe location 
of the access BE directly in the sql string because then I have to deal with editing that path part 
when the application is given to QA or user test or production.

ODBC type of queries can have the path in the ODBC Connect String property and I just change the 
server name.  Accessing a file directly like this doesn't use an ODBC connect string it embeds the 
file path and name out in thhe sql statement.

It works, and if it won't move it is fine, but in my case it becomes ugly.

John W. Colby

Reality is what refuses to go away
when you do not believe in it

On 4/26/2013 10:18 AM, Dan Waters wrote:
> Hi John,
>
> Are you trying to connect to an Access BE?
>
> If so, the syntax is this:
>
> stg = "SELECT Field1, Field2 FROM tblMain IN '" & stgBEFullPath & "'"
>
> stgBEFullPath is a variable for the full path to the Access BE, which of
> course you can change as needed.  I believe from 'old memory' that the speed
> difference compared to a normal link to an Access BE wasn't much if
> anything.  Anyway, a test would be easy.
>
> See this: http://msdn.microsoft.com/en-us/library/bb177907.aspx
> And
> http://blogs.office.com/b/microsoft-access/archive/2009/03/27/accessing-exte
> rnal-data-using-the-in-clause.aspx
>
> According to these sites, you can also use IN with a variety of external
> types of databases, not just Access tables.
>
> Good Luck!
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W Colby
> Sent: Friday, April 26, 2013 7:41 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Querying the back end directly
>
> I know that queries can be constructed to use a "SELECT FROM
> ExternalFileName.Table" kind of syntax.  In other words, not linking a table
> out in the BE but in the query referencing the external database (or
> spreadsheet etc) and then a table inside of that.
>
> First of all I need to know whether that is faster, slower or no difference.
>
> I need to be able to modify that to modify the location of the back end
> file.  While I can open the query def and search through the sql for the
> file name I would really prefer not to.  Is it possible to use this kind of
> thing using an ODBC connect string?  Or does an ODBC connect string assume a
> database server / engine at the other end?
>
> --
> John W. Colby
>
> Reality is what refuses to go away
> when you do not believe in it
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>