[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access obtaining Data Source Path

Posted on 2010-11-18
4
Medium Priority
?
670 Views
Last Modified: 2012-06-27
In the code attached, I would like to know how to dynamically specify the Data Source path to which the database is already linked to/

Also, my existing database uses .mdw file which contains list of users and passwords, which are required to log into the database.

For example in this line of code
cnn.Properties("Data Source") = "C:\MDB\Northwind.mdb" << I would like this path to be picked up from the current link.

I would also like to know how to pick up the existing username and password that is currently logged into the database and specify it in the attached code.

For example:
 Set wsDAO = DBEngine.CreateWorkspace("WorkSpace", "Admin", "", dbUseJet) << Where it says Admin, I would like that to be dynamically assigned depending what the current user, as well as the password.

'This sample uses Form level scope for the open DAO database.
'Depending on your application, you may choose Module level.
Private wsDAO As DAO.Workspace
Private dbDAO As DAO.Database

Private Sub Form_Load()
   Dim cnn As ADODB.Connection

  'ADO has the ability to open row-level locking; DAO does not.
  'The following code is used to implement row-level locking in DAO.
  'If the database is opened first in row-level locking in ADO,
  'subsequent attempts to open the database in ADO and DAO will use the same mode.

   Set cnn = New ADODB.Connection
   cnn.Provider = "Microsoft.JET.OLEDB.4.0"
   cnn.Properties("Data Source") = "C:\MDB\Northwind.mdb"
   cnn.Properties("Jet OLEDB:Database Locking Mode") = 1
   cnn.CursorLocation = adUseServer
   cnn.Open

   Set wsDAO = DBEngine.CreateWorkspace("WorkSpace", "Admin", "", dbUseJet)
   Set dbDAO = wsDAO.OpenDatabase("C:\MDB\Northwind.mdb")
   
   'Close the ADODB connection.
   cnn.Close
   Set cnn = Nothing
End Sub

Open in new window

0
Comment
Question by:dpokerbear
[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
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34178680
Microsoft article 306435 that you used is about using Access database from Visual Basic using DAO 3.60. What do you mean by "the database is already linked to" and by "the current link"?
0
 

Author Comment

by:dpokerbear
ID: 34185154
when i refer to current link, i am talking about the existing linked path (location of the back end database), which can be obtaine by going to Linked Table Manager.

I just don't know how to obtain this path using VBA code...

and yes, this code is a copy from a MS Article.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 34185348
For any linked table Linked_Table1, the path of its database can be obtained by this code:

split(currentdb.tabledefs("Linked_Table1").Connect,";DATABASE=")(1)
0
 

Author Closing Comment

by:dpokerbear
ID: 34290613
thanks and sorry for the dalay
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

656 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