Refresh Linked Table with path and table name

[Guest]

Hello,

I have a MS Access 2000 database which contains a linked table, "DATA".

Each month, I need to refresh the path to a new location for the database. I
have seen the code to refresh or create a linked table, however, my problem
is, even though the table is named "DATA", the name of the source table us
the month + "DATA". Currently, I delete the linked table and then link the
new month.

What I'd like to do is refresh the link of DATA from c:\may.mdb, using
table "maydata" to c:\june.mdb using the "junedata" table.

I use the ADOX example on the site.

objTbl.Properties("Jet OLEDB:Link Datasource") = "c:\june.mdb"
objTbl.Properties("Jet OLEDB:Remote Table Name") = "junedata"

I actually have 2 linked tables, the second table is the same name for each
month, so if I just refresh the link for it, using the Link Datasource
property, that one works fine. It's when I have to change the source from
maydata to junedata using the above code I get:

Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
-214727887

Is it possible to do it this way, or do I have to first delete the linked
table and then create a new linked table pointing to the new location and
table name?

Thank you,
Mike

 

[Tim Ferguson]

What I'd like to do is refresh the link of DATA from c:\may.mdb, using
table "maydata" to c:\june.mdb using the "junedata" table.

I use the ADOX example on the site.

If you are using Access to access a jet database then I have to wonder why
you are piddling about with ADO and why not use DAO, which is what it's
designed for. The DAO.Tabledef.Connect property is well documented and
works like a charm.

ADOX seems to me to be a terrible cross between a universal DDL-type
language (but not as structured as SQL-DDL) and a native engine API (but
far too restricted compared with DAO).

All the best


Tim F