Dynamic linked tables in Jet databases with ADO.NET

[Mark Rae]

Hi,

I've been given (i.e. lumbered with!) upgrading a VB6 desktop application to
a C# v2 WinForms application.

It's basically a reporting application which uses Jet4 as its database.

There is a single "main" backend database called Report.mdb, plus many
monthly database files called e.g. 200401.mdb, 200402,mdb,
200403.mdb.....200703.mdb.

Report.mdb contains a great many linked tables, plus a few additional native
lookup tables etc.

Report.mdb also contains a huge number of queries which drive the reporting.

In the VB6 app, the user selects which of the monthly database files they
want to work with. This then updates the linked tables in Report.mdb to
point to the currently selected monthly file, as follows:

For Each tdf In <Report.mdb>.TableDefs
strConnect = "DATABASE=" & <monthly database file> & ";TABLE=" &
tdf.Name
tdf.Connect = strConnect
tdf.RefreshLink
Next tdf

In the fullness of time, Report.mdb and the monthly databases will be
replaced with a single SQL Server database, but that won't happen for a
while so, regrettably, I need a "quick fix".

Therefore, I'm interested to know if C# / ADO.NET can do dynamic Jet table
linking natively, or whether I'm going to need to use COMInterop.

A couple of caveats:

1) Access is not guaranteed to be installed on the client machine

2) I cannot rename the monthly database files

Any assistance gratefully received.

Mark

 

[Mary Chipman [MSFT]]

If you're stuck with using Jet, then you'll need to use COM Interop.
There is a DAO interop assembly and writing the code itself is trivial
- you just set the connect property of a TableDef. I'd recommend
rewriting the code so that it drops all of the links and then
recreates them from scratch each time. Here's the link:
http://msdn2.microsoft.com/en-us/library/aa159923(office.11).aspx

--Mary

 

[Mark Rae]

If you're stuck with using Jet, then you'll need to use COM Interop.
There is a DAO interop assembly and writing the code itself is trivial
- you just set the connect property of a TableDef. I'd recommend
rewriting the code so that it drops all of the links and then
recreates them from scratch each time. Here's the link:
http://msdn2.microsoft.com/en-us/library/aa159923(office.11).aspx

That link seems to be the Office 2003 PIAs - doesn't that require a copy of
Office on the machine in the first place...?

 

[Mary Chipman [MSFT]]

There's one for DAO, which is the API for Jet. If you're just
manipulating Jet objects (tabledefs, querydefs -- no forms, reports,
macros, VBA, etc.) then you should be OK. --Mary

 

[Mark Rae]

There's one for DAO, which is the API for Jet. If you're just
manipulating Jet objects (tabledefs, querydefs -- no forms, reports,
macros, VBA, etc.) then you should be OK. --Mary

Ah right - thanks very much.