Dynamic linking of FoxPro tables

[wisenickel]

Hello all,

I'm looking for some sample VBA code that dynamically creates a link to Visual FoxPro 9 table. Our group has a number of end user FoxPro applications, and as FoxPro is reaching the end of its life in January 2015, we need to replace it.

A lot of the processing we do uses tables with a date embedded in the name, e.g. MyData_20131211.dbf. We'd like to be able to let our users to use Access queries that point to these tables without having to manually create the ODBC link each day. Is there a way to set up a link once, then use VBA code to dynamically change the table it points to?

For example, we set up an ODBC link table to MyData_20131211.dbf, and rename the link table in Access to MyData_Today. Then tomorrow, the VBA code would change the link to point to MyData_20131212.dbf.

An alternative would be to dynamically recreate the link each day.

As the tables are large, we don't want to import them into Access if we don't have to.

Any help would be appreciated, or if you can think of any other way of doing this, that would be great.

Thanks.

 

[AlexHedley]

http://p2p.wrox.com/access/32446-make-linked-table-vba.html

Amend for FoxPro

   Dim dbsTemp As Database
   Dim strMenu As String
   Dim strInput As String

   ' Open a Microsoft Jet database to which you will link
   ' a table.
   Set dbsTemp = CurrentDb

   ' Call the ConnectOutput procedure. The third argument
   ' will be used as the Connect string, and the fourth
   ' argument will be used as the SourceTableName.
         ConnectOutput dbsTemp, _
            "LinkedTable", _
            ";DATABASE=C:\db1.mdb", _
            "Dates"

End Sub

You could use a Date function here to change the name with some strong concatenation.
DATABASE=C:\db1.mdb

Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)

   Dim tdfLinked As TableDef

   ' Create a new TableDef, set its Connect and
   ' SourceTableName properties based on the passed
   ' arguments, and append it to the TableDefs collection.
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)

   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
   dbsTemp.TableDefs.Append tdfLinked

End Sub

 

[AlexHedley]

http://accessblog.net/2011/10/how-to-link-foxpro-tables.html?m=1

Set tdf = dbs.CreateTableDef(strTableName, 
   _ dbAttachExclusive, 
   _ strFileName, 
   _ "ODBC;DSN=Visual FoxPro Tables;SourceDB=" & strFolder & 
   _ ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;" & 
   _ "Collate=Machine;Null=Yes;Deleted=Yes;" & 
   _ "TABLE=" & strFileName) 

dbs.TableDefs.Append tdf dbs.TableDefs.Refresh

 

[wisenickel]

Thanks Alex ... I'll give it a try and let you know how it turns out.