Dynamic linked tables in Jet databases with ADO.NET

  • Thread starter Thread starter Mark Rae
  • Start date Start date
M

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
 
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
Click to expand...

That link seems to be the Office 2003 PIAs - doesn't that require a copy of
Office on the machine in the first place...?
 
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
 
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
Click to expand...

Ah right - thanks very much.
 
Back
Top