Linked Tables, ODBC Dsn - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Linked Tables, ODBC Dsn

Author

Topic

ankurgupta26
Starting Member

32 Posts

Posted - 2003-08-29 : 06:05:05

I have a DSN called "abc" which points to an oracle database "db1"

Access seems to retain the 'db1' connection string from the time of initially linking the tables. If you change the configuration of the DSN on the PC to look at another database ie 'db2', Access seems to ignore this and use/retain the original value of 'db1'. The workaround to this is to use the Linked table manager.

What I want is that access linked tables should pick up the database name at run time from the DSN rather than retaining the value of the database name that was used while confuguring the linked tables the first time.

Is there any way ?

Thanks !
AnkuR.

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-08-29 : 07:31:02

I don't think so. Access stores the linked database info in the sysObjects table (hidden). Perhaps if you use a file dsn and regenerate it when the database name changes - that may work but I don't know for sure.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-29 : 07:33:12

Not really. It's easier to use the Linked table manager. The only other workaround is to write VB code to dynamically re-link the table(s) based on the DSN.

Why are you changing the database that the DSN points to? That more or less defeats the purpose of having the DSN in the first place. If you need multiple datasources, create multiple DSNs.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-29 : 09:11:27

You can manually link the tables, and set the connect to be simple:

ODBC;DSN=SomeDSN

Note that you cannot edit the connect property of an existing linked table, you must delete it and re-create a new one.

To manually link the tables, you must use VBA. something like (this may not be exact):


dim t as tabledef

set t = currentdb.createtabledef("Test")
t.connect = "ODBC;DSN=SomeDSN"
t.SourcetableName = "SoureceTable"
currentdb.tabledefs.add t    'this step makes it "official"

Then, in theory, as your DSN definition changes, the link should point to other sources. However, you may have huge issues if the DSN points to databases in which the table definiitions are different; eitehr new columns won't show up or you will get errors and the link with fail completely.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-29 : 09:14:39

also note you can create a "linked query" very easily, without VBA:

1. create a new query
2. don't add any tables; hit cancel or close
3. in the queries properties, find SourceConnectStr
4. set it to "ODBC;DSN=SomeDSN"
5. Then, click "Add table". You will see the tables from your DSN! just select * from some table and you are all set.

This doesn't perform as well as linked tables, but it can be a really cool trick. And the linked query is truly dynamic; as the DSN changes, even if the table changes (as long as it is there) the query will pick up all columns correctly.

You can even add filtering and all that at this point. Very underused feature sometimes!

- Jeff

ankurgupta26
Starting Member

32 Posts

Posted - 2003-08-29 : 10:42:17

guys...thanks for your replies.

The problem is that the production database (oracle) is changing. Hence I need to chage DSN.

And now this access database would be used at several locations, each having a different oracle database. But each location would have the same common DSN name.

So what I wanted that if I just change the DSN, the access database should automatically pick the oracle database name from the re-configured DSN.

In other words change should be restricted to DSN change rather than doing any changes in the access database itself.

Thanks !
AnkuR.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-29 : 16:09:18

well, try reading my replies and trying them out. They answer your question directly, and I give you two different ways to do it. The "dynamic linked query" will work in your situation, if the DSN switches types. the linked table won't work in that case.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources