[AccessD] Querying the back end directly

If I recall correctly Access won't let you connect to an Access BE via ODBC.

What I do in this situation is use ordinary linked tables and run a relink routine when the BE 
location changes.

Here's an example from a system which can run from a local BE, a server based BE or an 
SQL Server instance:

Option Compare Database
Option Explicit

Const strBEName = "NECDecision_BE.mdb"
Const strServerBEDir = "\\DotSQL\DataApps"
Const strSQLConnect = "ODBC;Description=DoT Policy Information System;DRIVER=SQL 
Server;SERVER=DOTSQL;APP=Microsoft Data Access 
Components;DATABASE=DoTPolicy;Trusted_Connection=Yes"
 

Function ConnectSQL() As Long
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strSQLConnect, False
    End If
Next
ConnectSQL = True
End Function

Function ConnectBELocal() As Boolean
Dim tdf As TableDef
If Dir$(CurrentProject.Path & "\NECDecisions_BE.mdb") < " " Then
     MsgBox "Data file NECDecisions_Be.mdb missing! It must be in the same directory as 
this application file.", vbCritical, "ConnectBELocal Failed!"
     ConnectBELocal = False
     Exit Function
 End If
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, CurrentProject.Path & "\NECDecisions_BE.mdb", True
    End If
Next
ConnectBELocal = True
End Function

Function ConnectServerBE() As Long
Dim tdf As TableDef
If Dir$(strServerBEDir & "\NECDecisions_BE.mdb") < " " Then
     MsgBox "Data file NECDecisions_Be.mdb missing! It must be in the " & strServerBEDir & 
".", vbCritical, "ConnectBELocal Failed!"
     ConnectServerBE = False
     Exit Function
 End If
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strServerBEDir & "\NECDecisions_BE.mdb", True
    End If
Next
ConnectServerBE = True
End Function

Function renewlink(tablename As String, datafile As String, AccessDb As Boolean) As Long
On Error Resume Next
DoCmd.DeleteObject acTable, tablename
On Error GoTo 0
Select Case AccessDb
Case True
DoCmd.TransferDatabase acLink, "Microsoft Access", datafile, acTable, tablename, 
tablename, False
Case False
DoCmd.TransferDatabase acLink, "ODBC Database", datafile, acTable, tablename, 
tablename, False
End Select
End Function


On 26 Apr 2013 at 11:26, John W Colby wrote:

> 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
>