?
Solved

Connecting to a User-level protected Back-End DB via a query in another DB's 'Source Connect Str' option

Posted on 2004-10-13
12
Medium Priority
?
324 Views
Last Modified: 2012-06-27
To whom it may concern,

I am trying to connect to data in a Back-End database that is protected via user-level security i.e. it is linked to a specific Workgroup Information File (WIF). Is it possible to create a query in another database, not linked to this WIF, via it's 'Source Connect Str' option?

I have tried the following:

Source Database: C:\BackEndDB.mdb
Source Connect Str: MS Access;UID=MyUserName;Pwd=MyPassWord;

Please advise.

Regards,

Fanie.



0
Comment
Question by:MyVoyagerMy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 85
ID: 12298249
If you have a valid user/pass combo (and use the correct .mdw file), and the account you're using has the proper permissions to access the tables and other necessary objects then yes, you can ... what environment are you doing this in? Access? ASP? etc etc
0
 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 12298276

Try...
Dim Con1 As ADODB.Connection

Set Con1 = New ADODB.Connection

Con1 .ConnectionString = "Data Source=C:\BackEndDB.mdb;User ID=sMyUserName;Password=MyPassWord;"

Zen :))
0
 

Author Comment

by:MyVoyagerMy
ID: 12298375
Hi guys,

I'm working in MS Access 2000. I know how to connect to a user-level protected back-end via ADO. How do you do this directly from within a query using the 'Source Database' and 'Source Connect Str' properties?

Regards,

Fanie.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 12305549
create a dsn to the databse, then use a SQL Passthrough Query

Zen :))
0
 

Author Comment

by:MyVoyagerMy
ID: 12305643
Hi Zen,

Sounds interesting. I'm new to this concept. Do you mind elaborating a bit more? Do you have an example available?

Regards,

Fanie.
0
 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 12305723
ok step by step

1 Open a new query design view, cancel the add table wizard.
2 On the Menubar .....Query > SQL Specific > Pass-Through
3 Right-CLick on the top bar of the Query and select Properties
4 Click in the ODBC property and on the right you see 3 dots  ...  click that
5 You should now see a pop-up Select Datasource........

   If you only want to use this DSN then select File or Machine DSN
   If you wish the DSN to be User Wide, Select System DSN

6 Click New, Scroll through the drivers, and select Micrsoft Access Driver
7 Browse to the folder you want to store the DSN, for example a system DSN you might want to store with the
   Database you want to access, on the network so other users can access it and save it under a meaningful name. click next
8 click on the select button, and browse to the databse you want to access
9 click on the advanced button enter userid and pwd
10 click OK, now highlight the DSN you have just created and click OK
11 You will be prompted to enter userid and pwd and asked if you want to save this in the string.. I always select NO
12 Now you can type in your SQL.  When you run the Query it will prompt for userid and PWD, if you did not save in connection string (this is entirely up to you)

Zen :))

0
 

Author Comment

by:MyVoyagerMy
ID: 12306053
Hi Zen,

First of all thanks for your response. I tried the above and it fails at the end when the DSN must be created with the following message: "You do not have the necessary permisions to use the BackEndDB.mdb object....". I have mentioned that the Back-End DB that I'm trying to access is user-level protected and has an associated Wokgroup Inormation File (WIF) tied to it which I believe causes the error. I'm also not 'joined' to this workgroup when attemting this operation. I can access the required information through ADO by adding the following into the connection string: "Jet OLEDB:System Database=C:\WorkGroup.MDW;User ID = MyUserName; Password = MyPassword ;"
I have noticed when creating the DSN you can't specify the required WIF and therefore causes the problem.

Please advise.

Regards,

Fanie.
0
 
LVL 5

Expert Comment

by:ZenMasterrr
ID: 12306148
ok

Create a new database
in the comand line

"C:\My Documents\TestDatabase.mdb" /wrkgrp "C:\My Documents\SecureGrp.mdw"

this will join the workgroup for other database, you can perform your tasks in the other workgroup with this database

Hope this helps

Zen :))
0
 
LVL 85
ID: 12306679
You've probably already tried this but, use this as your connect string:

"Jet OLEDB:System Database=C:\WorkGroup.MDW;User ID = MyUserName; Password = MyPassword ;"

and the path to your db as your Source DB

Unfortunately, I'm betting you'll have to hardcode the username and password ... I don't think you can set these items for a query at runtime.
0
 

Author Comment

by:MyVoyagerMy
ID: 12307145
Thanks for your response regarding this matter!

LSMConsulting: I tried this and it doesn't work.

Zen: When creating the DSN, there is a button called 'System Database' that you can click on once you have selected the 'Database:' radio button. This will allow you to browse to the  .mdw or Workgroup Information File(WIF). I have successfully created the DSN but when I try to assign it to the Pass-Through Query's 'ODBC Connect Str', I get the following error message:

"You can not use ODBC to import from, export to , or link an external Microsoft Jet or ISAM database table to your databse."

I have also subsequently come accross a question in Experts-Exchange where they discussed connecting from within an Access DB to an Access datasource and that it can't be done with ODBC.

Ref: http://www.experts-exchange.com/Databases/MS_Access/Q_20753824.html

I'm however still wondering if it can be done as per my initial efforts within the query properties itself:

Source Database: C:\BackEndDB.mdb
Source Connect Str: MS Access;UID=MyUserName;Pwd=MyPassWord;

What other 'Source Connect Str' options are there to try? Where it's possible to specify the Workgroup information file as suggested by LSMConsulting?

Thanks,

Fanie.






0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12902938
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question