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
314 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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