Pass through query to linked table

[davidpayten]

Hi All,

I am using a BI tool to update data in a database via an ODBC connection into Access.
The problem is that I am trying to link to a linked table. Each time I link to it the data doesnot change unless I update the table.

So the question is: "Is it possible to create a query that links to the a "Linked table" and refreshes it?? the table links of a txt file.

Any help would be much appreciated.

Cheers

Dave

 

[SydneyGeek]

Pass through queries are by definition, read-only. I used them extensively for a system I built where Access was the middleware between 2 SQL-based systems. In that circumstance Pass-through was fine. If you want read-write you need to link the tables via ODBC.

Denis

 

[davidpayten]

Hi there,

we have the ODBC connection setup to pull from Access query, and points to the "Linked table". When a new text file is dropped into the directory where the linked table looks to grab the data, it does not update and the numbers ( ie it does not refresh - i need to refresh it each time and there appears not to be a macro that does the job for me).

When I refresh the link it works, but would like this to automatically refresh the table when I access it when I open / access it via ODBC.

Any ideas?

Cheers

Dave

 

[SydneyGeek]

When the database opens...

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
   If Len(tdf.Connect) > 0 Then
      tdf.RefreshLink
   End If
Next tdf

Set dbs = Nothing

Code like that could be put into the Open event of the first form that opens in the database. Alternatively, you could place that in a module (as a Function) and call that function when the database opens, using the autoexec macro.

Denis

 

[davidpayten]

Hi,

Thanks for your SydneyGeek. Will give that a try and let you know how I go.

Cheers

Dave