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
259 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
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 84
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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 84
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now