Changing a linked table's Connect property just with SQL

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

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
 
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"
 
It's not possible to do that using SQL: the Connect property isn't
accessible through SQL.
Click to expand...

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

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...?
 
Mark Rae said:
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.
Click to expand...

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

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.
 
Back
Top