Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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

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
MyVoyagerMy
Asked:
MyVoyagerMy
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
ZenMasterrrCommented:

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
 
MyVoyagerMyAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ZenMasterrrCommented:
create a dsn to the databse, then use a SQL Passthrough Query

Zen :))
0
 
MyVoyagerMyAuthor Commented:
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
 
ZenMasterrrCommented:
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
 
MyVoyagerMyAuthor Commented:
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
 
ZenMasterrrCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
MyVoyagerMyAuthor Commented:
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
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now