Solved

Syntax of Opendatabase for a jet db to specify workgroup, user, and password.

Posted on 2008-10-20
4
451 Views
Last Modified: 2013-11-27
Access 2003.  I want open a second database in my application mdb using the opendatabase method.  The second database is in a different workgroup and has Access security with a username and password.
The purpose is to set DB = (second database) so that I can run queries to import the data into the current database.  I could do it with ADO but I think this involves writing a lot of code.
Can anyone help?
Thanks in advance.
0
Comment
Question by:CRB1609
  • 2
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22756072
http://support.microsoft.com/kb/236878
Make sure you prefix the objects with 'DAO.'
0
 

Author Comment

by:CRB1609
ID: 22760294
peter57r
Thanks.  The code snippet gives an error 3029 'Not a valid account name or password' on the line: Set WS = (etc)
The account name and password are correct.
Can you help?
Thanks.
Private Function opentables() As Boolean
   Dim WS As DAO.Workspace
   Dim DB As DAO.Database
   Dim RS As DAO.Recordset
   DBEngine.SystemDB = "C:\cela\cela.mdw"
   Set WS = CreateWorkspace("NewWS", "cela_designer", "d7+_SeK!", dbUseJet)
   Set DB = WS.opendatabase("C:\cela\CELA.data.mdb")
   Set RS = DB.OpenRecordset("Users")

Open in new window

0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22810555
I have found some old code i have used before..
Just tested it with a rs and it worked OK.

Sub OpenDBWithNewWorkgroupFile(strPathToFile As String, _
  strDefaultUser As String, _
  strDefaultPwd As String, _
  strPathToDatabase As String)
    ' This function uses the unsupported PrivDBEngine object to
    ' open a database using a different workgroup information file
    ' than the one being used as the default. You might do this if
    ' you want to access data that is in a secured database that uses
    ' a different workgroup information file.
   
    Dim dbe As DAO.PrivDBEngine
    Dim wrk As DAO.Workspace
    Dim dbs As Database
   
    ' Return a reference to a new instance of the PrivDBEngine object.
    Set dbe = New PrivDBEngine
    ' Set the SystemDB property to specify the workgroup file.
    dbe.SystemDB = strPathToFile
    dbe.DefaultUser = strDefaultUser
    dbe.DefaultPassword = strDefaultPwd
    Set wrk = dbe.Workspaces(0)
    ' Open the secured database.
    Set dbs = wrk.OpenDatabase(strPathToDatabase)

'now process the open database - try opening a rs
Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset("smallertbl")
rs.MoveLast
MsgBox rs.RecordCount
rs.Close
Set rs = Nothing
' done with the open db

dbs.Close
Set dbs = Nothing
Set wrk = Nothing
Set dbe = Nothing
End Sub

I used:
OpenDBWithNewWorkgroupFile "C:\largemdb\systema.mdw", "Admin", "password", "C:\largemdb\largemdb.mdb"
0
 

Author Closing Comment

by:CRB1609
ID: 31507746
Thanks you very much.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now