Ok, I think this nightmare has finally come to a close.
Several points:
1) I agree that Access should not be used. But you know how hard it is to change legacy apps and procedures. It'll change, but not today. The customer has been using Access since the age of the dinosaurs (give or take a few millenia), and will continue to do so for the forseable future.
2) For some bizzare reason, the fastest way to copy the table from SQL Server to MS Access is still by doing an import from Access. So I'm not very proud of the approach I followed (since I had to pollute my project with COM and automation), but I see no other way for the moment:
I created a VBA module that does an import from an SQL server, and it receives three parameters: a DSN name to use (Access doesn't support dynamic connnection strings), a source table name and a target name:
Code:
Public Function Import(dsnName As String, sourceTableName As String, targetTableName As String)
On Error GoTo CopyTable
DoCmd.DeleteObject acTable, targetTableName
CopyTable:
DoCmd.TransferDatabase _
acImport, _
"ODBC Database", _
"ODBC;DSN=" + dsnName, _
acTable, _
sourceTableName, _
targetTableName
End Function
I then added a macro that calls this function, and called the macro "Import".
And finally, in my C# project, I added a reference to the Microsoft Access 9.0 Object Library, and added the following code in order to call the macro (based on an MSDN article at
Code:
using Access;
// Create an instance of Microsoft Access, make it visible,
// and open Db1.mdb.
Access.ApplicationClass oAccess = new Access.ApplicationClass();
try
{
oAccess.Visible = true;
oAccess.OpenCurrentDatabase(mQualifiedDatabaseName, false);
oAccess.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
null, oAccess, new Object[] { "Import", mDestDsn, sourceTableName, targetTableName });
}
finally
{
// Quit Access and clean up.
oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
oAccess = null;
}
This works flawlessly, with one thorn: I have no progress indication. I've tried to find a way to trap events from the Access object, but haven't really gone very far. It doesn't seem to be well documented, and I feel I'm in uncharted waters on this one. Haven't found too much about it on the net.
I also tried launching a separate thread that does a "SELECT COUNT(*)" on the target table every few seconds, but that failed too since Access maintains a full lock on the target table until the copy is complete, and the SELECT fails.
All in all, the damned thing works but I've now added COM to my project and have lost the ability to show any progress to the user. Not the best solution, but better than nothing.
If you have any ideas on how to trap progress events from the Access object, or how to get Access to copy a table without locking it (e.g. disable transactions somehow?) it would be appreciated.
Thx.
