How to attach DBF file in Access programmatically?

Question

Access can open DBF (dBase) files, but instead of physically converting the data into MDB format, it has the ability to link to the DBF table itself. This way the DBF is "linked" to the MDB.

Is it possible to attach a DBF file in such manner using C#?

Edit: I would like to use Jet and avoid using MS Access directly.

Answer 1

Perhaps this code from How to quickly copy tables from an ODBC source to MS Access within a C# project might help:

The VB function you’ll need to put into MS Access is quite simple, and basically calls the TransferDatabase method by passing it a DSN (pointing to the source database), a source table name and target table name. The code is as follows:

Public Function Import(dsnName As String, sourceTableName As String, targetTableName As String)
‘ if the table already existsm, delete it.
   On Error GoTo CopyTable
   DoCmd.DeleteObject acTable, targetTableName
CopyTable:
   DoCmd.TransferDatabase _
   acImport, _
   "ODBC Database", _
   "ODBC;DSN=" + dsnName, _
   acTable, _
   sourceTableName, _
   targetTableName
End Function

And then the C# code:

object accessObject = null;
try
{
   accessObject = Activator.CreateInstance(Type.GetTypeFromProgID("Access.Application"));

   accessObject.GetType().InvokeMember(
      "OpenCurrentDatabase",
      System.Reflection.BindingFlags.Default  System.Reflection.BindingFlags.InvokeMethod,
      null,
      accessObject,
      new Object[] { "AccessDbase.mdb" });

   accessObject.GetType().InvokeMember(
      "Run",
      System.Reflection.BindingFlags.Default  System.Reflection.BindingFlags.InvokeMethod,
      null,
      accessObject,
      new Object[] { "Import", "DSN Name", "Source table name", "Target table name" });

   accessObject.GetType().InvokeMember(
      "CloseCurrentDatabase",
      System.Reflection.BindingFlags.Default  System.Reflection.BindingFlags.InvokeMethod,
      null,
      accessObject,
      null);

   MessageBox.Show("Copy succeeded.");
}
catch (Exception ex)
{
   string message = ex.Message;
   while (ex.InnerException != null)
   {
      ex = ex.InnerException;
      message += "\r\n----\r\n" + ex.Message;
   }
   MessageBox.Show(message);
}
finally
{
   if (accessObject != null)
   {
      System.Runtime.InteropServices.Marshal.ReleaseComObject(accessObject);
      accessObject = null;
   }
}

Changing the VBA to read acLink rather than acImport should allow linking.

Edit re comments

I cannot help with c#, but here is some VBScript that links a table from one MDB to another.

strLinkFile = "C:\Docs\Link.mdb"
strAccessFile = "C:\Docs\LTD.mdb"

'Create Link... '
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & strAccessFile & ";" & _
       "Persist Security Info=False"

Set adoCat = CreateObject("ADOX.Catalog")
Set adoCat.ActiveConnection = cn

Set adoTbl = CreateObject("ADOX.Table")

Set adoTbl.ParentCatalog = adoCat
adoTbl.Name = "LinkTable"

adoTbl.properties("Jet OLEDB:Link Datasource") = strLinkFile
adoTbl.properties("Jet OLEDB:Link Provider String") = "MS Access"
adoTbl.properties("Jet OLEDB:Remote Table Name") = "Table1"
adoTbl.properties("Jet OLEDB:Create Link") = True

'Append the table to the tables collection '
adoCat.Tables.Append adoTbl

It is modified from: http://support.microsoft.com/kb/240222

Answer 2

This is only a suggestion, but what about linking to a DBF from Access, then look at the connect string Access uses to get to the data. For this to work with Jet alone, you'll have to have the ISAM that Access uses for accessing xBase data. I don't know for certain if that's an Access component or a Jet component, though.

This is what I get for the connect string when I link to a DBF with Access 97 (and the results are identical with Access 2003):

dBase IV;HDR=NO;IMEX=2;DATABASE=C:\Path

In the MSysObjects table, that's in the CONNECT column and the dbf file name is in the DATABASE column. Links to Jet data have nothing in the CONNECT column and only in the DATABASE column, but the .Connect property of a tabledef that is a link to a Jet table in another MDB is the same as what's in the Database column.

So, I'm not sure exactly what you'd supply as your connect string, but ConnectionStrings.com has suggestions. However, those don't use Jet for accessing the data, so I'm just not sure what you would use.

--
David W. Fenton
David Fenton Associates