Opening database in XL VBA

  • Thread starter Thread starter Michael J. Malinsky
  • Start date Start date
M

Michael J. Malinsky

I found this code from Microsoft:

Set dbs = wrkJet.OpenDatabase("", False, False,_
"ODBC;DSN=MS Access Database;" _
& "UID=admin;PWD=password;DATABASE=NewDB")

which opens a database. The problem is that it keeps opening a Select
Database dialog box which requires me to select the database. The database
name is NewDB and is included in the arguments for the OpenDatabase method
so I don't understand why the dialog asking me to select the database is
popping up. I've tried using the entire path, using the dbDriverNoPrompt
(and the other dbDriver options) to no avail.

Any help would be appreciated.

TIA
--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
Michael

The first argument for OpenDatabase is the db name. While you have it in
your connections string (4th argument) you also need it for the first one
where you have an empty string now. If you database is called NewDB.mdb,
then

wrkJet.OpenDatabase("NewDB.mdb", False, etc..)
 
Dick,

I made the change you suggested, now the line of code reads as:

Set dbsPubs = wrkJet.OpenDatabase("NewDB.mdb", _
False, False, _
"ODBC;DATABASE=NewDB.mdb;UID=admin;PWD=password;DSN=MS Access
Database")

But I'm still getting the Select Database dialog. When the dialog pops up,
it defaults to the directory of the Excel file the VBA code is located in.
This is fine and is where NewDB.mdb is located. Is there something else I'm
missing?

TIA

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
Michael

You got me. Get rid of all the arguments for OpenDatabase except the
database name, but change the database name to include the full path.
Here's how I get a database using DAO

Sub test2()

Dim dbspub As DAO.Database

Set dbspub = DAO.OpenDatabase("C:\Dick\db1.mdb")

Debug.Print dbspub.Name

dbspub.Close

End Sub

I don't use Workspace, but there must be some use for it. This worked too:

Sub test()

Dim dbspub As Database
Dim wksJet As Workspace

Set wksJet = DAO.DBEngine.Workspaces(0)

Set dbspub = wksJet.OpenDatabase("c:\Dick\db1.mdb")

Debug.Print dbspub.QueryDefs.Count

Debug.Print dbspub.Name

dbspub.Close

End Sub

I've just never seen a need for Workspaces. If you really need that
connection string argument, then let me know, otherwise this should get you
where you want to go.
 
Do you do anything with passwords in databases you access? Based on some
suggestions in the NGs, I'm trying to create a jet database using Excel VBA
that is password protected. I can get this far. Both of your code
suggestions work, but I cannot get them to work if there is a userid and
password attached. I found what the proper options arguments are for the
OpenDatabase method, but I keep getting an invalid password error. Any
thoughts?

Thanks again.

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
Michael

I think the Options has to be True to use a password. Try this

Sub Opendb()

Dim db As DAO.Database

Set db = DAO.OpenDatabase("C:\Dick\db10.mdb", True, False,
";UID=Admin;PWD=mypw")

Debug.Print db.QueryDefs.Count

db.Close

End Sub
 
Back
Top