Changing a linked table's Connect property just with SQL

[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".

I've tried to find a solution using purely managed .NET code, but I'm not
sure it's possible. AFAIK, ADO.NET can't do it. Therefore, I'm wondering if
it's possible to do this just with Access SQL...?

A couple of caveats:

1) Access will not be installed on the client machines

2) I cannot rename the monthly database files

Any assistance gratefully received.

Mark

 

[Douglas J. Steele]

It's not possible to do that using SQL: the Connect property isn't
accessible through SQL.

See whether you can use ADOX to do it. The property of the Table object in
which you're interested is "Jet OLEDB:Link Datasource"

 

[Mark Rae]

It's not possible to do that using SQL: the Connect property isn't
accessible through SQL.

Ah... that's a shame.

See whether you can use ADOX to do it. The property of the Table object in
which you're interested is "Jet OLEDB:Link Datasource"

I'm really trying to avoid COM if at all possible - this is a .NET app which
needs to run on both WinXP Pro and Vista Business, neither of which will
have any version of Office installed.

Am I right in thinking that ADOX is not part of the standard build of either
of these to operating systems...? If so, can it be installed independently
of ADO...?

 

[Douglas J. Steele]

Ah... that's a shame.


I'm really trying to avoid COM if at all possible - this is a .NET app
which needs to run on both WinXP Pro and Vista Business, neither of which
will have any version of Office installed.

You don't need Office installed to use either DAO or ADOX. All you need
installed is DAO or ADOX (and the appropriate Jet provider).

Am I right in thinking that ADOX is not part of the standard build of
either of these to operating systems...? If so, can it be installed
independently of ADO...?

To be honest, I'm not sure whether ADOX is contained as part of the OS, but
I suspect it is, since it's included as part of MDAC.

 

[Sylvain Lafontaine]

What about heterogeneous joins? A linked table is essentially a shortcut
for an heterogenous join, so maybe he could directly write these external
tables links in his SQL select queries?

For exemples of heterogenous joins:
http://support.microsoft.com/kb/254130

 

[Mark Rae]

What about heterogeneous joins?

Hmm - that's actually an excellent suggestion - thanks very much.