Solved

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

697 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