[AccessD] Querying the back end directly

John,

 The IN syntax is no different then having to maintain the .Connect string
for a table def. What your doing is simply using a connect string as part of
a SQL statement.

 So one way or another (tabledef or no), you are going to have to manage
that string.

Jim. 

-----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 11:27 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com