Solved

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

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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Familiarize people with the process of utilizing SQL Server views 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 Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

26 Experts available now in Live!

Get 1:1 Help Now