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
Solved

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

Posted on 2008-10-20
4
462 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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