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
284 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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