Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-20
4
Medium Priority
?
497 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

580 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