Link table Query in MS access

Question

0.00/5 (No votes)

See more:

hi,
i have two databases MS access and DB2, both have many tables. In a scenario i need to fetch the record from two tables(one table belongs to db2 and other one is from MS access) I have created the Link for DB2 table in MS Access(i.e - In MS access view we can see the db2 linked table).

Now i want to run the query for two tables,My query will be like this,
"select table1.col1,table2.col2 from table1 , table2 where (db2)table1.column1 = (MS access)table2.column1.

i have two different connection strings as well for each databases.

DB2 - connection string has some userid and password.
Ms access - also has its own connection strings.

I have used Ms access database connection strings, and used ODBCCommand(), when i try to execute my query in .net application it prompted me with DB2 user name and password.

how to get the result for the above query.....?

Thanks
Mani

Posted 11-Oct-11 7:23am

Manivannan Dheena

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VB Prog · Answer 1 · 2011-10-11T07:44:00

Solution 1

Did you try saving he DB2 user name and password with the table, when you created the link in Access

Posted 11-Oct-11 7:44am

VB Prog

Comments

Manivannan Dheena 12-Oct-11 1:09am

Yes, When i created the link in Access, it has given the option to save the password, In that case if i save it, any one can access the database right. then there is no use of password.

So my intention is like, we need to provide the DB2 user name and password only in run time. and it should be passed via query and should fetch the data.

Hope my replies make sense.

Thanks,
Mani

Sazzad Hossain · Answer 2 · 2011-10-11T20:26:00

there is no easy way of doing this without doing some VB coding. Basically you can save DB2 table link without the userid password.

Then before u execute the query, from vba code you need open this table def object and setup the link options which will include userid password which u might have prompted from the user using a form or something. Then modify the tabledef and then execute the query. That will allow u not to save the password but still access and make local joins.

Take a look at Table Def objects in VBA.

Hope this helps. Thx