dpokerbear
asked on
MS Access obtaining Data Source Path
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.
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("
'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
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"?
ASKER
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.
I just don't know how to obtain this path using VBA code...
and yes, this code is a copy from a MS Article.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks and sorry for the dalay